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;
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
