Finding if old transactions are running...

I was thinking about one of the "pathological cases" where Slony-I
behaves badly, and had an optimization thought...

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