Hi all,

I started using sqlalchemy (python ORM) that works really well but fails
detecting Primary and Foreign key other than for owner or
superusers. Sqlalchemy queries the information schema with the following query
that returns nothing if you are connected as a user with no particular
privileges:

SELECT
table_constraints.constraint_name ,
table_constraints.constraint_type ,
table_constraints.table_name ,
key_column_usage.table_schema ,
key_column_usage.table_name ,
key_column_usage.column_name ,
key_column_usage.constraint_name ,
constraint_column_usage.table_schema ,
constraint_column_usage.table_name ,
constraint_column_usage.column_name ,
constraint_column_usage.constraint_name

FROM information_schema.table_constraints
JOIN information_schema.constraint_column_usage ON
constraint_column_usage.constraint_name = table_constraints.constraint_name
JOIN information_schema.key_column_usage ON
key_column_usage.constraint_name = constraint_column_usage.constraint_name

WHERE table_constraints.table_name = 'my_table'
AND table_constraints.table_schema = 'public' ;

If you use '\d mytable' you get these information correctly so that there's
no reason to deny the same info from information_schema, correct?

Looking at how '\d' returns the information I always used a different query
(look here http://www.sqlalchemy.org/trac/ticket/71) that is not using
information_schema, but sqlalchemy would prefere to stick to the more
standard information_schema. What would you guys suggest in this case?

Thanks in advance
sandro
*:-)


--
Sandro Dentella *:-)
e-mail: sandro@e-den.it
http://www.tksql.org TkSQL Home page - My GPL work

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

sandro -

SQL2003 fixes these issues with information_schema but it seems that
postgres has not caught up yet:
http://groups.google.com/group/pgsql...0b81bf8cf183af

although i am running only 8.0.4 here. if 8.1 still has this problem
(and its documentation for information_schema still seems to indicate
the views show constraints "owned" by the current user rather than
"accessible"), then we might as well use PG's system tables until this
is resolved. can you submit a patch to postgres.py that uses the PG
system tables ?

Sandro Dentella wrote:
> Hi all,
>
> I started using sqlalchemy (python ORM) that works really well but fails
> detecting Primary and Foreign key other than for owner or
> superusers. Sqlalchemy queries the information schema with the following query
> that returns nothing if you are connected as a user with no particular
> privileges:
>
> SELECT
> table_constraints.constraint_name ,
> table_constraints.constraint_type ,
> table_constraints.table_name ,
> key_column_usage.table_schema ,
> key_column_usage.table_name ,
> key_column_usage.column_name ,
> key_column_usage.constraint_name ,
> constraint_column_usage.table_schema ,
> constraint_column_usage.table_name ,
> constraint_column_usage.column_name ,
> constraint_column_usage.constraint_name
>
> FROM information_schema.table_constraints
> JOIN information_schema.constraint_column_usage ON
> constraint_column_usage.constraint_name = table_constraints.constraint_name
> JOIN information_schema.key_column_usage ON
> key_column_usage.constraint_name = constraint_column_usage.constraint_name
>
> WHERE table_constraints.table_name = 'my_table'
> AND table_constraints.table_schema = 'public' ;
>
> If you use '\d mytable' you get these information correctly so that there's
> no reason to deny the same info from information_schema, correct?
>
> Looking at how '\d' returns the information I always used a different query
> (look here http://www.sqlalchemy.org/trac/ticket/71) that is not using
> information_schema, but sqlalchemy would prefere to stick to the more
> standard information_schema. What would you guys suggest in this case?
>
> Thanks in advance
> sandro
> *:-)
>
>
> --
> Sandro Dentella *:-)
> e-mail: sandro@e-den.it
> http://www.tksql.org TkSQL Home page - My GPL work
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

zzzeek@gmail.com writes:
> SQL2003 fixes these issues with information_schema but it seems that
> postgres has not caught up yet:
> http://groups.google.com/group/pgsql...0b81bf8cf183af

I believe we've updated to the SQL2003 definitions for PG 8.2.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings