Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group