Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #2848: information_schema.key_column_usage does not work

BUG #2848: information_schema.key_column_usage does not work

From
"Tony Marston"
Date:
The following bug has been logged online:

Bug reference:      2848
Logged by:          Tony Marston
Email address:      tony@marston-home.demon.co.uk
PostgreSQL version: 8.2
Operating system:   Windows XP
Description:        information_schema.key_column_usage does not work
Details:

I have the following query:

SELECT key_column_usage.*,constraint_type
FROM information_schema.key_column_usage
LEFT JOIN information_schema.table_constraints USING
(table_schema,table_name,constraint_name)
WHERE table_schema='whatever' and table_name='whatever'
ORDER BY constraint_type, constraint_name, ordinal_position

This works when I am logged on as 'postgres', but if I try it after logging
on with a different username it fails with "ERROR: relation with OID 18635
does not exist".

Re: BUG #2848: information_schema.key_column_usage does not work

From
Tom Lane
Date:
"Tony Marston" <tony@marston-home.demon.co.uk> writes:
> I have the following query:

> SELECT key_column_usage.*,constraint_type
> FROM information_schema.key_column_usage
> LEFT JOIN information_schema.table_constraints USING
> (table_schema,table_name,constraint_name)
> WHERE table_schema='whatever' and table_name='whatever'
> ORDER BY constraint_type, constraint_name, ordinal_position

> This works when I am logged on as 'postgres', but if I try it after logging
> on with a different username it fails with "ERROR: relation with OID 18635
> does not exist".

I tried to reproduce this, but it works for me ...

            regards, tom lane

Re: BUG #2848: information_schema.key_column_usage does not work

From
Laurence Rowe
Date:
I see the same problem with 8.2 on linux. Laurence

Tony Marston wrote:
> The following bug has been logged online:
>
> Bug reference:      2848
> Logged by:          Tony Marston
> Email address:      tony@marston-home.demon.co.uk
> PostgreSQL version: 8.2
> Operating system:   Windows XP
> Description:        information_schema.key_column_usage does not work
> Details:
>
> I have the following query:
>
> SELECT key_column_usage.*,constraint_type
> FROM information_schema.key_column_usage
> LEFT JOIN information_schema.table_constraints USING
> (table_schema,table_name,constraint_name)
> WHERE table_schema='whatever' and table_name='whatever'
> ORDER BY constraint_type, constraint_name, ordinal_position
>
> This works when I am logged on as 'postgres', but if I try it after logging
> on with a different username it fails with "ERROR: relation with OID 18635
> does not exist".
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Re: BUG #2848: information_schema.key_column_usage does not work

From
Tom Lane
Date:
Laurence Rowe <l@lrowe.co.uk> writes:
>> I have the following query:
>>
>> SELECT key_column_usage.*,constraint_type
>> FROM information_schema.key_column_usage
>> LEFT JOIN information_schema.table_constraints USING
>> (table_schema,table_name,constraint_name)
>> WHERE table_schema='whatever' and table_name='whatever'
>> ORDER BY constraint_type, constraint_name, ordinal_position
>>
>> This works when I am logged on as 'postgres', but if I try it after logging
>> on with a different username it fails with "ERROR: relation with OID 18635
>> does not exist".

Hmph ... I recall being unable to reproduce this before, but I'm not
sure why I failed, because it's definitely broken.  The key_column_usage
view has

          FROM pg_namespace nr, pg_class r, pg_namespace nc,
               pg_constraint c
          WHERE nr.oid = r.relnamespace
                AND r.oid = c.conrelid
                AND nc.oid = c.connamespace
                AND c.contype IN ('p', 'u', 'f')
                AND r.relkind = 'r'
                AND (NOT pg_is_other_temp_schema(nr.oid))
                AND (pg_has_role(r.relowner, 'USAGE')
                     OR has_table_privilege(c.oid, 'SELECT')
                     OR has_table_privilege(c.oid, 'INSERT')
                     OR has_table_privilege(c.oid, 'UPDATE')
                     OR has_table_privilege(c.oid, 'REFERENCES')) ) AS ss

Obviously those last four lines should be r.oid not c.oid.  The bug is
masked as long as the preceding pg_has_role() test succeeds, so in
particular a superuser would never see it :-(

We won't be able to force initdb to fix this in the back branches,
but fortunately the information schema views are not hardwired in.
Just drop the view and recreate it with the corrected definition...

            regards, tom lane

Re: BUG #2848: information_schema.key_column_usage

From
Bruce Momjian
Date:
This has has been fixed in CVS HEAD and 8.2.X will get the fix if the
drop and recreate the view, or initdb.

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

Tom Lane wrote:
> Laurence Rowe <l@lrowe.co.uk> writes:
> >> I have the following query:
> >>
> >> SELECT key_column_usage.*,constraint_type
> >> FROM information_schema.key_column_usage
> >> LEFT JOIN information_schema.table_constraints USING
> >> (table_schema,table_name,constraint_name)
> >> WHERE table_schema='whatever' and table_name='whatever'
> >> ORDER BY constraint_type, constraint_name, ordinal_position
> >>
> >> This works when I am logged on as 'postgres', but if I try it after logging
> >> on with a different username it fails with "ERROR: relation with OID 18635
> >> does not exist".
>
> Hmph ... I recall being unable to reproduce this before, but I'm not
> sure why I failed, because it's definitely broken.  The key_column_usage
> view has
>
>           FROM pg_namespace nr, pg_class r, pg_namespace nc,
>                pg_constraint c
>           WHERE nr.oid = r.relnamespace
>                 AND r.oid = c.conrelid
>                 AND nc.oid = c.connamespace
>                 AND c.contype IN ('p', 'u', 'f')
>                 AND r.relkind = 'r'
>                 AND (NOT pg_is_other_temp_schema(nr.oid))
>                 AND (pg_has_role(r.relowner, 'USAGE')
>                      OR has_table_privilege(c.oid, 'SELECT')
>                      OR has_table_privilege(c.oid, 'INSERT')
>                      OR has_table_privilege(c.oid, 'UPDATE')
>                      OR has_table_privilege(c.oid, 'REFERENCES')) ) AS ss
>
> Obviously those last four lines should be r.oid not c.oid.  The bug is
> masked as long as the preceding pg_has_role() test succeeds, so in
> particular a superuser would never see it :-(
>
> We won't be able to force initdb to fix this in the back branches,
> but fortunately the information schema views are not hardwired in.
> Just drop the view and recreate it with the corrected definition...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +