Re: transction_timestamp() inside of procedures

On 2018-09-26 12:54:43 -0300, Alvaro Herrera wrote:
> On 2018-Sep-26, Tom Lane wrote:
>
> > Alvaro Herrera <alvherre(at)2ndquadrant(dot)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.

+1

> > 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.

+1

Greetings,

Andres Freund