Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> > 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?
Well, in a serializable transaction there would be no difference at all, at
least if CURRENT_TIMESTAMP is consistent within the transaction. Any changes
outside the transaction after SetQuerySnapshot would not be seen by the
transaction anyway.
In read-commited, I think it's different. If CURRENT_TIMESTAMP is frozen,
than the behavior would be the same as in serializable level, if
CURRENT_TIMESTAMP advances with each statement, the result would also
change. That is an inherent problem with read-commited though and has not so
much to do with the timestamp behavior.
Regards,
Michael Paesold