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