Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
> > Note also, that a typical SELECT only session would not advance
> > CURRENT_TIMESTAMP at all in the typical "autocommit off" mode that
> > the Spec is all about.
>
> True, but the spec also says to default to serializable transaction
> mode. So in a single-transaction session like you are picturing,
> the successive SELECTs would all see a frozen snapshot of the database.
> Freezing CURRENT_TIMESTAMP goes right along with that, and in fact makes
> a lot of sense, because it tells you exactly what time your snapshot
> of the database state was taken.
>
> This line of thought opens another can of worms: should the behavior
> of CURRENT_TIMESTAMP depend on serializable vs. read-committed mode?
> Maybe SetQuerySnapshot is the routine that ought to capture the
> "statement-start-time" timestamp value. We could define
> CURRENT_TIMESTAMP as the time of the active database snapshot.
> Or at least offer a fourth parameter to that parameterized now() to
> return this time.
>
> regards, tom lane

That is a very good point. At least with serializable transactions it seems
perfectly reasonable to return a frozen CURRENT_TIMESTAMP. What do you think
about read-commited level? Can time be commited? ;-)
It would be even more surprising to new users if the implementation of
CURRENT_TIMESTAMP would depend on trx serialization level.

Regards,
Michael Paesold