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

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
>