Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > So, in summary, reasons for the change:
> > more intuitive
> > more standard-compliant
> > more closely matches other db's
>
> I'd give you the first and third of those. As Andrew noted, the
> argument that "it's more standard-compliant" is not very solid.

The standard doesn't say anything about transaction in this regard. I
actually think Oracle is closer to the standard than we are right now.

> > Reasons not to change:
> > PostgreSQL traditional behavior
>
> You've phrased that in a way that makes it sound like the decision
> is a no-brainer. How about
>
> Breaks existing Postgres applications in non-obvious ways
>
> which I think is a more realistic description of the downside.

I had used Andrew's words:

the traditional Postgres behaviour of time "freezing" in transaction.

Yes, "breaking" is a clearer description.

> Also, it seems a lot of people who have thought about this carefully
> think that the start-of-transaction behavior is just plain more useful.
> The fact that it surprises novices is not a reason why people who know
> the behavior shouldn't want it to work like it does. (The behavior of
> nextval/currval for sequences surprises novices, too, but I haven't
> heard anyone claim we should change it because of that.)

No one has suggested a more intuitive solution for sequences, or we
would have discussed it.

> So I think a fairer summary is
>
> Pro:
>
> more intuitive (but still not what an unversed person would
> expect, namely true current time)
> arguably more standard-compliant

What does "arguably" mean? That seems more like a throw-away objection.

> more closely matches other db's (but still not very closely)

Closer!

No need to qualify what I said. It is "more" of all these things, not
"exactly", of course.

> Con:
>
> breaks existing Postgres applications in non-obvious ways
> arguably less useful than our traditional behavior
>
> I've got no problem with the idea of adding a way to get at
> statement-arrival time. (I like the idea of a parameterized version of
> now() to provide a consistent interface to all three functionalities.)
> But I'm less than enthused about changing the existing functions to give
> pride of place to statement-arrival time. In the end, I think that
> transaction-start time is the most commonly useful and safest variant,
> and so I feel it ought to have pride of place as the easiest one to get
> at.

Well, let's see what others say. If no one is excited about the change,
we can just document its current behavior. Oh, I see it is already
documented in func.sgml:

It is quite important to realize that
<function>CURRENT_TIMESTAMP</function> and related functions all return
the time as of the start of the current transaction; their values do not
increment while a transaction is running. But
<function>timeofday()</function> returns the actual current time.

Seems that isn't helping enough to reduce the number of people who are
surprised by our behavior. I don't think anyone would be surprised by
statement time.

What do others think?

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