Обсуждение: Not valid dump [8.2.9, 8.3.1]

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

Gaetano Mendola <mendola@gmail.com> writes:
> 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:

This is a bug in your function: it will not work if the search path
doesn't contain the public schema.  You'd be best advised to make it
qualify the reference to t_public explicitly.

In 8.3 another possibility would be to attach an explicit search_path
setting to the function.

(Of course you realize that referencing any table at all in an
"immutable" function is probably a mortal sin...)
        regards, tom lane

On Fri, Jun 20, 2008 at 4:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yes Tom I know, in our case that table is a lookup table, noone update,
delete, insert data in it, so from my point of view it is like I have declared a
static array inside the function declaration.

--
cpp-today.blogspot.com

"Gaetano Mendola" <mendola@gmail.com> writes:
> On Fri, Jun 20, 2008 at 4:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> (Of course you realize that referencing any table at all in an
>> "immutable" function is probably a mortal sin...)

> Yes Tom I know, in our case that table is a lookup table, noone update,
> delete, insert data in it, so from my point of view it is like I have
> declared a static array inside the function declaration.

No, you'd like to imagine that it is a static array, but that technique
is just a foot-gun waiting to bite you.  As an example, since pg_dump
has no idea that that function has any dependency on the lookup table,
there is nothing to stop it from trying to create the index before it's
populated the lookup table.

(I think it probably works for you at the moment because pg_dump tends
to fill all the tables before creating any indexes, but the planned
changes to support multi-threaded restores will certainly break your
case.)
        regards, tom lane

Tom Lane wrote:
> "Gaetano Mendola" <mendola@gmail.com> writes:
>   
>> On Fri, Jun 20, 2008 at 4:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> (Of course you realize that referencing any table at all in an
>>     
>>> "immutable" function is probably a mortal sin...)
>>>       
>
>   
>> Yes Tom I know, in our case that table is a lookup table, noone update,
>> delete, insert data in it, so from my point of view it is like I have
>> declared a static array inside the function declaration.
>>     
>
> No, you'd like to imagine that it is a static array, but that technique
> is just a foot-gun waiting to bite you.  As an example, since pg_dump
> has no idea that that function has any dependency on the lookup table,
> there is nothing to stop it from trying to create the index before it's
> populated the lookup table.
>
> (I think it probably works for you at the moment because pg_dump tends
> to fill all the tables before creating any indexes, but the planned
> changes to support multi-threaded restores will certainly break your
> case.)
>
>             
>   

Purely static lookup tables can also often be replaced by enum types, 
often with significant efficiency gains.

cheers

andrew