pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] CURRENT_TIMESTAMP
61+ messages / 13 participants
[nested] [flat]
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-23 19:02 Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 2 replies; 61+ messages in thread
From: Manfred Koizar @ 2002-09-23 19:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql; pgsql-general@postgresql.org
On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>Manfred Koizar <mkoi-pg@aon.at> writes:
>> This has been discussed before and I know I'm going to get flamed for
>> this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP)
>> return the start time of the current transaction is a bug, or at least
>> it is not conforming to the standard.
>
>As you say, it's been discussed before.
Yes, and I hate to be annoying.
>We concluded that the spec defines the behavior as
>implementation-dependent,
AFAICT the spec requires the returned value to meet two conditions.
C1: If a statement contains more than one <datetime value function>,
they all have to return (maybe different formats of) the same value.
C2: The returned value has to represent a point in time *during* the
execution of the SQL-statement.
The only thing an implementor is free to choose is which point in time
"during the execution of the SQL-statement" is to be returned, i.e. a
timestamp in the interval between the start of the statement and the
first time when the value is needed.
The current implementation only conforms to C1.
>and therefore we can pretty much do what we want.
Start time of the statement, ... of the transaction, ... of the
session, ... of the postmaster, ... of the century?
I understand that with subselects, functions, triggers, rules etc. it
is not easy to implement the specification. If we can't do it now, we
should at least add a todo and make clear in the documentation that
CURRENT_DATE/TIME/TIMESTAMP is not SQL92/99 compliant.
Servus
Manfred
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-23 20:36 Josh Berkus <josh@agliodbs.com>
parent: Manfred Koizar <mkoi-pg@aon.at>
1 sibling, 2 replies; 61+ messages in thread
From: Josh Berkus @ 2002-09-23 20:36 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Manfred,
> C2: The returned value has to represent a point in time *during* the
> execution of the SQL-statement.
>
> The only thing an implementor is free to choose is which point in time
> "during the execution of the SQL-statement" is to be returned, i.e. a
> timestamp in the interval between the start of the statement and the
> first time when the value is needed.
>
> The current implementation only conforms to C1.
I, for one, would judge that the start time of the statement is "during the
execution"; it would only NOT be "during the execution" if it was a value
*before* the start time of the statement. It's a semantic argument.
The spec is, IMHO, rather vague on how this would relate to transactions. I
do not find it at all inconsitent that Bruce, Thomas, and co. interpreted a
transaction to be an extension of an individual SQL statement for this
purpose (at least, that's what I guess they did).
Thus, if you accept the postulates that:
1) "During" a SQL statement includes the start time of the statement, and
2) A Transaction is the equivalent of a single SQL statement for many
purposes,
Then the current behavior is a logical conclusion.
Further, we could not change that behaviour without breaking many people's
applications.
Ideally, since we get this question a lot, that a compile-time or
execution-time switch to change the behavior of current_timestamp
contextually would be nice. We just need someone who;s interested enough in
writing one.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-23 20:41 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 61+ messages in thread
From: Bruce Momjian @ 2002-09-23 20:41 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Tom Lane <tgl@sss.pgh.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Josh Berkus wrote:
> I, for one, would judge that the start time of the statement is "during the
> execution"; it would only NOT be "during the execution" if it was a value
> *before* the start time of the statement. It's a semantic argument.
>
> The spec is, IMHO, rather vague on how this would relate to transactions. I
> do not find it at all inconsitent that Bruce, Thomas, and co. interpreted a
> transaction to be an extension of an individual SQL statement for this
> purpose (at least, that's what I guess they did).
>
> Thus, if you accept the postulates that:
> 1) "During" a SQL statement includes the start time of the statement, and
> 2) A Transaction is the equivalent of a single SQL statement for many
> purposes,
> Then the current behavior is a logical conclusion.
>
> Further, we could not change that behaviour without breaking many people's
> applications.
I don't see how we can defend returning the start of the transaction as
the current_timestamp. In a multi-statement transaction, that doesn't
seem very current to me. I know there are some advantages to returning
the same value for all queries in a transaction, but is that value worth
returning such stale time information?
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-23 20:49 Josh Berkus <josh@agliodbs.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 61+ messages in thread
From: Josh Berkus @ 2002-09-23 20:49 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Tom Lane <tgl@sss.pgh.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Bruce,
> I don't see how we can defend returning the start of the transaction as
> the current_timestamp. In a multi-statement transaction, that doesn't
> seem very current to me. I know there are some advantages to returning
> the same value for all queries in a transaction, but is that value worth
> returning such stale time information?
Then what *was* the reasoning behind the current behavior?
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-23 20:53 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 61+ messages in thread
From: Bruce Momjian @ 2002-09-23 20:53 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Tom Lane <tgl@sss.pgh.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Josh Berkus wrote:
>
> Bruce,
>
> > I don't see how we can defend returning the start of the transaction as
> > the current_timestamp. In a multi-statement transaction, that doesn't
> > seem very current to me. I know there are some advantages to returning
> > the same value for all queries in a transaction, but is that value worth
> > returning such stale time information?
>
> Then what *was* the reasoning behind the current behavior?
I thought the spec required it, but now that I see it doesn't, I don't
know why it was done that way.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-23 20:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 2 replies; 61+ messages in thread
From: Tom Lane @ 2002-09-23 20:55 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: josh@agliodbs.com, Manfred Koizar <mkoi-pg@aon.at>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I don't see how we can defend returning the start of the transaction as
> the current_timestamp.
Here's an example:
CREATE RULE foo AS ON INSERT TO mytable DO
( INSERT INTO log1 VALUES (... , now(), ...);
INSERT INTO log2 VALUES (... , now(), ...) );
I think it's important that these commands store the same timestamp in
both log tables (not to mention that any now() being stored into mytable
itself generate that same timestamp).
If you scale that up just a little bit, you can devise scenarios where
successive client-issued commands (within a single transaction) want to
store the same timestamp. After all, it's only a minor implementation
detail that you chose to fire these logging operations via a rule and
not by client-side logic.
In short, there are plenty of situations where it's critical for
application correctness that a series of commands all be able to operate
with the same value of now(). I don't think that it's wise for Postgres
to try to decide where within a transaction it's safe to advance now().
That will inevitably break some applications, and it's not obvious what
the benefit is.
In short: if you want exact current time, there's timeofday(). If you
want start of transaction time, we've got that. If you want start of
current statement time, I have two questions: why, and exactly how do
you want to define current statement, considering functions, rules,
triggers, and all that other stuff that makes it interesting?
ISTM that if a client or function wants to record intratransaction
times, it can call timeofday() at the appropriate points for itself.
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 00:11 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-09-24 00:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, Manfred Koizar <mkoi-pg@aon.at>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
I see what you are saying now --- that even single user statements can
trigger multiple statements, so you would have to say transaction start
time is time the user query starts. I can see how that seems a little
arbitrary. However, don't we have separate paths for user queries and
queries sent as part of a rule?
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I don't see how we can defend returning the start of the transaction as
> > the current_timestamp.
>
> Here's an example:
>
> CREATE RULE foo AS ON INSERT TO mytable DO
> ( INSERT INTO log1 VALUES (... , now(), ...);
> INSERT INTO log2 VALUES (... , now(), ...) );
>
> I think it's important that these commands store the same timestamp in
> both log tables (not to mention that any now() being stored into mytable
> itself generate that same timestamp).
>
> If you scale that up just a little bit, you can devise scenarios where
> successive client-issued commands (within a single transaction) want to
> store the same timestamp. After all, it's only a minor implementation
> detail that you chose to fire these logging operations via a rule and
> not by client-side logic.
>
> In short, there are plenty of situations where it's critical for
> application correctness that a series of commands all be able to operate
> with the same value of now(). I don't think that it's wise for Postgres
> to try to decide where within a transaction it's safe to advance now().
> That will inevitably break some applications, and it's not obvious what
> the benefit is.
>
> In short: if you want exact current time, there's timeofday(). If you
> want start of transaction time, we've got that. If you want start of
> current statement time, I have two questions: why, and exactly how do
> you want to define current statement, considering functions, rules,
> triggers, and all that other stuff that makes it interesting?
>
> ISTM that if a client or function wants to record intratransaction
> times, it can call timeofday() at the appropriate points for itself.
>
> regards, tom lane
>
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 00:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Tom Lane @ 2002-09-24 00:32 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: josh@agliodbs.com, Manfred Koizar <mkoi-pg@aon.at>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I see what you are saying now --- that even single user statements can
> trigger multiple statements, so you would have to say transaction start
> time is time the user query starts. I can see how that seems a little
> arbitrary. However, don't we have separate paths for user queries and
> queries sent as part of a rule?
We could use "time of arrival of the latest client command string",
if we wanted to do something like this. My point is that that very
arbitrarily assumes that those are the significant points within a
transaction, and that the client has no need to send multiple commands
that want to insert the same timestamp into different tables. This is
an unwarranted assumption about the client's control structure, IMHO.
A possible compromise is to dissociate now() and current_timestamp,
allowing the former to be start of transaction and the latter to be
start of client command.
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 00:37 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 61+ messages in thread
From: Bruce Momjian @ 2002-09-24 00:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, Manfred Koizar <mkoi-pg@aon.at>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I see what you are saying now --- that even single user statements can
> > trigger multiple statements, so you would have to say transaction start
> > time is time the user query starts. I can see how that seems a little
> > arbitrary. However, don't we have separate paths for user queries and
> > queries sent as part of a rule?
>
> We could use "time of arrival of the latest client command string",
> if we wanted to do something like this. My point is that that very
> arbitrarily assumes that those are the significant points within a
> transaction, and that the client has no need to send multiple commands
> that want to insert the same timestamp into different tables. This is
> an unwarranted assumption about the client's control structure, IMHO.
>
> A possible compromise is to dissociate now() and current_timestamp,
> allowing the former to be start of transaction and the latter to be
> start of client command.
I was thinking 'transaction_timestamp' for the transaction start time, and
current_timestamp for the statement start time. I would equate now()
with current_timestamp.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-24 01:19 Martijn van Oosterhout <kleptog@svana.org>
parent: Manfred Koizar <mkoi-pg@aon.at>
1 sibling, 2 replies; 61+ messages in thread
From: Martijn van Oosterhout @ 2002-09-24 01:19 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql; pgsql-general@postgresql.org
On Mon, Sep 23, 2002 at 09:02:00PM +0200, Manfred Koizar wrote:
> On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <tgl@sss.pgh.pa.us>
> >We concluded that the spec defines the behavior as
> >implementation-dependent,
>
> AFAICT the spec requires the returned value to meet two conditions.
>
> C1: If a statement contains more than one <datetime value function>,
> they all have to return (maybe different formats of) the same value.
>
> C2: The returned value has to represent a point in time *during* the
> execution of the SQL-statement.
>
> The only thing an implementor is free to choose is which point in time
> "during the execution of the SQL-statement" is to be returned, i.e. a
> timestamp in the interval between the start of the statement and the
> first time when the value is needed.
Well, what I would suggest is that when you wrap several statements into a
single transaction with begin/commit, the whole lot could be considered a
single statement (since they form an atomic transaction so in a sense they
are all executed simultaneously). And hence Postgresql is perfectly
compliant.
My second point would be: what is the point of a timestamp that keeps
changing during a transaction? If you want that, there are other functions
that serve that purpose.
> I understand that with subselects, functions, triggers, rules etc. it
> is not easy to implement the specification. If we can't do it now, we
> should at least add a todo and make clear in the documentation that
> CURRENT_DATE/TIME/TIMESTAMP is not SQL92/99 compliant.
The current definition is, I would say, the most useful definition. Can you
give an example where your definition would be more useful?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 01:53 Josh Berkus <josh@agliodbs.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 61+ messages in thread
From: Josh Berkus @ 2002-09-24 01:53 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Tom, Bruce,
> > A possible compromise is to dissociate now() and current_timestamp,
> > allowing the former to be start of transaction and the latter to be
> > start of client command.
>
> I was thinking 'transaction_timestamp' for the transaction start time, and
> current_timestamp for the statement start time. I would equate now()
> with current_timestamp.
May I point out that this will break compatibility for those used to the
current behavior?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 02:01 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-09-24 02:01 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Manfred Koizar <mkoi-pg@aon.at>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Josh Berkus wrote:
>
> Tom, Bruce,
>
> > > A possible compromise is to dissociate now() and current_timestamp,
> > > allowing the former to be start of transaction and the latter to be
> > > start of client command.
> >
> > I was thinking 'transaction_timestamp' for the transaction start time, and
> > current_timestamp for the statement start time. I would equate now()
> > with current_timestamp.
>
> May I point out that this will break compatibility for those used to the
> current behavior?
I am not saying we have to make that change. My point is that our
current behavior may not be the most intuitive, and that most people may
prefer a change. Any such change would be documented in the release
notes.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 02:20 John Hasler <john@dhh.gt.org>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: John Hasler @ 2002-09-24 02:20 UTC (permalink / raw)
To: pgsql-sql
Bruce Momjian writes:
> My point is that our current behavior may not be the most intuitive, and
> that most people may prefer a change.
I would prefer a change.
--
John Hasler
john@dhh.gt.org
Dancing Horse Hill
Elmwood, Wisconsin
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 03:17 Bruce Momjian <pgman@candle.pha.pa.us>
parent: John Hasler <john@dhh.gt.org>
0 siblings, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-09-24 03:17 UTC (permalink / raw)
To: John Hasler <john@dhh.gt.org>; +Cc: pgsql-sql
John Hasler wrote:
> Bruce Momjian writes:
> > My point is that our current behavior may not be the most intuitive, and
> > that most people may prefer a change.
>
> I would prefer a change.
Yes, I guess that is my point, that we want to make transaction _and_
statement timestamp values available, but most people are going to use
current_timestamp, and most people are going to assume it is statement
time, not transaction time.
Can I add TODO items for this:
o Make CURRENT_TIMESTAMP/now() return statement start time
o Add TRANSACTION_TIMESTAMP to return transaction start time
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 03:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 2 replies; 61+ messages in thread
From: Tom Lane @ 2002-09-24 03:35 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: josh@agliodbs.com, Manfred Koizar <mkoi-pg@aon.at>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I was thinking 'transaction_timestamp' for the transaction start time, and
> current_timestamp for the statement start time. I would equate now()
> with current_timestamp.
So you want to both (a) invent even more nonstandard syntax than we
already have, and (b) break as many traditional-Postgres applications
as you possibly can?
'transaction_timestamp' has no reason to live. It's not in the spec.
And AFAIK the behavior of now() has been well-defined since the
beginning of Postgres. If you want to change 'current_timestamp' to
conform to a rather debatable reading of the spec, then fine --- but
keep your hands off of now().
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 03:37 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 61+ messages in thread
From: Bruce Momjian @ 2002-09-24 03:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, Manfred Koizar <mkoi-pg@aon.at>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was thinking 'transaction_timestamp' for the transaction start time, and
> > current_timestamp for the statement start time. I would equate now()
> > with current_timestamp.
>
> So you want to both (a) invent even more nonstandard syntax than we
> already have, and (b) break as many traditional-Postgres applications
> as you possibly can?
No, but I would like to see you stop makeing condescending replies to
emails. How is that!
> 'transaction_timestamp' has no reason to live. It's not in the spec.
> And AFAIK the behavior of now() has been well-defined since the
> beginning of Postgres. If you want to change 'current_timestamp' to
> conform to a rather debatable reading of the spec, then fine --- but
> keep your hands off of now().
Oh, really. When you get down off your chair we can vote on it.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 03:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Tom Lane @ 2002-09-24 03:44 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: John Hasler <john@dhh.gt.org>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can I add TODO items for this:
> o Make CURRENT_TIMESTAMP/now() return statement start time
> o Add TRANSACTION_TIMESTAMP to return transaction start time
I object to both of those as phrased. If you have already unilaterally
determined the design of this feature change, then go ahead and put that
in. But I'd suggest
o Revise current-time functions to allow access to statement
start time
which doesn't presuppose the vote about how to do it.
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 03:52 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 61+ messages in thread
From: Bruce Momjian @ 2002-09-24 03:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John Hasler <john@dhh.gt.org>; pgsql-sql
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Can I add TODO items for this:
> > o Make CURRENT_TIMESTAMP/now() return statement start time
> > o Add TRANSACTION_TIMESTAMP to return transaction start time
>
> I object to both of those as phrased. If you have already unilaterally
> determined the design of this feature change, then go ahead and put that
> in. But I'd suggest
>
> o Revise current-time functions to allow access to statement
> start time
>
> which doesn't presuppose the vote about how to do it.
OK, I am still just throwing out ideas. I am not sure we even have
enough people who want statement_timestamp to put it in TODO. I do think
we have a standards issue.
My personal opinion is that most people think current_timestamp and
now() are statement start time, not transaction start time. In the past
we have told them the standard requires that but now I think we are not
even sure if that is correct.
So, I have these concerns:
our CURRENT_TIMESTAMP may not be standards compliant
even if it is, it is probably not returning the value most people want
most people don't know it is returning the transaction start time
So, we can just throw the TODO item you mentioned above with a question
mark, or we can try to figure out what to return for CURRENT_TIMESTAMP,
now(), and perhaps create a TRANSACTION_TIMESTAMP.
So, do people want to discuss it or should we just throw it in TODO with
a question mark?
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-24 08:33 Manfred Koizar <mkoi-pg@aon.at>
parent: Martijn van Oosterhout <kleptog@svana.org>
1 sibling, 1 reply; 61+ messages in thread
From: Manfred Koizar @ 2002-09-24 08:33 UTC (permalink / raw)
To: Martijn van Oosterhout <kleptog@svana.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql; pgsql-general@postgresql.org
On Tue, 24 Sep 2002 11:19:12 +1000, Martijn van Oosterhout
<kleptog@svana.org> wrote:
>Well, what I would suggest is that when you wrap several statements into a
>single transaction with begin/commit, the whole lot could be considered a
>single statement (since they form an atomic transaction so in a sense they
>are all executed simultaneously).
The people who wrote the specification knew about transactions. If
they had wanted what you describe above, they would have written:
3) If a transaction generally contains more than one reference
to one or more <datetime value function>s, then all such ref-
erences are effectively evaluated simultaneously. The time of
evaluation of the <datetime value function> during the execution
of the transaction is implementation-dependent.
But they wrote "SQL-statement", not "transaction".
>And hence Postgresql is perfectly compliant.
I'm not so sure.
>The current definition is, I would say, the most useful definition. Can you
>give an example where your definition would be more useful?
I did not write the standard, I'm only reading it. I have no problem
with an implementation that deviates from the standard "because we
know better". But we should users warn about this fact and not tell
them it is compliant.
Servus
Manfred
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 09:16 Manfred Koizar <mkoi-pg@aon.at>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 61+ messages in thread
From: Manfred Koizar @ 2002-09-24 09:16 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <josh@agliodbs.com>
wrote:
>I, for one, would judge that the start time of the statement is "during the
>execution"; it would only NOT be "during the execution" if it was a value
>*before* the start time of the statement. It's a semantic argument.
Josh, you're right, I meant closed interval.
>Further, we could not change that behaviour without breaking many people's
>applications.
>
>Ideally, since we get this question a lot, that a compile-time or
>execution-time switch to change the behavior of current_timestamp
>contextually would be nice.
Yes, GUC!
>We just need someone who;s interested enough in
>writing one.
First we need someone who decyphers SQL99's wording.
Servus
Manfred
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 09:37 Manfred Koizar <mkoi-pg@aon.at>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 61+ messages in thread
From: Manfred Koizar @ 2002-09-24 09:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; josh@agliodbs.com, Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
On Mon, 23 Sep 2002 16:55:48 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>Here's an example:
>
>CREATE RULE foo AS ON INSERT TO mytable DO
>( INSERT INTO log1 VALUES (... , now(), ...);
> INSERT INTO log2 VALUES (... , now(), ...) );
>
>I think it's important that these commands store the same timestamp in
>both log tables (not to mention that any now() being stored into mytable
>itself generate that same timestamp).
I agree. SQL99 mentions this requirement for triggers and I think we
can apply it to rules as well.
Here is another example:
BEGIN;
INSERT INTO foo VALUES (..., CURRENT_TIMESTAMP, ...);
-- wait a few seconds
INSERT INTO foo VALUES (..., CURRENT_TIMESTAMP, ...);
COMMIT;
Please don't ask me, why I would want that, but the standard demands
the timestamps to be different.
>After all, it's only a minor implementation
>detail that you chose to fire these logging operations via a rule and
>not by client-side logic.
No, it's fundamentally different whether you do something in one
SQL-statment or per a sequence of statements.
Servus
Manfred
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 09:44 Manfred Koizar <mkoi-pg@aon.at>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 61+ messages in thread
From: Manfred Koizar @ 2002-09-24 09:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; josh@agliodbs.com, Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
On Mon, 23 Sep 2002 23:35:13 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>If you want to change 'current_timestamp' to
>conform to a rather debatable reading of the spec, [...]
Well the spec may be debatable, but could you please explain why my
reading of the spec is debatable. The spec says "during the execution
of the SQL-statement". You know English is not my first language, but
as far as I have learned "during" does not mean "at any time before".
Servus
Manfred
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 11:44 Haller Christoph <Christoph.Haller@rodos.fzk.de>
0 siblings, 0 replies; 61+ messages in thread
From: Haller Christoph @ 2002-09-24 11:44 UTC (permalink / raw)
To: pgsql-sql
>
> Christoph Haller wrote:
> > Hi pgsql-sql list,
> > I did some testing around tables using a column
> > timestamp with time zone not null default now().
> > I have noticed a valuable feature:
> > As long as being inside a transaction initiated by
> > begin;
> > the return value of the now() function does not change.
> > Is this intended - as written in the documentation?
> > Can I rely on it, so future releases of PostgreSQL
> > will act the same.
>
> Yes, this is intended and will not change.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania
19026
>
I vote the now() behaviour not to change.
Regards, Christoph
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 12:08 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 61+ messages in thread
From: Christoph Haller @ 2002-09-24 12:08 UTC (permalink / raw)
To: pgsql-sql
>
> Christoph Haller wrote:
> > Hi pgsql-sql list,
> > I did some testing around tables using a column
> > timestamp with time zone not null default now().
> > I have noticed a valuable feature:
> > As long as being inside a transaction initiated by
> > begin;
> > the return value of the now() function does not change.
> > Is this intended - as written in the documentation?
> > Can I rely on it, so future releases of PostgreSQL
> > will act the same.
>
> Yes, this is intended and will not change.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania
19026
>
I vote the now() behaviour not to change.
Regards, Christoph
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 13:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 1 reply; 61+ messages in thread
From: Tom Lane @ 2002-09-24 13:26 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: josh@agliodbs.com, Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Manfred Koizar <mkoi-pg@aon.at> writes:
> On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <josh@agliodbs.com>
> wrote:
>> Ideally, since we get this question a lot, that a compile-time or
>> execution-time switch to change the behavior of current_timestamp
>> contextually would be nice.
> Yes, GUC!
I think a GUC variable is overkill, in fact potentially dangerous
(what if it's been changed without your app noticing)? I'm fine with
changing current_timestamp to be start-of-current-interactive-command,
though I'd not want to try to chop it more finely than that, for the
reasons already discussed. But I strongly feel that we should leave
the historical behavior of now() alone. There is no spec-based argument
for changing now(), since it isn't in the spec, and its behavior has
been set *and documented* in Postgres since Berkeley days.
If we leave now() alone then there's no need to create another
non-spec-compliant syntax like 'transaction_timestamp', either.
(I really don't want to see us do that, because without parens
it would mean making a new, not-in-the-spec fully-reserved word.)
BTW, as long as we are dorking with the current-time family, does
anyone want to vote for changing timeofday() to return a timestamptz
instead of a text string? There's no good argument except slavish
backward compatibility for having it return text, and we seem to be
quite willing to ignore backwards compatibility in this thread ...
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-24 14:55 Roland Roberts <roland@astrofoto.org>
parent: Martijn van Oosterhout <kleptog@svana.org>
1 sibling, 1 reply; 61+ messages in thread
From: Roland Roberts @ 2002-09-24 14:55 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
>>>>> "Martijn" == Martijn van Oosterhout <kleptog@svana.org> writes:
Martijn> Well, what I would suggest is that when you wrap several
Martijn> statements into a single transaction with begin/commit,
Martijn> the whole lot could be considered a single statement
Martijn> (since they form an atomic transaction so in a sense they
Martijn> are all executed simultaneously). And hence Postgresql is
Martijn> perfectly compliant.
FWIW, and not that I am an Oracle fan :-), Oracle seems to interpret
this the same way when using a "select sysdate from dual" inside a
transaction.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 15:05 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 61+ messages in thread
From: Josh Berkus @ 2002-09-24 15:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Manfred Koizar <mkoi-pg@aon.at>; +Cc: josh@agliodbs.com, Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
Tom,
> If we leave now() alone then there's no need to create another
> non-spec-compliant syntax like 'transaction_timestamp', either.
> (I really don't want to see us do that, because without parens
> it would mean making a new, not-in-the-spec fully-reserved word.)
So, if I've got this straight:
-- current_timestamp will return the timestamp for the beginning of the
SQL statement.
-- now() will return the timestamp for the beginning of the
transaction.
-- timeofday() will return the timestamp of the exact time the function
is called.
... thus changing only current_timestamp.
This looks fine to me, as a search-and-replace on current_timestamp is
easy. However, we need to do a better job of warning people about the
change than we did with interval() to "interval"().
Actually, can I make the proposal that *any* change that breaks
backward compatibility be mentioned in both the new version
announcement and on the download page? This would prevent a lot of
grief. If I'm kept informed of these changes, I'll be happy to write
up a user-friendly announcement/instructions on how to cope with the
change.
> BTW, as long as we are dorking with the current-time family, does
> anyone want to vote for changing timeofday() to return a timestamptz
> instead of a text string? There's no good argument except slavish
> backward compatibility for having it return text, and we seem to be
> quite willing to ignore backwards compatibility in this thread ...
No, I don't see any reason to do this. It's not like timeofday() is a
particularly logical name, anyway. Why not introduce a new function,
rightnow(), that returns timestamptz?
Better yet, how about we introduce a parameter to now()? Example:
now() or now('transaction') returns the transaction timestamp.
now('statement') returns the statement timestamp
now('immediate') returns the timestamp at the exact time the function
is called.
This would seem to me much more consistent than having 3 different
time-calls, whose names have nothing to do with the difference between
them. And it has the advantage of not breaking backward compatibility.
We could introduce the new version of now() in 7.4, encourage everyone
to use it instead of other timestamp calls, and then in 7.5 change the
behavior of current_timestamp for SQL92 compliance.
-Josh Berkus
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 15:07 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 61+ messages in thread
From: Ross J. Reedstrom @ 2002-09-24 15:07 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: Martijn van Oosterhout <kleptog@svana.org>; Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql; pgsql-general@postgresql.org
On Tue, Sep 24, 2002 at 10:33:51AM +0200, Manfred Koizar wrote:
>
> The people who wrote the specification knew about transactions. If
> they had wanted what you describe above, they would have written:
>
> 3) If a transaction generally contains more than one reference
> to one or more <datetime value function>s, then all such ref-
> erences are effectively evaluated simultaneously. The time of
> evaluation of the <datetime value function> during the execution
> of the transaction is implementation-dependent.
>
> But they wrote "SQL-statement", not "transaction".
>
> >And hence Postgresql is perfectly compliant.
>
> I'm not so sure.
>
> >The current definition is, I would say, the most useful definition. Can you
> >give an example where your definition would be more useful?
>
> I did not write the standard, I'm only reading it. I have no problem
> with an implementation that deviates from the standard "because we
> know better". But we should users warn about this fact and not tell
> them it is compliant.
At first, I also found the idea of now() freezing during a transaction
odd. But now I seems the right thing to do - I can't really come up with
a use-case for current_timestamp to vary.
For the relational algebra and transactional logic purists out there,
having current_timetamp be a fixed transaction time reinforces the
'atomicity' of a transaction - it's _supposed_ to happen all at once,
as far as the rest of the system is concerned. Many parts of the the
standard deviate from the ideals, however, probably due to the desire
of those with existing software to make it 'standards compliant' by
bending the standard, instead of fixing the software. There are places
in SQL92, especially, where if you know the exact feature set of some of
the big DBs from that era, you can imagine the conversation that lead
to inserting specific ambiguities into the document.
As you've probably noticed, SQL92 (and '99, from what I've look at in it)
are _not_ examples of the clearest, most pristine english in the world.
I sometimes wonder if the committee was actually an early attempt at
machine generated natural language, then I realize if that were true,
it would be clearer and more self-consistent. ;-)
All this is a very longwinded way for me to say leave now() as transaction
time, and get Peter to interpret this passage, to see what should happen
with current_timestamp. He seems to be one of the best at disentagling
the standards verbiage.
Ross
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 15:10 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Roland Roberts <roland@astrofoto.org>
0 siblings, 1 reply; 61+ messages in thread
From: Ross J. Reedstrom @ 2002-09-24 15:10 UTC (permalink / raw)
To: Roland Roberts <roland@astrofoto.org>; +Cc: pgsql-sql; pgsql-general@postgresql.org
On Tue, Sep 24, 2002 at 10:55:41AM -0400, Roland Roberts wrote:
> >>>>> "Martijn" == Martijn van Oosterhout <kleptog@svana.org> writes:
>
> Martijn> Well, what I would suggest is that when you wrap several
> Martijn> statements into a single transaction with begin/commit,
> Martijn> the whole lot could be considered a single statement
> Martijn> (since they form an atomic transaction so in a sense they
> Martijn> are all executed simultaneously). And hence Postgresql is
> Martijn> perfectly compliant.
>
> FWIW, and not that I am an Oracle fan :-), Oracle seems to interpret
> this the same way when using a "select sysdate from dual" inside a
> transaction.
Oh, interesting datapoint. Let me get this clear - on oracle, the
equivalent of:
BEGIN;
SELECT current_timestamp;
<go off to lunch, come back>
SELECT current_timestamp;
END;
will give two identical timestamps?
Ross
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 15:19 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 61+ messages in thread
From: Ross J. Reedstrom @ 2002-09-24 15:19 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Manfred Koizar <mkoi-pg@aon.at>; Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
On Tue, Sep 24, 2002 at 08:05:59AM -0700, Josh Berkus wrote:
>
> This looks fine to me, as a search-and-replace on current_timestamp is
> easy. However, we need to do a better job of warning people about the
> change than we did with interval() to "interval"().
>
> Actually, can I make the proposal that *any* change that breaks
> backward compatibility be mentioned in both the new version
> announcement and on the download page? This would prevent a lot of
> grief. If I'm kept informed of these changes, I'll be happy to write
> up a user-friendly announcement/instructions on how to cope with the
> change.
I'd suggest we (for values of we that probably resolve to Bruce
or a Bruce triggered Josh ;-) start a new doc, right now, for
7.4_USER_VISIBLE_CHANGES, or some other, catchy title. In it, document,
with example SQL snippets, if need be, the change from previous behavior,
_when the patch is committed_. In fact, y'all could be hardnosed about
not accepting a user visible syntax changing patch without it touching
this file. Such a document would be invaluable for database migration.
On another note, this discussion is happening on GENERAL and SQL, but
is getting pretty technical - should someone more it to HACKERS to get
input from developers who don't hang out here?
Ross
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 15:27 John Hasler <john@dhh.gt.org>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 61+ messages in thread
From: John Hasler @ 2002-09-24 15:27 UTC (permalink / raw)
To: pgsql-sql
Josh Berkus writes:
> now() or now('transaction') returns the transaction timestamp.
> now('statement') returns the statement timestamp now('immediate') returns
> the timestamp at the exact time the function is called.
I like that.
IMHO "the exact time the function is called" is what most people would
expect to get from now(), but it's too late for that.
--
John Hasler
john@dhh.gt.org
Dancing Horse Hill
Elmwood, Wisconsin
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [GENERAL] CURRENT_TIMESTAMP
@ 2002-09-24 16:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 61+ messages in thread
From: Tom Lane @ 2002-09-24 16:00 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Bruce Momjian <pgman@candle.pha.pa.us>; Aaron Held <aaron@MetroNY.com>; Roberto Mello <rmello@cc.usu.edu>; Neil Conway <neilc@samurai.com>; pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> So, if I've got this straight:
> [ snip ]
> ... thus changing only current_timestamp.
Yeah, that's more or less what I was thinking. The argument for
changing current_timestamp seems to be really just spec compliance;
that doesn't apply to now() or timeofday().
> Better yet, how about we introduce a parameter to now()? Example:
> now() or now('transaction') returns the transaction timestamp.
> now('statement') returns the statement timestamp
> now('immediate') returns the timestamp at the exact time the function
> is called.
I like this.
> We could introduce the new version of now() in 7.4, encourage everyone
> to use it instead of other timestamp calls, and then in 7.5 change the
> behavior of current_timestamp for SQL92 compliance.
I'd be inclined to just do it; we have not been very good about
following through on multi-version sequences of changes. And the
folks who want a standard-compliant current_timestamp aren't going
to want to migrate to now('statement') instead ...
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-24 21:48 Roland Roberts <roland@astrofoto.org>
parent: Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 61+ messages in thread
From: Roland Roberts @ 2002-09-24 21:48 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
>>>>> "Ross" == Ross J Reedstrom <reedstrm@rice.edu> writes:
Ross> Oh, interesting datapoint. Let me get this clear - on
Ross> oracle, the equivalent of:
Well, I've never gone off to lunch in the middle, but in Oracle 7, I
had transactions which definitely took as much as a few minutes to
complete where the timestamp on every row committed was the same.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-24 21:56 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Roland Roberts <roland@astrofoto.org>
0 siblings, 2 replies; 61+ messages in thread
From: Bruce Momjian @ 2002-09-24 21:56 UTC (permalink / raw)
To: Roland Roberts <roland@astrofoto.org>; +Cc: pgsql-sql; pgsql-general@postgresql.org
Roland Roberts wrote:
> >>>>> "Ross" == Ross J Reedstrom <reedstrm@rice.edu> writes:
>
> Ross> Oh, interesting datapoint. Let me get this clear - on
> Ross> oracle, the equivalent of:
>
> Well, I've never gone off to lunch in the middle, but in Oracle 7, I
> had transactions which definitely took as much as a few minutes to
> complete where the timestamp on every row committed was the same.
Can you run a test:
BEGIN;
SELECT CURRENT_TIMESTAMP;
wait 5 seconds
SELECT CURRENT_TIMESTAMP;
Are the two times the same?
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-25 06:54 Manfred Koizar <mkoi-pg@aon.at>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 61+ messages in thread
From: Manfred Koizar @ 2002-09-25 06:54 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
>Can you run a test:
>
> BEGIN;
> SELECT CURRENT_TIMESTAMP;
> wait 5 seconds
> SELECT CURRENT_TIMESTAMP;
>
>Are the two times the same?
MS SQL 7:
begin transaction
insert into tst values (CURRENT_TIMESTAMP)
-- wait
insert into tst values (CURRENT_TIMESTAMP)
commit
select * from tst
t
---------------------------
2002-09-24 09:49:58.777
2002-09-24 09:50:14.100
Interbase 6:
SQL> select current_timestamp from rdb$database;
=========================
2002-09-24 22:30:13.0000
SQL> select current_timestamp from rdb$database;
=========================
2002-09-24 22:30:18.0000
SQL> commit;
Servus
Manfred
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-27 17:29 Roland Roberts <roland@astrofoto.org>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 61+ messages in thread
From: Roland Roberts @ 2002-09-27 17:29 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
SQL> create table rbr_foo (a date);
Table created.
SQL> begin
2 insert into rbr_foo select sysdate from dual;
[...wait about 10 seconds...]
3 insert into rbr_foo select sysdate from dual;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select * from rbr_foo;
A
---------------------
SEP 27, 2002 12:57:27
SEP 27, 2002 12:57:27
Note that, as near as I can tell, Oracle 8 does NOT have timestamp or
current_timestamp. Online docs say both are present in Oracle 9i.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 03:28 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-09-29 03:28 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
OK, we have two db's returning statement start time, and Oracle 8 not
having CURRENT_TIMESTAMP.
Have we agreed to make CURRENT_TIMESTAMP statement start, and now()
transaction start? Is this an open item or TODO item?
---------------------------------------------------------------------------
Manfred Koizar wrote:
> On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian
> <pgman@candle.pha.pa.us> wrote:
> >Can you run a test:
> >
> > BEGIN;
> > SELECT CURRENT_TIMESTAMP;
> > wait 5 seconds
> > SELECT CURRENT_TIMESTAMP;
> >
> >Are the two times the same?
>
> MS SQL 7:
> begin transaction
> insert into tst values (CURRENT_TIMESTAMP)
> -- wait
> insert into tst values (CURRENT_TIMESTAMP)
> commit
> select * from tst
>
> t
> ---------------------------
> 2002-09-24 09:49:58.777
> 2002-09-24 09:50:14.100
>
> Interbase 6:
> SQL> select current_timestamp from rdb$database;
>
> =========================
> 2002-09-24 22:30:13.0000
>
> SQL> select current_timestamp from rdb$database;
>
> =========================
> 2002-09-24 22:30:18.0000
>
> SQL> commit;
>
> Servus
> Manfred
>
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 03:47 Martijn van Oosterhout <kleptog@svana.org>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Martijn van Oosterhout @ 2002-09-29 03:47 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
On Sat, Sep 28, 2002 at 11:28:03PM -0400, Bruce Momjian wrote:
>
> OK, we have two db's returning statement start time, and Oracle 8 not
> having CURRENT_TIMESTAMP.
>
> Have we agreed to make CURRENT_TIMESTAMP statement start, and now()
> transaction start? Is this an open item or TODO item?
Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In
any case, if it does get changed we'll have to go through the documentation
and work out whether we mean current_timestamp or now(). I think most people
actually want now().
Fortunatly where I work we only use now() so it won't really matter too
much. Is there a compelling reason to change?
> ---------------------------------------------------------------------------
>
> Manfred Koizar wrote:
> > On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian
> > <pgman@candle.pha.pa.us> wrote:
> > >Can you run a test:
> > >
> > > BEGIN;
> > > SELECT CURRENT_TIMESTAMP;
> > > wait 5 seconds
> > > SELECT CURRENT_TIMESTAMP;
> > >
> > >Are the two times the same?
> >
> > MS SQL 7:
> > begin transaction
> > insert into tst values (CURRENT_TIMESTAMP)
> > -- wait
> > insert into tst values (CURRENT_TIMESTAMP)
> > commit
> > select * from tst
> >
> > t
> > ---------------------------
> > 2002-09-24 09:49:58.777
> > 2002-09-24 09:50:14.100
> >
> > Interbase 6:
> > SQL> select current_timestamp from rdb$database;
> >
> > =========================
> > 2002-09-24 22:30:13.0000
> >
> > SQL> select current_timestamp from rdb$database;
> >
> > =========================
> > 2002-09-24 22:30:18.0000
> >
> > SQL> commit;
> >
> > Servus
> > Manfred
> >
>
> --
> 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, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 03:51 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-09-29 03:51 UTC (permalink / raw)
To: Martijn van Oosterhout <kleptog@svana.org>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Martijn van Oosterhout wrote:
> On Sat, Sep 28, 2002 at 11:28:03PM -0400, Bruce Momjian wrote:
> >
> > OK, we have two db's returning statement start time, and Oracle 8 not
> > having CURRENT_TIMESTAMP.
> >
> > Have we agreed to make CURRENT_TIMESTAMP statement start, and now()
> > transaction start? Is this an open item or TODO item?
>
> Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In
> any case, if it does get changed we'll have to go through the documentation
> and work out whether we mean current_timestamp or now(). I think most people
> actually want now().
Well, I think we have to offer statement start time somewhere, and it
seems the standard probably requires that. Two other databases do it
that way. Oracle doesn't have CURRENT_TIMESTAMP in 8.X. Can anyone
test on 9.X?
> Fortunatly where I work we only use now() so it won't really matter too
> much. Is there a compelling reason to change?
Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be
happy with STATEMENT_TIMESTAMP, but because the standard requires it we
may just have to fix CURRENT_TIMESTAMP.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 04:10 Martijn van Oosterhout <kleptog@svana.org>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Martijn van Oosterhout @ 2002-09-29 04:10 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
On Sat, Sep 28, 2002 at 11:51:32PM -0400, Bruce Momjian wrote:
> Martijn van Oosterhout wrote:
> > Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In
> > any case, if it does get changed we'll have to go through the documentation
> > and work out whether we mean current_timestamp or now(). I think most people
> > actually want now().
>
> Well, I think we have to offer statement start time somewhere, and it
> seems the standard probably requires that. Two other databases do it
> that way. Oracle doesn't have CURRENT_TIMESTAMP in 8.X. Can anyone
> test on 9.X?
Hmm, well having a statement start time could be conceivably useful.
> > Fortunatly where I work we only use now() so it won't really matter too
> > much. Is there a compelling reason to change?
>
> Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be
> happy with STATEMENT_TIMESTAMP, but because the standard requires it we
> may just have to fix CURRENT_TIMESTAMP.
Well, my vote would be for STATEMENT_TIMESTAMP. Is there really no other
database that does it the way we do? Perhaps it could be matched with a
TRANSACTION_TIMESTAMP and we can sort out CURRENT_TIMESTAMP some other way.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 04:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 1 reply; 61+ messages in thread
From: Tom Lane @ 2002-09-29 04:35 UTC (permalink / raw)
To: Martijn van Oosterhout <kleptog@svana.org>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sat, Sep 28, 2002 at 11:51:32PM -0400, Bruce Momjian wrote:
>> Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be
>> happy with STATEMENT_TIMESTAMP, but because the standard requires it we
>> may just have to fix CURRENT_TIMESTAMP.
> Well, my vote would be for STATEMENT_TIMESTAMP.
One problem with inventing STATEMENT_TIMESTAMP is that (if spelled that
way, without parens) it would have to become a fully-reserved keyword,
thus possibly breaking some applications that use that name now.
But the real point, I think, is that the folks pushing for this think
that the standard requires CURRENT_TIMESTAMP to be statement timestamp.
Inventing some other keyword isn't going to satisfy them.
I don't personally find the "it's required by the spec" argument
compelling, because the spec specifically says that the exact behavior
is implementation-dependent --- so anyone who assumes CURRENT_TIMESTAMP
will behave as start-of-statement timestamp is going to have portability
problems anyway. Oracle didn't seem to find the argument compelling
either; at last report they have no statement-timestamp function.
I'd be happier with the whole thing if anyone had exhibited a convincing
use-case for statement timestamp. So far I've not seen any actual
examples of situations that are not better served by either transaction
timestamp or true current time. And the spec is perfectly clear that
CURRENT_TIMESTAMP does not mean true current time...
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 19:43 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 61+ messages in thread
From: Josh Berkus @ 2002-09-29 19:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Martijn van Oosterhout <kleptog@svana.org>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Tom,
> I'd be happier with the whole thing if anyone had exhibited a convincing
> use-case for statement timestamp. So far I've not seen any actual
> examples of situations that are not better served by either transaction
> timestamp or true current time. And the spec is perfectly clear that
> CURRENT_TIMESTAMP does not mean true current time...
Are we still planning on putting the three different versions of now() on the
TODO? I.e.,
now('transaction'),
now('statement'), and
now('immediate')
With now() = now('transaction')?
I still think it's a good idea, provided that we have some easy means to
determine now('statement').
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 20:38 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 2 replies; 61+ messages in thread
From: Bruce Momjian @ 2002-09-29 20:38 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Martijn van Oosterhout <kleptog@svana.org>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Josh Berkus wrote:
>
> Tom,
>
> > I'd be happier with the whole thing if anyone had exhibited a convincing
> > use-case for statement timestamp. So far I've not seen any actual
> > examples of situations that are not better served by either transaction
> > timestamp or true current time. And the spec is perfectly clear that
> > CURRENT_TIMESTAMP does not mean true current time...
>
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
>
> I still think it's a good idea, provided that we have some easy means to
> determine now('statement').
I did a little more research on CURRENT_TIMESTAMP. I read the Oracle
docs, and while they mention it, they don't say if the date is xact,
statement, or timeofday. They do mention it was only added in their
newest product, 9.X, so it isn't surpising no one is using it.
I also researched the SQL99 standards and found a much more specific
definition:
3) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value
function>s that are generally contained, without an intervening
<routine invocation> whose subject routines do not include an
SQL function, in <value expression>s that are contained either
in S without an intervening <SQL procedure statement> or in an
<SQL procedure statement> contained in the <triggered action>
of a trigger activated as a consequence of executing S, are
effectively evaluated simultaneously. The time of evaluation of
a <datetime value function> during the execution of S and its
activated triggers is implementation-dependent.
They basically seem to be saying that CURRENT_TIMESTAMP has to be the
same for all triggers as it is for the submitted SQL statement. When
they say "the time of evaluation ... is implementation-dependent" they
mean that is can be the beginning of the statement, or the end of the
statement. In fact, you can make a strong argument that it should be
the statement end time that is the proper time, but for implementation
reasons, it is certainly easier to make it start.
Now, they are _not_ saying the statement can't have the same time as
other statements in the transaction, but I don't see why they would
explicitly have to state that. They say statement, so I think we need
to follow that if we want to be standard-compliant. We already have two
other databases who are doing this timing at statement level.
If we change CURRENT_TIMESTAMP to statement time, I don't think we need
now(""), but if we don't change it, I think we do --- somehow we should
allow users to access statement time.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 20:47 Josh Berkus <josh@agliodbs.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 61+ messages in thread
From: Josh Berkus @ 2002-09-29 20:47 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Martijn van Oosterhout <kleptog@svana.org>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Bruce,
> If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> now(""), but if we don't change it, I think we do --- somehow we should
> allow users to access statement time.
I'd argue that we need the 3 kinds of now() regardless, just to limit user
confusion. If we set things up as:
now() = transaction time
current_timestamp = statement time
timeofday() = exact time
That does give users access to all 3 timestamps, but using a competely
non-intuitive nomenclature. It's likely that the three types of now() would
just be pointers to other time functions, but would provide nomenative
clarity.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-29 21:27 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 61+ messages in thread
From: Bruce Momjian @ 2002-09-29 21:27 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Martijn van Oosterhout <kleptog@svana.org>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Josh Berkus wrote:
> Bruce,
>
> > If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> > now(""), but if we don't change it, I think we do --- somehow we should
> > allow users to access statement time.
>
> I'd argue that we need the 3 kinds of now() regardless, just to limit user
> confusion. If we set things up as:
>
> now() = transaction time
> current_timestamp = statement time
> timeofday() = exact time
>
> That does give users access to all 3 timestamps, but using a competely
> non-intuitive nomenclature. It's likely that the three types of now() would
> just be pointers to other time functions, but would provide nomenative
> clarity.
I agree, having now() as a central place for time information is a good
idea. Maybe we need to vote on these issues.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-30 03:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 61+ messages in thread
From: Tom Lane @ 2002-09-30 03:53 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Martijn van Oosterhout <kleptog@svana.org>; Bruce Momjian <pgman@candle.pha.pa.us>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Josh Berkus <josh@agliodbs.com> writes:
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
I have no objection to doing that. What seems to be contentious is
whether we should change the current behavior of CURRENT_TIMESTAMP.
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-30 04:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 61+ messages in thread
From: Tom Lane @ 2002-09-30 04:36 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: josh@agliodbs.com, Martijn van Oosterhout <kleptog@svana.org>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Now, they are _not_ saying the statement can't have the same time as
> other statements in the transaction, but I don't see why they would
> explicitly have to state that.
Allow me to turn that around: given that they clearly do NOT state that,
how can you argue that "the spec requires it"? AFAICS the spec does not
require it. In most places they are considerably more explicit than
this about stating what is required.
> We already have two other databases who are doing this timing at
> statement level.
The behavior of CURRENT_TIMESTAMP is clearly stated by the spec to be
implementation-dependent. We are under no compulsion to follow any
specific other implementation. If we were going to follow some other
lead, I'd look to Oracle first...
> If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> now(""), but if we don't change it, I think we do --- somehow we should
> allow users to access statement time.
I have no problem with providing a function to access statement time,
and now('something') seems a reasonable spelling of that function.
But I think the argument that we should change our historical behavior
of CURRENT_TIMESTAMP is very weak.
One reason why I have a problem with the notion that the spec requires
CURRENT_TIMESTAMP to mean "time of arrival of the current interactive
command" (which is the only specific definition I've seen mentioned
here) is that the spec does not truly have a notion of interactive
command to begin with. AFAICT the spec's model of command execution
is ecpg-like: you have commands embedded in a calling language with
all sorts of opportunities for pre-planning, pre-execution, etc.
The notion of command arrival time is extremely fuzzy in this model.
It could very well be the time you compiled the ecpg application, or
the time you started the application running.
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-30 16:20 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-09-30 16:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, Martijn van Oosterhout <kleptog@svana.org>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Now, they are _not_ saying the statement can't have the same time as
> > other statements in the transaction, but I don't see why they would
> > explicitly have to state that.
>
> Allow me to turn that around: given that they clearly do NOT state that,
> how can you argue that "the spec requires it"? AFAICS the spec does not
> require it. In most places they are considerably more explicit than
> this about stating what is required.
I just looked at the SQL99 spec again:
3) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value
function>s that are generally contained, without an intervening
<routine invocation> whose subject routines do not include an
SQL function, in <value expression>s that are contained either
in S without an intervening <SQL procedure statement> or in an
<SQL procedure statement> contained in the <triggered action>
of a trigger activated as a consequence of executing S, are
effectively evaluated simultaneously. The time of evaluation of
a <datetime value function> during the execution of S and its
^^^^^^^^^^^^^^^^^^^^^^^^^
activated triggers is implementation-dependent.
Notice the part I highlighted. The time returned is
implementation-dependent "during the execution of S". Now, if we do:
BEGIN;
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP;
the time currently returned for the second query is _not_ during the
duration of S (S being an SQL procedure statement) so I don't see how we
can be viewed as spec-compliant.
> > We already have two other databases who are doing this timing at
> > statement level.
>
> The behavior of CURRENT_TIMESTAMP is clearly stated by the spec to be
> implementation-dependent. We are under no compulsion to follow any
> specific other implementation. If we were going to follow some other
> lead, I'd look to Oracle first...
Only "implementation-dependent" during the execution of the statement.
We can't just return the session start time or 1970-01-01 for every
invocation of CURRENT_TIMESTAMP.
> > If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> > now(""), but if we don't change it, I think we do --- somehow we should
> > allow users to access statement time.
>
> I have no problem with providing a function to access statement time,
> and now('something') seems a reasonable spelling of that function.
> But I think the argument that we should change our historical behavior
> of CURRENT_TIMESTAMP is very weak.
Hard to see how it is "very weak". What do you base that on?
Everything I have seen looks pretty strong that we are wrong in our
current implementation.
> One reason why I have a problem with the notion that the spec requires
> CURRENT_TIMESTAMP to mean "time of arrival of the current interactive
> command" (which is the only specific definition I've seen mentioned
> here) is that the spec does not truly have a notion of interactive
> command to begin with. AFAICT the spec's model of command execution
> is ecpg-like: you have commands embedded in a calling language with
> all sorts of opportunities for pre-planning, pre-execution, etc.
> The notion of command arrival time is extremely fuzzy in this model.
> It could very well be the time you compiled the ecpg application, or
> the time you started the application running.
The spec says "during the execution of S" so that is what I think we
have to follow.
Hopefully we will get an Oracle 9 tester soon.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-30 17:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Tom Lane @ 2002-09-30 17:59 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: josh@agliodbs.com, Martijn van Oosterhout <kleptog@svana.org>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Notice the part I highlighted. The time returned is
> implementation-dependent "during the execution of S". Now, if we do:
> BEGIN;
> SELECT CURRENT_TIMESTAMP;
> SELECT CURRENT_TIMESTAMP;
> the time currently returned for the second query is _not_ during the
> duration of S (S being an SQL procedure statement)
Not so fast. What is an "SQL procedure statement"?
Our interactive commands do not map real well to the spec's definitions.
Consider for example SQL92 section 4.17:
4.17 Procedures
A <procedure> consists of a <procedure name>, a sequence of <pa-
rameter declaration>s, and a single <SQL procedure statement>.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
A <procedure> in a <module> is invoked by a compilation unit as-
sociated with the <module> by means of a host language "call"
statement that specifies the <procedure name> of the <procedure>
and supplies a sequence of parameter values corresponding in number
and in <data type> to the <parameter declaration>s of the <proce-
dure>. A call of a <procedure> causes the <SQL procedure statement>
that it contains to be executed.
The only thing you can easily map this onto in Postgres is stored
functions; your reading would then say that each Postgres function call
requires its own evaluation of current_timestamp, which I think we are
all agreed would be a disastrous interpretation.
It would be pretty easy to make the case that an ECPG module represents
a "procedure" in the spec's meaning, in which case it is *necessary* for
spec compliance that the ECPG module be able to execute all its commands
with the same value of current_timestamp. This would look like a series
of interactive commands to the backend.
So I do not think that the spec provides clear support for your position.
The only thing that is really clear is that there is a minimum unit
of execution in which current_timestamp is not supposed to change.
It does not clearly define any maximum unit; and it is even less clear
that our interactive commands should be equated to "SQL procedure
statement".
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-30 18:37 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 61+ messages in thread
From: Jean-Luc Lachance @ 2002-09-30 18:37 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Martijn van Oosterhout <kleptog@svana.org>; Bruce Momjian <pgman@candle.pha.pa.us>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
How can you make a difference between now('statement'), and
now('immediate').
To me they are the same thing. Why not simply now() for transaction, and
now('CLOCK') or better yet system_clock() or clock() for curent time.
JLL
Josh Berkus wrote:
>
> Tom,
>
> > I'd be happier with the whole thing if anyone had exhibited a convincing
> > use-case for statement timestamp. So far I've not seen any actual
> > examples of situations that are not better served by either transaction
> > timestamp or true current time. And the spec is perfectly clear that
> > CURRENT_TIMESTAMP does not mean true current time...
>
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
>
> I still think it's a good idea, provided that we have some easy means to
> determine now('statement').
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-30 18:47 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 61+ messages in thread
From: Jean-Luc Lachance @ 2002-09-30 18:47 UTC (permalink / raw)
To: josh@agliodbs.com, Tom Lane <tgl@sss.pgh.pa.us>; Martijn van Oosterhout <kleptog@svana.org>; Bruce Momjian <pgman@candle.pha.pa.us>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
OK, forget system_clock() or clock() timeofday() will do.
Jean-Luc Lachance wrote:
>
> How can you make a difference between now('statement'), and
> now('immediate').
> To me they are the same thing. Why not simply now() for transaction, and
> now('CLOCK') or better yet system_clock() or clock() for curent time.
>
> JLL
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-30 18:49 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-09-30 18:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, Martijn van Oosterhout <kleptog@svana.org>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Notice the part I highlighted. The time returned is
> > implementation-dependent "during the execution of S". Now, if we do:
>
> > BEGIN;
> > SELECT CURRENT_TIMESTAMP;
> > SELECT CURRENT_TIMESTAMP;
>
> > the time currently returned for the second query is _not_ during the
> > duration of S (S being an SQL procedure statement)
>
> Not so fast. What is an "SQL procedure statement"?
>
> Our interactive commands do not map real well to the spec's definitions.
> Consider for example SQL92 section 4.17:
>
> 4.17 Procedures
>
> A <procedure> consists of a <procedure name>, a sequence of <pa-
> rameter declaration>s, and a single <SQL procedure statement>.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> A <procedure> in a <module> is invoked by a compilation unit as-
> sociated with the <module> by means of a host language "call"
> statement that specifies the <procedure name> of the <procedure>
> and supplies a sequence of parameter values corresponding in number
> and in <data type> to the <parameter declaration>s of the <proce-
> dure>. A call of a <procedure> causes the <SQL procedure statement>
> that it contains to be executed.
>
> The only thing you can easily map this onto in Postgres is stored
> functions; your reading would then say that each Postgres function call
> requires its own evaluation of current_timestamp, which I think we are
> all agreed would be a disastrous interpretation.
>
> It would be pretty easy to make the case that an ECPG module represents
> a "procedure" in the spec's meaning, in which case it is *necessary* for
> spec compliance that the ECPG module be able to execute all its commands
> with the same value of current_timestamp. This would look like a series
> of interactive commands to the backend.
>
> So I do not think that the spec provides clear support for your position.
> The only thing that is really clear is that there is a minimum unit
> of execution in which current_timestamp is not supposed to change.
> It does not clearly define any maximum unit; and it is even less clear
> that our interactive commands should be equated to "SQL procedure
> statement".
OK, you don't like "SQL procedure statement". Let's look at SQL92:
3) If an SQL-statement generally contains more than one reference
to one or more <datetime value function>s, then all such ref-
erences are effectively evaluated simultaneously. The time of
evaluation of the <datetime value function> during the execution
^^^^^^^^^^^^^^^^^^^^
of the SQL-statement is implementation-dependent.
^^^^^^^^^^^^^^^^^^^^
so, again, we have wording that is has to be "during" the SQL statement.
Also, we have MSSQL, Interbase, and now Oracle modifying
CURRENT_TIMESTAMP during the transaction. (The Oracle report just came
in a few hours ago.)
Perhaps we need a vote on this.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] CURRENT_TIMESTAMP
@ 2002-09-30 21:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Tom Lane @ 2002-09-30 21:26 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: josh@agliodbs.com, Martijn van Oosterhout <kleptog@svana.org>; Manfred Koizar <mkoi-pg@aon.at>; Roland Roberts <roland@astrofoto.org>; pgsql-sql; pgsql-general@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Also, we have MSSQL, Interbase, and now Oracle modifying
> CURRENT_TIMESTAMP during the transaction. (The Oracle report just came
> in a few hours ago.)
Weren't you dissatisfied with the specificity of that Oracle report?
> Perhaps we need a vote on this.
Perhaps, but let's wait till the facts are in.
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
@ 2002-10-03 20:18 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-10-03 20:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
[ 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 of
value?
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
@ 2002-10-03 22:03 Andrew Sullivan <andrew@libertyrms.info>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Andrew Sullivan @ 2002-10-03 22:03 UTC (permalink / raw)
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
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
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
@ 2002-10-03 22:15 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-10-03 22:15 UTC (permalink / raw)
To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
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 intuitive
more standard-compliant
more 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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
@ 2002-10-03 23:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 61+ messages in thread
From: Tom Lane @ 2002-10-03 23:09 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; PostgreSQL-development <pgsql-hackers@postgresql.org>
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-compliant
more closely matches other db's (but still not very closely)
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.
regards, tom lane
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
@ 2002-10-03 23:58 Andrew Sullivan <andrew@libertyrms.info>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 61+ messages in thread
From: Andrew Sullivan @ 2002-10-03 23:58 UTC (permalink / raw)
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
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
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
@ 2002-10-04 00:41 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 61+ messages in thread
From: Bruce Momjian @ 2002-10-04 00:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; PostgreSQL-development <pgsql-hackers@postgresql.org>
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
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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
@ 2002-10-04 05:03 Oliver Elphick <olly@lfix.co.uk>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 61+ messages in thread
From: Oliver Elphick @ 2002-10-04 05:03 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Andrew Sullivan <andrew@libertyrms.info>; PostgreSQL-development <pgsql-hackers@postgresql.org>
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 twoedged sword, piercing even to the
dividing asunder of soul and spirit, and of the joints
and marrow, and is a discerner of the thoughts and
intents of the heart." Hebrews 4:12
^ permalink raw reply [nested|flat] 61+ messages in thread
* Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
@ 2002-10-04 05:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 61+ messages in thread
From: Tom Lane @ 2002-10-04 05:20 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Andrew Sullivan <andrew@libertyrms.info>; PostgreSQL-development <pgsql-hackers@postgresql.org>
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
^ permalink raw reply [nested|flat] 61+ messages in thread
end of thread, other threads:[~2002-10-04 05:20 UTC | newest]
Thread overview: 61+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-23 19:02 Re: [SQL] CURRENT_TIMESTAMP Manfred Koizar <mkoi-pg@aon.at>
2002-09-23 20:36 ` Josh Berkus <josh@agliodbs.com>
2002-09-23 20:41 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-23 20:49 ` Josh Berkus <josh@agliodbs.com>
2002-09-23 20:53 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-23 20:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-24 00:11 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-24 00:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-24 00:37 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-24 01:53 ` Josh Berkus <josh@agliodbs.com>
2002-09-24 02:01 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-24 02:20 ` John Hasler <john@dhh.gt.org>
2002-09-24 03:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-24 03:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-24 03:52 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-24 03:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-24 03:37 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-24 09:44 ` Manfred Koizar <mkoi-pg@aon.at>
2002-09-24 09:37 ` Manfred Koizar <mkoi-pg@aon.at>
2002-09-24 09:16 ` Manfred Koizar <mkoi-pg@aon.at>
2002-09-24 13:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-24 15:05 ` Josh Berkus <josh@agliodbs.com>
2002-09-24 15:19 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-24 15:27 ` John Hasler <john@dhh.gt.org>
2002-09-24 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-24 01:19 ` Martijn van Oosterhout <kleptog@svana.org>
2002-09-24 08:33 ` Manfred Koizar <mkoi-pg@aon.at>
2002-09-24 15:07 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-24 14:55 ` Roland Roberts <roland@astrofoto.org>
2002-09-24 15:10 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-24 21:48 ` Roland Roberts <roland@astrofoto.org>
2002-09-24 21:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-25 06:54 ` Manfred Koizar <mkoi-pg@aon.at>
2002-09-29 03:28 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-29 03:47 ` Martijn van Oosterhout <kleptog@svana.org>
2002-09-29 03:51 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-29 04:10 ` Martijn van Oosterhout <kleptog@svana.org>
2002-09-29 04:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-29 19:43 ` Josh Berkus <josh@agliodbs.com>
2002-09-29 20:38 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-29 20:47 ` Josh Berkus <josh@agliodbs.com>
2002-09-29 21:27 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-30 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-30 16:20 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-30 17:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-30 18:49 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-09-30 21:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-03 20:18 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-03 22:03 ` Andrew Sullivan <andrew@libertyrms.info>
2002-10-03 22:15 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-03 23:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-03 23:58 ` Andrew Sullivan <andrew@libertyrms.info>
2002-10-04 00:41 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-04 05:03 ` Oliver Elphick <olly@lfix.co.uk>
2002-10-04 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-30 03:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-30 18:37 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-09-30 18:47 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-09-27 17:29 ` Roland Roberts <roland@astrofoto.org>
2002-09-24 11:44 Re: [GENERAL] CURRENT_TIMESTAMP Haller Christoph <Christoph.Haller@rodos.fzk.de>
2002-09-24 12:08 Re: [GENERAL] CURRENT_TIMESTAMP Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox