Thread: Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

From
Bruce Momjian
Date:
[ Thread moved to hackers.]

OK, I have enough information from the various other databases to make a
proposal.  It seems the other databases, particularly Oracle, record
CURRENT_TIMESTAMP as the time of statement start.  However, it isn't the
time of statement start from the user's perspective, but rather from the
database's perspective, i.e. if you call a function that has two
statements in it, each statement could have a different
CURRENT_TIMESTAMP.

I don't think that is standards-compliant, and I don't think any of our
users want that.  What they probably want is to have a fixed
CURRENT_TIMESTAMP from the time the query is submitted until it is
completed.  We can call that the "statement arrival time" version of
CURRENT_TIMESTAMP.  I don't know if any of the other databases support
this concept, but it seems the most useful, and is closer to the
standards and to other databases than we are now.

So, we have a couple of decisions to make:
Should CURRENT_TIMESTAMP be changed to "statement arrival time"?Should now() be changed the same way?If not, should
now()and CURRENT_TIMESTAMP return the same type ofvalue?
 

One idea is to change CURRENT_TIMESTAMP to "statement arrival time", and
leave now() as transaction start time. 

Also, should we added now("val") where val can be "transaction",
"statement", or "clock"?

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


Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

From
Andrew Sullivan
Date:
On Thu, Oct 03, 2002 at 04:18:08PM -0400, Bruce Momjian wrote:
> 
> So, we have a couple of decisions to make:
> 
>     Should CURRENT_TIMESTAMP be changed to "statement arrival time"?
>     Should now() be changed the same way?
>     If not, should now() and CURRENT_TIMESTAMP return the same type of
>     value?
> 
> One idea is to change CURRENT_TIMESTAMP to "statement arrival time", and
> leave now() as transaction start time. 

A disadvantage to this, as I see it, is that users may have depended on
the traditional Postgres behaviour of time "freezing" in transaction. 
You always had to select timeofday() for moving time.  I can see an
advantage in making what Postgres does somewhat more like what other
people do (as flat-out silly as some of that seems to be).  Still, it
looks to me like the present CURRENT_TIMESTAMP implementation is at
least as much like the spec as anyone else's implementation, and more
like the spec than many of them.  So I'm still not clear on what
problem the change is going to fix, especially since it breaks with
traditional behaviour.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8                                        +1 416 646 3304
x110



Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

From
Bruce Momjian
Date:
Andrew Sullivan wrote:
> On Thu, Oct 03, 2002 at 04:18:08PM -0400, Bruce Momjian wrote:
> > 
> > So, we have a couple of decisions to make:
> > 
> >     Should CURRENT_TIMESTAMP be changed to "statement arrival time"?
> >     Should now() be changed the same way?
> >     If not, should now() and CURRENT_TIMESTAMP return the same type of
> >     value?
> > 
> > One idea is to change CURRENT_TIMESTAMP to "statement arrival time", and
> > leave now() as transaction start time. 
> 
> A disadvantage to this, as I see it, is that users may have depended on
> the traditional Postgres behavior of time "freezing" in transaction. 
> You always had to select timeofday() for moving time.  I can see an
> advantage in making what Postgres does somewhat more like what other
> people do (as flat-out silly as some of that seems to be).  Still, it
> looks to me like the present CURRENT_TIMESTAMP implementation is at
> least as much like the spec as anyone else's implementation, and more
> like the spec than many of them.  So I'm still not clear on what
> problem the change is going to fix, especially since it breaks with
> traditional behavior.

Uh, why change?  Well, we have a "tradition" issue here, and changing it
will require something in the release notes.  The big reason to change
is that most people using CURRENT_TIMESTAMP are not anticipating that it
is transaction start time, and are asking/complaining.  We had one only
this week.  If it were obvious to users when they used it, we could just
say it is our way of doing it, but in most cases it is catching people
by surprised.  Given that other DB's have CURRENT_TIMESTAMP changing
even more frequently than we think is reasonable, it would make sense to
change it so it more closely matches what people expect, both new SQL
users and users moving from other DBs.

So, in summary, reasons for the change:
more intuitivemore standard-compliantmore closely matches other db's

Reasons not to change:
PostgreSQL traditional behavior

Does that help?

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


Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

From
Tom Lane
Date:
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.

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

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

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-compliantmoreclosely matches other db's (but still not very closely)
 

Con:
breaks existing Postgres applications in non-obvious waysarguably 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.
        regards, tom lane


Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

From
Andrew Sullivan
Date:
On Thu, Oct 03, 2002 at 07:09:33PM -0400, Tom Lane wrote:

> statement-arrival time.  (I like the idea of a parameterized version of
> now() to provide a consistent interface to all three functionalities.)

I like this, too.  I think it'd be probably useful.  But. . .

> pride of place to statement-arrival time.  In the end, I think that
> transaction-start time is the most commonly useful and safest variant,

. . .I also think this is true.  If I'm doing a bunch of database
operations in one transaction, there is a remarkably good argument
that they happened "at the same time".  After all, the marked passage
of time is probably just an unfortunate side effect of the inability
of my database can't process things instantaneously.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8                                        +1 416 646 3304
x110



Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

From
Bruce Momjian
Date:
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
 




Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

From
Oliver Elphick
Date:
On Fri, 2002-10-04 at 01:41, Bruce Momjian wrote:
> 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?

I would prefer that CURRENT_TIME[STAMP] always produce the same time
within a transaction.  If it is changed, it will certainly break one of
my applications, which explicitly depends on the current behaviour.  If
you change it, please provide an alternative way of doing the same
thing.

I can see that the current behaviour might give surprising results in a
long running transaction.  Surprise could be reduced by giving the time
of first use within the transaction rather than the start of the
transaction.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "For the word of God is quick, and powerful, and       sharper than any
twoedgedsword, piercing even to the      dividing asunder of soul and spirit, and of the joints     and marrow, and is
adiscerner of the thoughts and      intents of the heart."        Hebrews 4:12 
 



Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> I can see that the current behaviour might give surprising results in a
> long running transaction.  Surprise could be reduced by giving the time
> of first use within the transaction rather than the start of the
> transaction.

[ cogitates ... ]  Hmm, we could do that, and it probably would break
few if any existing apps.  But would it really reduce the surprise
factor?  The complaints we've heard so far all seemed to come from
people who expected multiple current_timestamp calls to show advancing
times within a transaction.

Oliver's idea might be worth doing just on performance grounds: instead
of a gettimeofday() call at the start of every transaction, we'd only
have to reset a flag variable.  When and if current_timestamp is done
inside the transaction, then call the kernel to ask what time it is.
We win on every transaction that does not contain a current_timestamp
call, which is probably a good bet for most apps.  But I don't think
this does much to resolve the behavioral complaints.
        regards, tom lane