failing detecting primary keys if table created by a different user

[Former user]

sqlalchemy fails to detect primary keys if table is created by another user. You can reproduce the error with the following sql/python code:

    CREATE USER user1;
    CREATE USER user2;

    SET SESSION AUTHORIZATION 'user1';
    DROP TABLE table1;
    CREATE TABLE table1 (
        user_key varchar(3) NOT NULL PRIMARY KEY
    );
    GRANT ALL on table1 to PUBLIC;

    SET SESSION AUTHORIZATION 'user2';
    DROP TABLE table2;
    CREATE TABLE table2 (
        user_key varchar(3) NOT NULL PRIMARY KEY
    );
    GRANT ALL on table2 to PUBLIC;

and:

from sqlalchemy import *

engine = ...
tables = [ 'table1', 'table2' ]
for table_name in tables:
    table = Table(table_name, engine, autoload=True)
    print table_name
    print [for c in table.columns](c)
    print table.primary_key

you get something like this:

table1 object at 0xb6cabf2c>,key='user_key',primary_key=True,nullable=False,hidden=False,default=None)](Column('user_key',<sqlalchemy.databases.postgres.PGString) ''

---

table2 [object at 0xb6cb086c>,key='user_key',primary_key=False,nullable=False,hidden=False,default=None)](Column('user_key',<sqlalchemy.databases.postgres.PGString)

[]

---

Where primary_key is True for the first and False for the second. Why that? is this a bug or I'm missing something?

[Former user]

What fails is the join to get the primary key. In the attached file you can see that * both tables are in the public scheme * the pg superuser has no more privileges than user2 and fails to get primary key of table created by user1 * user1 gets correctly the primary keys on his own table

[Former user]

In Tksql I used to detect Primary Keys with a different query, that never seems to fail even though I cannot say if it is valid with schemas. I worked it out looking at the queries issued by 'psql -E':

   "select attname from pg_attribute where attrelid = (select indexrelid from pg_index i, pg_class c  where c.relname = '%s' and c.oid = i.indrelid  and i.indisprimary = 't' )" %  table

In a similar way I devised a query that leads to Foreign Keys, I'm not stating this is always true since I have not looked at the system catalog or at documentation, but it seems to work...

    "SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) as condef FROM pg_catalog.pg_constraint r WHERE r.conrelid = (SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = '%s' AND pg_catalog.pg_table_is_visible(c.oid)) AND r.contype = 'f' ORDER BY 1" % (table_name)

*:-)

[Mike Bayer]

hey guys, I am using the information_schema for all of this, which had some tricky documentation but there is no mention of any usernames in the queries that are used. if postgres' information_schema magically forgets to give you the primary keys for a table if its not your username, that would be a postgres bug wouldnt it ? unless Im doing the query incorrectly. It would be nice for this to be fixed but I am relying upon the postgres community to step in and suggest the correct queries, which people have already been doing in other cases similar to this (just turn on echo=True in your engine to see the queries...I guess you knew that). id very much like to stay on information_schema though since its a standard.

[Mike Bayer]

FYI, ive been snooping around on some PG lists (and reading SQL2003 directly) and it appears that SQL2003 relaxes the restrictions on information schema tables like TABLE_CONSTRAINTS, KEY_COLUMN_USAGE to allow users who have access to those tables to see the information. the current PG 8.1 docs use language like The view table_constraints contains all constraints belonging to tables owned by the current user., however SQL2003 says: Identify the table constraints defined on tables in this catalog that are accessible to a given user or role.. i.e. "owned" becomes "accessible".

This thread mentions it too: http://groups.google.com/group/pgsql.interfaces/browse_thread/thread/9f19995849b3cdf4/c20b81bf8cf183af?lnk=st&q=information+schema&rnum=9&hl=en#c20b81bf8cf183af

So it seems like this will be fixed in PG.

[Mike Bayer]

that said, since it is now established that a. information schema is broken and b. SQL2003 agrees that its broken, I will accept the switch to using Postgres' internal tables for now. can someone provide a patch to postgres.py ?

[Mike Bayer]

sandro dentella's patch which uses pg_catalog

[Mike Bayer]

also should mention that a PG developer said this issue is fixed in Postgres in 8.2. but im curious if the pg_catalog also performs faster than information schema (thereby fixing #60 too....)

[Former user]

fixes my own reflecttable to return length of fields as int

[Former user]

I've been meaning to work on this: what's the timeline? It'll take (me) a couple of days.

[Mike Bayer]

hi -

there are some issues with the attached patch:

• it is not quite up to date anymore, as we have added composite foreign key capability via ForeignKeyConstraint. The regular expression used will have to be modified, and there is actually a good example in the mysql.py module as it is reflecting the same thing. you can consult information_schema.py, mysql.py, etc. for examples on how to convert the foreign key rowsets into a ForeignKeyConstraint object.

• i have heard that the design of pg_catalog keeps changing between postgres versions. Before i switch the entire userbase over to this methodology, it would be nice to get some feedback as to which versions of PG we can confirm these queries are fully functional. At the very least I still would probably have a flag in the PG dialect called "use_information_schema" so that people can go back to the old way if desired; but I would like the pg_catalog method to be the default if it does actually work across all PG versions 7 (or 8?) and greater.

i have to get 0.2.7 out this weekend so it looks like this patch wont make it into that release :/

[Former user]

The patch I just added to #155 includes a reworked version of these patches.

http://www.sqlalchemy.org/trac/attachment/ticket/155/case_and_reflection1.diff

Should work against 8.1. Doesn't work against 7.3 but neither does information_schema.

[Mike Bayer]

ive committed the latest version of the postgres reflection patch in changeset:1810, which is part of #155, but I dont have a test case with which to test this specific issue. im going to mark this closed but if you still cant get around pg's security then reopen and we'll keep trying.

[Mike Bayer]

Removing milestone: 0.3.0 (automated comment)