proper way to fix information_schema.key_column_usage view

Tom Lane commits
(http://www.postgresql.org/community/weeklynews/pwn20070121.html)

- Fix incorrect permissions check in
information_schema.key_column_usage view: it was checking a
pg_constraint OID instead of pg_class OID, resulting in "relation with
OID nnnnn does not exist" failures for anyone who wasn't owner of the
table being examined. Per bug #2848 from Laurence Rowe. Note: for
existing 8.2 installations a simple version update won't fix this; the
easiest fix is to CREATE OR REPLACE this view with the corrected
definition.

and from http://www.postgresql.org/docs/8.2/interactive/release-8-2-2.html

Fix incorrect permission check in information_schema.key_column_usage view (Tom)

The symptom is "relation with OID nnnnn does not exist" errors. To get
this fix without using initdb, use CREATE OR REPLACE VIEW to install
the corrected definition found in share/information_schema.sql. Note
you will need to do this in each database.

***********************

I had to feel my way carrying out this fix, and I don't know if I did
it right - I only know that it appears I no longer have the error.
Please confirm whether I was supposed to execute all of
share/information_schema.sql --- or just the portion that CREATEs or
REPLACEs key_column_usage view.

I did not execute the whole share/information_schema.sql --- I only
executed the part of it referring to the key_column_usage view and
each of the functions necessary to support that. CREATE OR REPLACE the
key_column_usage view gave errors otherwise for numerous missing
functions. That worries me because it makes me think I should execute
the whole thing. Instead I just kept adding the function definitions
until it successfully executed.

This is against a very large database which would take a long time to
restore if running the complete information_schema.sql screws things
up. Also I am not sure I would know right away if it was screwed up or
not. So hopefully you can tell me something such as "it's just a view
of info and won't cause changes in your data"?

Here's what I executed, successfully, and apparently the error in the
log file has ceased:

CREATE FUNCTION _pg_underlying_index(oid) RETURNS oid
LANGUAGE sql STRICT STABLE
AS $$
SELECT refobjid FROM pg_catalog.pg_depend
WHERE classid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND
objid = $1 AND
refclassid = 'pg_catalog.pg_class'::pg_catalog.regclass AND
refobjsubid = 0 AND deptype = 'n'
$$;

CREATE DOMAIN cardinal_number AS integer
CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);

CREATE DOMAIN sql_identifier AS character varying;

CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
RETURNS SETOF RECORD
LANGUAGE sql STRICT IMMUTABLE
AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
pg_catalog.array_upper($1,1),
1) as g(s)';

CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
LANGUAGE sql STRICT STABLE
AS $$
SELECT (ss.a).n FROM
(SELECT information_schema._pg_expandarray(indkey) AS a
FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
WHERE (ss.a).x = $2;
$$;

CREATE or REPLACE VIEW key_column_usage AS
SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
CAST(nc_nspname AS sql_identifier) AS constraint_schema,
CAST(conname AS sql_identifier) AS constraint_name,
CAST(current_database() AS sql_identifier) AS table_catalog,
CAST(nr_nspname AS sql_identifier) AS table_schema,
CAST(relname AS sql_identifier) AS table_name,
CAST(a.attname AS sql_identifier) AS column_name,
CAST((ss.x).n AS cardinal_number) AS ordinal_position,
CAST(CASE WHEN contype = 'f' THEN
_pg_index_position(_pg_underlying_index(ss.coid),
ss.confkey[(ss.x).n])
ELSE NULL
END AS cardinal_number)
AS position_in_unique_constraint
FROM pg_attribute a,
(SELECT r.oid AS roid, r.relname, nc.nspname AS nc_nspname,
nr.nspname AS nr_nspname,
c.oid AS coid, c.conname, c.contype, c.confkey, c.confrelid,
_pg_expandarray(c.conkey) AS x
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(r.oid, 'SELECT')
OR has_table_privilege(r.oid, 'INSERT')
OR has_table_privilege(r.oid, 'UPDATE')
OR has_table_privilege(r.oid, 'REFERENCES')) ) AS ss
WHERE ss.roid = a.attrelid
AND a.attnum = (ss.x).x
AND NOT a.attisdropped;

GRANT SELECT ON key_column_usage TO PUBLIC;

"April Lorenzen" <outboundindex(at)gmail(dot)com> writes:
> I had to feel my way carrying out this fix, and I don't know if I did
> it right - I only know that it appears I no longer have the error.
> Please confirm whether I was supposed to execute all of
> share/information_schema.sql --- or just the portion that CREATEs or
> REPLACEs key_column_usage view.

You should only have had to CREATE OR REPLACE the one view.

> I did not execute the whole share/information_schema.sql --- I only
> executed the part of it referring to the key_column_usage view and
> each of the functions necessary to support that. CREATE OR REPLACE the
> key_column_usage view gave errors otherwise for numerous missing
> functions. That worries me because it makes me think I should execute
> the whole thing. Instead I just kept adding the function definitions
> until it successfully executed.

What it sounds like to me is you had the wrong search_path set, and
so what you've done is create a new copy of the view (and supporting
functions) in whichever schema was first in your search path ---
possibly "public". This isn't a good fix. You should clean out the
extraneous view and supporting functions, and try again with
set search_path = information_schema;

regards, tom lane

April Lorenzen wrote:
> I had to feel my way carrying out this fix, and I don't know if I did
> it right - I only know that it appears I no longer have the error.
> Please confirm whether I was supposed to execute all of
> share/information_schema.sql --- or just the portion that CREATEs or
> REPLACEs key_column_usage view.

If you don't have any dependencies on the information schema (that is,
you have yourself created objects that refer to the information schema,
which should be rare), it is safe to just drop the schema, that is,

DROP SCHEMA information_schema CASCADE;

and reload it

psql -f .../information_schema.sql $PGDATABASE

--
Peter Eisentraut
http://developer.postgresql.org/~petere/