Re: Problems with information_schema

Hi all

I just copied a rather complex application database together with all tables and triggers to 8.0.0RC1 running under windows (the original 7.4 database still runs on linux). I migrated everyting using pg_dump and then executing the resulting scripts via pgsql.

Most things work as expected until now, except for the following problem.

My application uses some functions that use the information_schema. Now these functions seem to fail. Further analysis reveals that some of the dictionary views (e.g. information_schema.table_constraints) always give empty result sets.

I can see several differences between 7.4 and 8.0RC1, mainly with schema-qualifiyng all names. Below are the definitions I find in the view "table_constraints".

Is this a known problem? If the problem is unknown, I could probably help to find out what's going wrong. The base tables (in schema pg_catalog) appear to be ok at first sight. Perhaps the information_schema dictionary views have not yet been debugged yet?

Best regards
--Marcel

example of diffs in view definition (as reported by pgadmin III 1.2.0 final, Nov 29, 2004):

In 8.0.0RC1:

CREATE OR REPLACE VIEW information_schema.table_constraints AS
SELECT current_database()::information_schema.sql_identifier AS constraint_catalog, nc.nspname::information_schema.sql_identifier AS constraint_schema, c.conname::information_schema.sql_identifier AS constraint_name, current_database()::information_schema.sql_identifier AS table_catalog, nr.nspname::information_schema.sql_identifier AS table_schema, r.relname::information_schema.sql_identifier AS table_name,
CASE c.contype
WHEN 'c'::"char" THEN 'CHECK'::text
WHEN 'f'::"char" THEN 'FOREIGN KEY'::text
WHEN 'p'::"char" THEN 'PRIMARY KEY'::text
WHEN 'u'::"char" THEN 'UNIQUE'::text
ELSE NULL::text
END::information_schema.character_data AS constraint_type,
CASE
WHEN c.condeferrable THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.character_data AS is_deferrable,
CASE
WHEN c.condeferred THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.character_data AS initially_deferred
FROM pg_namespace nc, pg_namespace nr, pg_constraint c, pg_class r, pg_user u
WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND r.relowner = u.usesysid AND r.relkind = 'r'::"char" AND u.usename = "current_user"();

ALTER TABLE information_schema.table_constraints OWNER TO postgres;
GRANT ALL ON TABLE information_schema.table_constraints TO postgres;
GRANT SELECT ON TABLE information_schema.table_constraints TO public;

in 7.4:

CREATE OR REPLACE VIEW information_schema.table_constraints AS
SELECT current_database()::character varying::sql_identifier AS constraint_catalog, nc.nspname::character varying::sql_identifier AS constraint_schema, c.conname::character varying::sql_identifier AS constraint_name, current_database()::character varying::sql_identifier AS table_catalog, nr.nspname::character varying::sql_identifier AS table_schema, r.relname::character varying::sql_identifier AS table_name,
CASE
WHEN c.contype = 'c'::"char" THEN 'CHECK'::text
WHEN c.contype = 'f'::"char" THEN 'FOREIGN KEY'::text
WHEN c.contype = 'p'::"char" THEN 'PRIMARY KEY'::text
WHEN c.contype = 'u'::"char" THEN 'UNIQUE'::text
ELSE NULL::text
END::character_data AS constraint_type,
CASE
WHEN c.condeferrable THEN 'YES'::text
ELSE 'NO'::text
END::character_data AS is_deferrable,
CASE
WHEN c.condeferred THEN 'YES'::text
ELSE 'NO'::text
END::character_data AS initially_deferred
FROM pg_namespace nc, pg_namespace nr, pg_constraint c, pg_class r, pg_user u
WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND r.relowner = u.usesysid AND r.relkind = 'r'::"char" AND u.usename = "current_user"();

ALTER TABLE information_schema.table_constraints OWNER TO postgres;
GRANT ALL ON TABLE information_schema.table_constraints TO postgres WITH GRANT OPTION;
GRANT SELECT ON TABLE information_schema.table_constraints TO public;

"Marcel Gsteiger" <Marcel(dot)Gsteiger(at)milprog(dot)ch> writes:
> Is this a known problem? If the problem is unknown, I could probably help to find out what's going wrong. The base tables (in schema pg_catalog) appear to be ok at first sight. Perhaps the information_schema dictionary views have not yet been debugged yet?

AFAIK all the information_schema changes since 7.4 are quite
intentional. Rather than pointing out that it's changed, you need to
show us an example where you think the new behavior is wrong.

(And please do so ASAP, because if 8.0 goes final next week, it'll be
quite hard to fix later ...)

regards, tom lane