So I did miss something! Thanks for the brilliant explanation and simple solution, Tom.
Bob --- On Thu, 3/18/10, Tom Lane <t...@sss.pgh.pa.us> wrote: > From: Tom Lane <t...@sss.pgh.pa.us> > Subject: Re: [BUGS] Error while altering an inheritance hierarchy in mid-query > To: "Bob Lunney" <bob_lun...@yahoo.com> > Cc: pgsql-bugs@postgresql.org > Date: Thursday, March 18, 2010, 3:26 PM > Bob Lunney <bob_lun...@yahoo.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 > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
