Finding if old transactions are running...

There is a cleanup loop that (commonly) runs every 10 minutes or so
and vacuums the tables that are used by the replication application.

If there is some long-running transaction kicking around, this will
keep that from actually cleaning things out.

Consider the scenario where the system is pretty busy because of that
long-running transaction...

.. Add in that plenty of updates are going in ...

And so you have a very busy system.

Now add insult to injury in view that the VACUUM adds to the load but
doesn't actually accomplish anything useful because the lingering old
transaction keeps any tuples from being vacuumed out.

The obvious question: Why bother with the VACUUM? Why don't we just
skip it (or do an ANALYZE instead; cheaper, and at least improves the
stats...)?

Alas and alack, the only place I can think of offhand where I can
determine any "global" information on the age of transactions on the
system is to look at pg_stat_activity, and that provides only pretty
limited information, and that only if query monitoring is turned on.

[Wishful thinking...]

It sure would be nice to be able to have a way to query the start time
of the eldest transaction on the system. If that could be done at a
not-too-high cost, it would be eminently helpful for various sorts of
maintenance processes so that you could assortedly:

a) Be able to know that I should do an ANALYZE rather than wasting
system resources on a futile VACUUM;

b) Find a PID that is misbehaving by running transactions that run
9 hours contrary to production policy, and trace it back to
the client so you can then "THWACK!" them.

I could live with less than perfection, as long as I don't get false
positives...
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/x.html
"A army's effectiveness depends on its size, training, experience and
morale, and morale is worth more than all the other factors combined."
-- Napoleon Bonaparte

> It sure would be nice to be able to have a way to query the start time
> of the eldest transaction on the system. If that could be done at a
> not-too-high cost, it would be eminently helpful for various sorts of
> maintenance processes so that you could assortedly:

You can get that from pg_stat_activity, if you have the relevant stats
turned on.

Chris

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
>> It sure would be nice to be able to have a way to query the start time
>> of the eldest transaction on the system. If that could be done at a
>> not-too-high cost, it would be eminently helpful for various sorts of
>> maintenance processes so that you could assortedly:

> You can get that from pg_stat_activity, if you have the relevant stats
> turned on.

pg_stat_activity will tell you about the oldest active query, but not
about oldest open transaction.

regards, tom lane

On Thu, Feb 24, 2005 at 11:14:07AM -0500, Tom Lane wrote:
> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
> >> It sure would be nice to be able to have a way to query the start time
> >> of the eldest transaction on the system. If that could be done at a
> >> not-too-high cost, it would be eminently helpful for various sorts of
> >> maintenance processes so that you could assortedly:
>
> > You can get that from pg_stat_activity, if you have the relevant stats
> > turned on.
>
> pg_stat_activity will tell you about the oldest active query, but not
> about oldest open transaction.

You can get list of currently runing transactions from pg_locks table,
but no start time...
But if you can remember oldest transaction_id from the last vacuum then
you got what you need: if oldest transaction still here - you not
need to vacuum

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
> >> It sure would be nice to be able to have a way to query the start time
> >> of the eldest transaction on the system. If that could be done at a
> >> not-too-high cost, it would be eminently helpful for various sorts of
> >> maintenance processes so that you could assortedly:
>
> > You can get that from pg_stat_activity, if you have the relevant stats
> > turned on.
>
> pg_stat_activity will tell you about the oldest active query, but not
> about oldest open transaction.

And pg_stat_activity can lose information when the network is under
heavy load too.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

>> > You can get that from pg_stat_activity, if you have the relevant stats
>> > turned on.
>>
>> pg_stat_activity will tell you about the oldest active query, but not
>> about oldest open transaction.
>
> And pg_stat_activity can lose information when the network is under
> heavy load too.

On a side note, a similar issue came up with libpqxx, in the part that
deals with connections being lost while committing a transaction. The
library tries to reconnect and figure out whether the commit completed or
not, but it was pointed out that the commit might actually still be in
progress by that time.

Tom, I believe you said at the time that I should check pg_stat_activity.
My current code polls it for the old backend pid. But if that is neither
100% reliable nor unconditionally available, wouldn't it be better if I
just queried pg_locks for the transaction's ID? Would that work for all
backend versions I can expect to see?

Jeroen

jtv(at)xs4all(dot)nl writes:
> Tom, I believe you said at the time that I should check pg_stat_activity.
> My current code polls it for the old backend pid. But if that is neither
> 100% reliable nor unconditionally available, wouldn't it be better if I
> just queried pg_locks for the transaction's ID? Would that work for all
> backend versions I can expect to see?

pg_locks certainly seems like a better solution. Perhaps it didn't
exist when you went with pg_stat_activity? Can't recall offhand.

Note that you still want to look for your old backend's PID; it seems
impractically expensive to keep track of the current transaction's XID.
(At a minimum that would cost another query per xact...)

regards, tom lane

> jtv(at)xs4all(dot)nl writes:

> pg_locks certainly seems like a better solution. Perhaps it didn't
> exist when you went with pg_stat_activity? Can't recall offhand.

Neither do I... But I do need something that will work with at least any
recent backend version--say, 7.2 or since. The more the better, really.
Any idea how old pg_locks is?

> Note that you still want to look for your old backend's PID; it seems
> impractically expensive to keep track of the current transaction's XID.
> (At a minimum that would cost another query per xact...)

Yes, I see now--I thought I had the transaction ID handy already anyway,
but I didn't. Thanks.

Jeroen