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;