Re: transactional swap of tables

Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> wrote:

> Would you please elaborate more on the "wait[ing] long enough
> after the COMMIT" ?

You can note the time when you commit the transaction, and then
poll pg_stat_activity until there are no active transactions which
started before that.

You can sometimes simplify this a little.  In the case where I did
something like this, the only use of the table was by a web
application which timed out any query which didn't complete in 20
seconds.  To that web team, and those using that web application,
there is absolutely no difference between a query which takes more
than 20 seconds and one where PostgreSQL throws an error; the
resulting user-facing behavior is identical.  So if, after waiting
more than 20 seconds, a query blocked long enough to get an error
on trying to access the dropped table OID, that made no difference
to them -- the user would have already been given an error.  In
that situation I was comfortable just hard-coding a 60-second sleep
before dropping the old table.

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