Transaction start in pg_stat_activity

Attached is a first revision of a patch that adds a column to
pg_stat_activity containing the time at which the backend's current
transaction began, or NULL if the backend is not inside a transaction.
This is useful for finding long-running transactions, and AFAIK this
information is not otherwise easily available from outside the backend's
session.

Naturally, this is for 8.3.

-Neil

On Sat, 2006-11-18 at 21:44 -0500, Neil Conway wrote:

> Attached is a first revision of a patch that adds a column to
> pg_stat_activity containing the time at which the backend's current
> transaction began, or NULL if the backend is not inside a transaction.
> This is useful for finding long-running transactions, and AFAIK this
> information is not otherwise easily available from outside the backend's
> session.
>
> Naturally, this is for 8.3.

Great idea. Would it be appropriate to show the time the current
snapshot was taken also/instead?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> Great idea. Would it be appropriate to show the time the current
> snapshot was taken also/instead?

There is no way we are putting a gettimeofday() call into
GetSnapshotData. I thought you were focused on performance??

regards, tom lane

On Mon, 2006-11-20 at 10:58 -0500, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > Great idea. Would it be appropriate to show the time the current
> > snapshot was taken also/instead?
>
> There is no way we are putting a gettimeofday() call into
> GetSnapshotData. I thought you were focused on performance??

LOL.

My understanding was there was already a gettimeofday() call per
statement which is displayed in pg_stat_activity. It seems relatively
straightforward to have another column which is *not* updated for each
statement when we are in SERIALIZABLE mode and CommandId > 1.

So I wasn't talking about issuing any additional gettimeofday() calls at
all. :-)

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> On Mon, 2006-11-20 at 10:58 -0500, Tom Lane wrote:
>> There is no way we are putting a gettimeofday() call into
>> GetSnapshotData. I thought you were focused on performance??

> My understanding was there was already a gettimeofday() call per
> statement which is displayed in pg_stat_activity. It seems relatively
> straightforward to have another column which is *not* updated for each
> statement when we are in SERIALIZABLE mode and CommandId > 1.

What for? The proposal already covers transaction start and statement
start, and those are the only two timestamps available (without adding
extra gettimeofday() calls). What you propose will merely repeat one of
them.

regards, tom lane

On Mon, 2006-11-20 at 11:32 -0500, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > On Mon, 2006-11-20 at 10:58 -0500, Tom Lane wrote:
> >> There is no way we are putting a gettimeofday() call into
> >> GetSnapshotData. I thought you were focused on performance??
>
> > My understanding was there was already a gettimeofday() call per
> > statement which is displayed in pg_stat_activity. It seems relatively
> > straightforward to have another column which is *not* updated for each
> > statement when we are in SERIALIZABLE mode and CommandId > 1.
>
> What for? The proposal already covers transaction start and statement
> start, and those are the only two timestamps available (without adding
> extra gettimeofday() calls). What you propose will merely repeat one of
> them.

That's true, but you don't know which one is the snapshot timestamp. To
do that we need to either:
1. record the transaction isolation level of the snapshot, then document
the rule by which one would determine the snapshot timestamp.
2. record the timestamp of the snapshot directly

Either way you need another column.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> That's true, but you don't know which one is the snapshot timestamp.

You're assuming there is such a thing as "the" unique active snapshot,
an assumption I find highly dubious. In any case, the reasons for
wanting to know which transactions are old have to do with the behavior
of VACUUM, and that only pays attention to the age of the whole
transaction not any individual snapshots. So I still don't see the
point of cluttering pg_stat_activity with yet more columns.

regards, tom lane