Re: transactional swap of tables

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

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company