Problems with information_schema - Mailing list pgsql-general

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
resultingscripts 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
revealsthat 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
definitionsI 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
yetbeen 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_identifierAS 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_identifierAS 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_identifierAS constraint_schema, c.conname::character varying::sql_identifier AS constraint_name,
current_database()::charactervarying::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;