Checking for old transaction snapshots

On Sun, Aug 14, 2005 at 11:26:33AM -0400, Greg Stark wrote:

I have a job that does a big batch delete/insert. I want it to vacuum (or
probably cluster) when it's finished. But I figure I should sleep for a while
before doing the vacuum to be sure there are no old transactions still
running.

Is there a simple query I can have it do against the system tables to check
for any transactions older either than when the batch delete finished?

I guess what you could do is get lock information from pg_locks. You
need to know the Xid of the transactions doing the deletes; any Xid
smaller than that is going to block the vacuum, and any one that started
after the deletes started, too. I guess you could get the list of
running Xids (from pg_locks) at the time the delete finished, and wait
until all of them are gone.

I'm also interested in verifying that I don't have the problem of the
front-end application issuing a BEGIN as soon as a script ends. Ie, starting a
transaction that will lie idle until the next page hit that process handles.

I think this one is harder to find out. However we should really fix
this problem on the server.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"No hay cielo posible sin hundir nuestras ra�ces
en la profundidad de la tierra" (Malucha Pinto)