On Wed, Dec 15, 2010 at 11:30 AM, Kevin Grittner <kevin.gritt...@wicourts.gov> wrote: > Heikki Linnakangas wrote: >> On 14.12.2010 20:27, Simon Riggs wrote: > >>> 1. Prepare new data into "new_table" and build indexes >>> 2. Swap old for new >>> BEGIN; >>> DROP TABLE "old_table"; >>> ALTER TABLE "new_table" RENAME to "old_table"; >>> COMMIT; >>> >>> Step (2) works, but any people queuing to access the table >>> will see ERROR: could not open relation with OID xxxxx >> >> Could we make that work without error? > > Well, that worked better for us than building up the new > contents in a temporary table and doing the sequence Tom > suggests, but to eliminate the above error we had to do: > > BEGIN; > ALTER TABLE "old_table" RENAME TO "dead_table"; > ALTER TABLE "new_table" RENAME TO "old_table"; > COMMIT; > -- Wait for all references to old OID to expire. > DROP TABLE "dead_table";
Been there, done that. Didn't buy the post-card. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
