Re: [SQL] [GENERAL] CURRENT_TIMESTAMP - Mailing list pgsql-hackers

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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
thetime 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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073