Обсуждение: best way to get PKey and FKey from inside applications (permission pb)

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

sandro -

SQL2003 fixes these issues with information_schema but it seems that
postgres has not caught up yet:

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

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.interfaces/browse_thread/thread/9f19995849b3cdf4/c20b81bf8cf183af?lnk=st&q=information+schema&rnum=9&hl=en#c20b81bf8cf183af

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

            regards, tom lane