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;