Re: transction_timestamp() inside of procedures

On 2018-Sep-26, Tom Lane wrote:

> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > On 2018-Sep-26, Tom Lane wrote:
> >> I agree that it would be surprising for transaction timestamp to be newer
> >> than statement timestamp.  So for now at least, I'd be satisfied with
> >> documenting the behavior.
> 
> > Really?  I thought it was practically obvious that for transaction-
> > controlling procedures, the transaction timestamp would not necessarily
> > be aligned with the statement timestamp.  The surprise would come
> > together with the usage of the new feature, so existing users would not
> > be surprised in any way.
> 
> Nope.  That's the same poor reasoning we've fallen into in some other
> cases, of assuming that "the user" is a point source of knowledge.
> But DBMSes tend to interact with lots of different code.  If some part
> of application A starts using intraprocedure transactions, and then
> application B breaks because it wasn't expecting to see xact_start
> later than query_start in pg_stat_activity, you've still got a problem.

While that's true, I think it's also highly hypothetical.

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.

> I'm also a bit hesitant to invent new semantics here based on the
> assumption that we've got only one, nonoverlapping, top-level transaction
> at a time.  It's not terribly hard to imagine suspend-and-resume-
> transaction features coming down the pike at some point.  What will
> we do then?  We'll already have a definitional issue for xact_start,
> but it'll get worse the more different kinds of xact_start we have.

This is even more hypothetical.

If we can have a list or stack of running transactions, clearly a single
timestamp value is not sufficient.  We could report a single value for
"the oldest transaction", or perhaps "the transaction that's currently
active".  But if we wanted to be really thorough about it, we'd need to
report the list of timestamps for each running transaction in the
current session.  However, I don't think those future developments would
change what the transaction timestamp is, namely, the start of the
current transaction, not the start of the statement that (after possibly
many iterations) gave rise to the current transaction.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services