Thread: pg_dump: schema with OID XXXXX does not exist - was Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
pg_dump: schema with OID XXXXX does not exist - was Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
From
Nikhil Sontakke
Date:
Hi,
After Daniel's hunch about pg_dump barfing due to such leftover entries proving out to be true, we have one credible explanation (there might be other reasons too) for this long standing issue. I see some reports from as early as 2004 and some as latest as Feb, 2011!
http://archives.postgresql.org/pgsql-bugs/2011-02/msg00185.php
One way in 9.x could be to modify get_object_address to additionally accept objoid as an argument and use that to lock the schema in AccessShareLock mode from all places where schema based objects (tables, views, types, sequences, functions, indexes, extensions, constraints, operators stuff, ts stuff, rules, domains, etc. phew!) can be created. Or since this is schema specific, we can as well right a new function to do this. We might also add logic to only lock user created schemas.
This can be added right after the namespace for the involved object has been correctly identified. The lock can then get released later as part of the transaction commit.
Regards,
Nikhils
But if it's deemed to be a
problem, I want to see a solution that's actually watertight.)
After Daniel's hunch about pg_dump barfing due to such leftover entries proving out to be true, we have one credible explanation (there might be other reasons too) for this long standing issue. I see some reports from as early as 2004 and some as latest as Feb, 2011!
http://archives.postgresql.org/pgsql-bugs/2011-02/msg00185.php
One way in 9.x could be to modify get_object_address to additionally accept objoid as an argument and use that to lock the schema in AccessShareLock mode from all places where schema based objects (tables, views, types, sequences, functions, indexes, extensions, constraints, operators stuff, ts stuff, rules, domains, etc. phew!) can be created. Or since this is schema specific, we can as well right a new function to do this. We might also add logic to only lock user created schemas.
This can be added right after the namespace for the involved object has been correctly identified. The lock can then get released later as part of the transaction commit.
Regards,
Nikhils
Re: pg_dump: schema with OID XXXXX does not exist - was Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
From
Daniel Farina
Date:
On Thu, Nov 10, 2011 at 11:57 PM, Nikhil Sontakke <nikkhils@gmail.com> wrote: > Hi, > >> >> But if it's deemed to be a >> problem, I want to see a solution that's actually watertight.) >> > > After Daniel's hunch about pg_dump barfing due to such leftover entries > proving out to be true, we have one credible explanation (there might be > other reasons too) for this long standing issue. I see some reports from as > early as 2004 and some as latest as Feb, 2011! The 2004 report was in version 7.3, released in 2002. So it's Very Nearly a ten year old bug, and may very well go back further back in time. -- fdr
Re: Re: pg_dump: schema with OID XXXXX does not exist - was Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
From
Alvaro Herrera
Date:
Excerpts from Daniel Farina's message of vie nov 11 16:08:01 -0300 2011: > On Thu, Nov 10, 2011 at 11:57 PM, Nikhil Sontakke <nikkhils@gmail.com> wrote: > > Hi, > > > >> > >> But if it's deemed to be a > >> problem, I want to see a solution that's actually watertight.) > >> > > > > After Daniel's hunch about pg_dump barfing due to such leftover entries > > proving out to be true, we have one credible explanation (there might be > > other reasons too) for this long standing issue. I see some reports from as > > early as 2004 and some as latest as Feb, 2011! > > The 2004 report was in version 7.3, released in 2002. So it's Very > Nearly a ten year old bug, and may very well go back further back in > time. Wasn't 7.3 the release that introduced schemas in the first place? I wonder if there's any other kind of "container" sort of object that could present a similar problem, in releases prior to that. If we delay fixing it for 16 more days, it would last nine years. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: Re: pg_dump: schema with OID XXXXX does not exist - was Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Daniel Farina's message of vie nov 11 16:08:01 -0300 2011:
>> The 2004 report was in version 7.3, released in 2002. So it's Very
>> Nearly a ten year old bug, and may very well go back further back in
>> time.
> Wasn't 7.3 the release that introduced schemas in the first place?
I think there's a very good chance that the older reports with similar
symptoms are completely unrelated, anyhow.
regards, tom lane
Re: Re: pg_dump: schema with OID XXXXX does not exist - was Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
From
Nikhil Sontakke
Date:
<br /> <div class="gmail_quote"><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solidrgb(204, 204, 204); padding-left: 1ex;"><div class="im"> > Wasn't 7.3 the release that introduced schemas in thefirst place?<br /><br /></div>I think there's a very good chance that the older reports with similar<br /> symptoms arecompletely unrelated, anyhow.<br /><br /></blockquote><br />Tom Lane is reluctant and that should tell me something :)<br/><br />So unless the list feels that this should be fixed and also agrees on the general approach, I will not toucha single line of code. Obviously someone else is welcome to have a stab at this too.<br /><br />Regards,<br />Nikhils<br/></div>