Re: transction_timestamp() inside of procedures

On 22/09/2018 00:35, Bruce Momjian wrote:
> I have always thought of clock/statement/transation as decreasing levels
> of time precision, and it might be odd to change that. I don't think we
> want to change the behavior of statement_timestamp() in procedures, so
> that kind of requires us not to change transaction_timestamp() inside of
> procedures.
>
> However, no change in behavior causes the problem that if you have a
> transaction block using transaction_timestamp() or CURRENT_TIMESTAMP,
> and you move it into a procedure, the behavior of those functions will
> change, but this was always true of moving statement_timestamp() into a
> function, and I have never heard a complaint about that.

Right. Statement timestamp actually means the timestamp of the
top-level statement, because it's set where the protocol interaction
happens. The transaction timestamp is implemented as the statement
timestamp when the transaction starts, but for intra-procedural
transactions, the statement timestamp does not advance, so the
transaction timestamp doesn't change.

Note that this also affects the xact_start column in pg_stat_activity.

We could certainly address this by adding three or four or five new
timestamps that cover all these varieties. But perhaps it's worth
asking what these timestamps are useful for and which ones we really need.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services