Re: ALTER TABLE ... REPLACE WITH

Lists: pgsql-hackers
From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <simon(at)2ndQuadrant(dot)com>,<heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: ALTER TABLE ... REPLACE WITH
Date: 2010-12-15 16:30:40
Message-ID: 4D0898D002000025000386A7@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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";

We don't put foreign keys on the table we do this with;
it's rebuilt from the related tables weekly....

-Kevin

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: simon(at)2ndquadrant(dot)com, heikki(dot)linnakangas(at)enterprisedb(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: ALTER TABLE ... REPLACE WITH
Date: 2010-12-15 16:34:59
Message-ID: AANLkTimAxHrW7-8ZtHEd9qxB0ErHU4e6BR_KpARv5GmY@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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