Re: ALTER TABLE ... REPLACE WITH

On Wed, Dec 15, 2010 at 11:30 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)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