Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Laurence Rowe <l(at)lrowe(dot)co(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2848: information_schema.key_column_usage does not work
Date: 2007-01-16 17:34:06
Message-ID: 21849.1168968846@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Laurence Rowe <l(at)lrowe(dot)co(dot)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

In response to

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2007-01-17 02:57:18
Subject: Re: BUG #2898: dynamic load support
Previous:From: Tom LaneDate: 2007-01-16 16:49:01
Subject: Re: BUG #2901: missing alter table online help in psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group