Re: transction_timestamp() inside of procedures

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 +