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

Re: Error while altering an inheritance hierarchy in mid-query

From: Bob Lunney <bob_lunney(at)yahoo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Error while altering an inheritance hierarchy in mid-query
Date: 2010-03-19 03:22:14
Message-ID: 633121.66821.qm@web39706.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-bugs
So I did miss something!  Thanks for the brilliant explanation and simple solution, Tom.

Bob

--- On Thu, 3/18/10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Subject: Re: [BUGS] Error while altering an inheritance hierarchy in mid-query
> To: "Bob Lunney" <bob_lunney(at)yahoo(dot)com>
> Cc: pgsql-bugs(at)postgresql(dot)org
> Date: Thursday, March 18, 2010, 3:26 PM
> Bob Lunney <bob_lunney(at)yahoo(dot)com>
> writes:
> > 1.  A select into query is run which summarizes
> the data from a partition into a table outside the
> inheritance hierarchy, which is then indexed.
> > 2.  Then 
> >   a.  a transaction is begun,
> >   b.  the original partition is
> dropped, 
> >   c.  the new table renamed to the
> original partition's name, 
> >   d.  the new table's unique index
> is renamed,
> >   e.  the appropriate check
> constraint is added, 
> >   f.  select privilege is granted,
> and
> >   g.  the transaction is
> committed.
> 
> I'd suggest taking an exclusive lock on the inheritance
> hierarchy's
> parent table between 2a and 2b.  The "could not open
> relation with OID
> nnn" error is to be expected when a table is dropped just
> as a query
> is in the act of trying to open it, which is what could
> happen here if
> a query on the parent table runs concurrently with the
> DROP.
> You're also at risk that a concurrent query might see both
> or neither
> of the old and new versions of the partition, leading to
> bogus answers.
> Both of these things would be fixed if incoming queries are
> blocked
> while trying to open the parent table, rather than while
> iterating
> through the list of inheritance children for it.
> 
>            
> regards, tom lane
> 


      

In response to

pgsql-bugs by date

Next:From: KrishnamoorthyDate: 2010-03-19 05:30:30
Subject: BUG #5380: error in xlog
Previous:From: Tom LaneDate: 2010-03-18 19:26:32
Subject: Re: Error while altering an inheritance hierarchy in mid-query

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