Checking for old transaction snapshots

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Checking for old transaction snapshots

Greg Stark-3

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

--
greg


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Checking for old transaction snapshots

Álvaro Herrera
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)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend