From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Andres Freund <andres@anarazel.de>
Subject: Re: transction_timestamp() inside of procedures
Date: Fri, 28 Sep 2018 09:35:48 +0200
Message-ID: <e2ad8aaf-e1d8-499e-4de0-e3616f8d908d@2ndquadrant.com> (raw)
In-Reply-To: <ec816bd0-0357-23a9-bea2-3ef1619eb340@2ndquadrant.com>
References: <20180926155443.u4zh7jactzflys3z@alvherre.pgsql>
<ec816bd0-0357-23a9-bea2-3ef1619eb340@2ndquadrant.com>
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
Attachments:0001-Advance-transaction-timestamp-in-intra-procedure-tra.patch (text/plain, 1.6K) download
From 2a8c214479adb4b98c8b6c95875d8bebd88cb940 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@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.cindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, alvherre@2ndquadrant.com, tgl@sss.pgh.pa.us, bruce@momjian.us, michael@paquier.xyz, andres@anarazel.de
Subject: Re: transction_timestamp() inside of procedures
In-Reply-To: <e2ad8aaf-e1d8-499e-4de0-e3616f8d908d@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox