#107 Can't use VIEWs with postgres

closed-accepted
Oleg Broytman
None
5
2012-05-13
2012-01-09
Henry S Thompson
No

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. . .

Discussion

  • Candidate patch

     
    Attachments
  • Oleg Broytman
    Oleg Broytman
    2012-01-10

    • assigned_to: nobody --> phd
     
  • Oleg Broytman
    Oleg Broytman
    2012-01-10

    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)

     
  • 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. . .

     
  • Oleg Broytman
    Oleg Broytman
    2012-05-13

    • labels: 519513 -->
    • milestone: 293287 -->
    • status: open --> closed-accepted
     
  • Oleg Broytman
    Oleg Broytman
    2012-05-13

    Well, after reading Postgres' docs and looking into pg_catalog tables I see VIEWs don't have PRIMARY KEYs. Seems I have noting but accept your patch. Sorry it took so long and thank you for help!

    Applied and committed in the revisions 4524-4526 (branches 1.2, 1.3 and the trunk). Will be in the next round of releases.