4

I am reviewing over the pg_stat_activity view. And there are two fields that are indistinguishable to me:

xact_start:The time at which the transaction started to run which could be different than the query_start but should always have occurred before the query_start time.

query_start: The time at which the query started to run. This can be useful to determine if a query has been running for a long time.

It says they both determine the start time of the query. It also says that xact_start can be different than query_start, but doesn't explain why - only that it should start prior to query_start. But what is the actual difference between the two? Why would I care about xact_start?

6

It also says that xact_start can be different than query_start, but doesn't explain why

It differs when the transaction encapsulates several queries, for example:

BEGIN;  -- starts at t0, beginning of transaction
query1; -- starts at t1
query2; -- starts at t2
COMMIT; -- starts at t4, end of transaction

If we look at the pg_stat_activity entry for this session while query2 is running, xact_starts will be t0 and query_start will be t2, with t2>t0, the difference being essentially the duration of query1.

xact_start helps discovering long running transactions, which is useful when troubleshooting or tuning a live database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.