Thread: Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
[ 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
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
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
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
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
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
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
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