On 26/09/2018 23:48, Peter Eisentraut wrote:
> On 26/09/2018 17:54, Alvaro Herrera wrote:
>> What could be the use for the transaction timestamp? I think one of the
>> most important uses (at least in pg_stat_activity) is to verify that
>> transactions are not taking excessively long time to complete; that's
>> known to cause all sorts of trouble in Postgres, and probably other
>> DBMSs too. If we don't accurately measure what it really is, and
>> instead keep the compatibility behavior, we risk panicking people
>> because they think some transaction has been running for a long time
>> when in reality it's just a very long procedure which commits frequently
>> enough not to be a problem.
>
> That's certainly a good argument. Note that if we implemented that the
> transaction timestamp is advanced inside procedures, that would also
> mean that the transaction timestamp as observed in pg_stat_activity
> would move during VACUUM, for example. That might or might not be
> desirable.
Attached is a rough implementation.
I'd be mildly in favor of doing this, but we have mentioned tradeoffs in
this thread.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 2a8c214479adb4b98c8b6c95875d8bebd88cb940 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Fri, 28 Sep 2018 09:33:24 +0200
Subject: [PATCH] Advance transaction timestamp in intra-procedure transactions
---
src/backend/access/transam/xact.c | 19 ++++++++++++-------
1 file changed, 12 insertions(+), 7 deletions(-)
diff --git a/src/backend/access/transam/xact.c
b/src/backend/access/transam/xact.c
index 9aa63c8792..245735420c 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -1884,14 +1884,19 @@ StartTransaction(void)
TRACE_POSTGRESQL_TRANSACTION_START(vxid.localTransactionId);
/*
- * set transaction_timestamp() (a/k/a now()). We want this to be the
same
- * as the first command's statement_timestamp(), so don't do a fresh
- * GetCurrentTimestamp() call (which'd be expensive anyway). Also, mark
- * xactStopTimestamp as unset.
- */
- xactStartTimestamp = stmtStartTimestamp;
- xactStopTimestamp = 0;
+ * set transaction_timestamp() (a/k/a now()). Normally, we want this to
+ * be the same as the first command's statement_timestamp(), so don't
do a
+ * fresh GetCurrentTimestamp() call (which'd be expensive anyway). But
+ * for transactions started inside statements (e.g., procedure calls),
we
+ * want to advance the timestamp.
+ */
+ if (xactStartTimestamp < stmtStartTimestamp)
+ xactStartTimestamp = stmtStartTimestamp;
+ else
+ xactStartTimestamp = GetCurrentTimestamp();
pgstat_report_xact_timestamp(xactStartTimestamp);
+ /* Mark xactStopTimestamp as unset. */
+ xactStopTimestamp = 0;
/*
* initialize current transaction state fields
--
2.19.0
