Hi all,
we have faced lately dumps not valid, the bug can be replicated using a 8.2.9 or
a 8.3.1 server.
These are the steps to create the database that will generate a not valid dump:
-------------------------------------------------------
CREATE TABLE t_public ( a integer
);
CREATE OR REPLACE FUNCTION sp_public ( )
RETURNS INTEGER AS'
BEGIN PERFORM * FROM t_public LIMIT 1; RETURN 0;
END;
' LANGUAGE 'plpgsql'
IMMUTABLE;
CREATE SCHEMA my_schema;
CREATE TABLE my_schema.table_ref(x integer primary key);
CREATE TABLE my_schema.table_test(x integer references my_schema.table_ref (x));
CREATE INDEX idx ON my_schema.table_test (x) WHERE x = sp_public();
--------------------------------
Briefly: in the public schema we have a function that uses a table.
In another schema we have a table with a foreign key to another table, and an partial
index that uses the function in the public schema.
The function is immutable because in our case the table being used inside the function
is a lookup table (readonly).
When the dump is restored the index idx is created but the foreign key is not.
This is the error we obtain during the restore:
psql:test.dump:143: ERROR: relation "t_public" does not exist
CONTEXT: SQL statement "SELECT * FROM t_public LIMIT 1"
PL/pgSQL function "sp_public" line 2 at perform
SQL statement "SELECT fk."x" FROM ONLY "my_schema"."table_test" fk LEFT
OUTER JOIN ONLY "my_schema"."table_ref" pk ON (pk."x"=fk."x") WHERE pk."x"
IS NULL AND (fk."x" IS NOT NULL)"
Regards
