Thread: Problems with information_schema

Problems with information_schema

From
"Marcel Gsteiger"
Date:
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;


Re: Problems with information_schema

From
Tom Lane
Date:
"Marcel Gsteiger" <Marcel.Gsteiger@milprog.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
yetbeen 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