Not valid dump [8.2.9, 8.3.1]

From: Gaetano Mendola <mendola(at)gmail(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Cc: Patrizia Grasso - Eutelsat <pgrasso(at)mbigroup(dot)it>
Subject: Not valid dump [8.2.9, 8.3.1]
Date: 2008-06-20 09:52:03
Message-ID: 485B7DC3.4060800@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zoltan Boszormenyi 2008-06-20 10:24:13 Re: posix advises ...
Previous Message Thomas Lee 2008-06-20 09:36:02 Re: Backend Stats Enhancement Request