Re: 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)