proper way to fix information_schema.key_column_usage view
От | April Lorenzen |
---|---|
Тема | proper way to fix information_schema.key_column_usage view |
Дата | |
Msg-id | 88e5673e0708111432w3e490f27g902c03bc48c0ba82@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: proper way to fix information_schema.key_column_usage view
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: proper way to fix information_schema.key_column_usage view (Peter Eisentraut <peter_e@gmx.net>) |
Список | pgsql-hackers |
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_constraintc 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;
В списке pgsql-hackers по дате отправления: