Re: transactional swap of tables

On Fri, Jul 12, 2013 at 9:24 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> wrote:
>
>> I'd like to "replace" a full table F with an empty one E.
>> In order to do this I see only one way:
>>
>> ALTER TABLE F RENAME TO T;
>> ALTER TABLE E RENAME TO F;
>> ALTER TABLE T RENAME TO E; -- optional
>>
>> This implies there's a moment when the full table doesn't exist.
>> Would a transaction enclosure ensure that the table F will be
>> always available to all clients?
>
> Yes. What you show is safe. What has a race condition is dropping
> the old table before all transactions which started with it have
> completed. If you're going to drop the old table, be sure to wait
> long enough after the COMMIT for things to "settle".

By advised that when doing rename based swaps all depending structures
(foreign keys, views, table based composite types, etc) will still be
pointing at the old table.

merlin