Re: BUG #2848: information_schema.key_column_usage

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(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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