Vincenzo Romano <vincenzo.romano@notorand.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