[SQL-CVS] [ sqlobject-Bugs-3471315 ] Can't use VIEWs with postgres

[SourceForge.net <no...@so...>]

Bugs item #3471315, was opened at 2012-01-09 04:49
Message generated for change (Comment added) made by hthompson
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=3471315&group_id=74338

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Postgres
Group: SQLObject release (specify)
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Henry S Thompson (hthompson)
Assigned to: Oleg Broytman (phd)
Summary: Can't use VIEWs with postgres

Initial Comment:
SQLObject v.1.2.1, Python 2.6, Windows 7 x64, Postgresql 9.1

The code in pgconnection.py for finding primary keys won't ever find anything for a VIEW.  Not sure what the correct fix is (I don't understand postgres internals v. well), but the attached patch works for me. . .

----------------------------------------------------------------------

>Comment By: Henry S Thompson (hthompson)
Date: 2012-01-10 09:35

Message:
Sorry not to be clearer -- what you've written is the existing code, which
doesn't work for VIEWs.  I don't understand how postgres lays out and uses
its internal tables, so I can't offer any suggestions about how to modify
that SELECT so that it works for VIEWs, sorry.

The proximate problem with that code is that although there is an entry in
pg_class for a view, so there is an oid for it, that oid does _not_ appear
in pg_index in the indrelid column (or in any other column).  I'm afraid I
don't know where to look next. . .

----------------------------------------------------------------------

Comment By: Oleg Broytman (phd)
Date: 2012-01-10 09:11

Message:
Instead of blindly copying idName wouldn't it be better to find out why
PostgresConnection doesn't recognize pkeys on views? Try the following
query replacing 'test' with the name of a VIEW:

SELECT pg_index.indisprimary,
    pg_catalog.pg_get_indexdef(pg_index.indexrelid)
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
    pg_catalog.pg_index AS pg_index
WHERE c.relname = 'test'
    AND c.oid = pg_index.indrelid
    AND pg_index.indexrelid = c2.oid
    AND pg_index.indisprimary

It have to return something like

 indisprimary |                      pg_get_indexdef
--------------+-----------------------------------------------------------
 t            | CREATE UNIQUE INDEX test_pkey ON test USING btree (my_id)
(1 row)

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=3471315&group_id=74338