Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

Michael Paesold wrote:
> 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.

Yes, CURRENT_TIMESTAMP changing based on transaction serializable/read
commited would be quite confusing. Also, because our default is read
committed, we would end up with CURRENT_TIMESTAMP being statement level,
which actually does give us a logical place to allow CURRENT_TIMESTAMP
to change, but I thought people voted against that.

However, imagine a query that used CURRENT_TIMESTAMP in the WHERE clause
to find items that were not in the future. Would a CURRENT_TIMESTAMP
test in a multi-statement transaction want to check based on transaction
start, or on the tuples visible at the time the statement started?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073