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