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

So I did miss something!  Thanks for the brilliant explanation and simple s=
olution, Tom.

Bob

--- On Thu, 3/18/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [BUGS] Error while altering an inheritance hierarchy in mid-=
query
> To: "Bob Lunney" <bob_lunney@yahoo.com>
> Cc: pgsql-bugs@postgresql.org
> Date: Thursday, March 18, 2010, 3:26 PM
> Bob Lunney <bob_lunney@yahoo.com>
> writes:
> > 1.=A0 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.=A0 Then=20
> >=A0=A0=A0a.=A0 a transaction is begun,
> >=A0=A0=A0b.=A0 the original partition is
> dropped,=20
> >=A0=A0=A0c.=A0 the new table renamed to the
> original partition's name,=20
> >=A0=A0=A0d.=A0 the new table's unique index
> is renamed,
> >=A0=A0=A0e.=A0 the appropriate check
> constraint is added,=20
> >=A0=A0=A0f.=A0 select privilege is granted,
> and
> >=A0=A0=A0g.=A0 the transaction is
> committed.
>=20
> I'd suggest taking an exclusive lock on the inheritance
> hierarchy's
> parent table between 2a and 2b.=A0 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.
>=20
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0
> regards, tom lane
>=20