Date: Tue, 2 Oct 2018 10:16:29 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	Tom Lane <tgl@sss.pgh.pa.us>, Michael Paquier <michael@paquier.xyz>,
	Andres Freund <andres@anarazel.de>
Subject: Re: transction_timestamp() inside of procedures
Message-ID: <20181002141629.GA22680@momjian.us>
References: <20180926155443.u4zh7jactzflys3z@alvherre.pgsql>
 <ec816bd0-0357-23a9-bea2-3ef1619eb340@2ndquadrant.com>
 <e2ad8aaf-e1d8-499e-4de0-e3616f8d908d@2ndquadrant.com>
 <4ff8e6a2-c585-02fe-3f20-b9aa26fa2c4f@2ndquadrant.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4ff8e6a2-c585-02fe-3f20-b9aa26fa2c4f@2ndquadrant.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Tue, Oct  2, 2018 at 10:55:56AM +0200, Peter Eisentraut wrote:
> On 28/09/2018 09:35, Peter Eisentraut wrote:
> >> 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.
> 
> So do we want to do this or not?

I thought some more about this.  I think there are a few issues:

1  Utility:  since you can't use CALL in a transaction block, our
current code will always have transaction_timestamp() and
statement_timestamp() as identical in a procedure.  Having
transaction_timestamp() advance on COMMIT gives users a new ability.

2  Surprise:  What do people use transaction_timestamp() for and what
behavior would be most expected?

3  Other databases:  How do other database systems handle this, and the
SQL standard?

Based on 1 and 2, I suggest we change transaction_timestamp() to advance
on COMMIT in procedure, and document this.  I have no idea on #3.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +