Re: [HACKERS] Not valid dump [8.2.9, 8.3.1]
On Fri, Jun 20, 2008 at 4:37 PM, Tom Lane [EMAIL PROTECTED] wrote: Gaetano Mendola [EMAIL PROTECTED] 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. Yes, that's the way we are fixing it. Still I have a bitter taste being able to create a working database instance that doesn't generate a valid dump. (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. -- cpp-today.blogspot.com
Re: [HACKERS] Not valid dump [8.2.9, 8.3.1]
Gaetano Mendola [EMAIL PROTECTED] writes: On Fri, Jun 20, 2008 at 4:37 PM, Tom Lane [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not valid dump [8.2.9, 8.3.1]
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: On Fri, Jun 20, 2008 at 4:37 PM, Tom Lane [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not valid dump [8.2.9, 8.3.1]
Gaetano Mendola [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
