Thread: Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
> > 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. Yes, it sais statement. Note also, that a typical SELECT only session would not advance CURRENT_TIMESTAMP at all in the typical "autocommit off" mode that the Spec is all about. > What do others think? I liked your proposal to advance CURRENT_TIMESTAMP at each statement start. (It would not advance inside a stored procedure). Andreas
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> Note also, that a typical SELECT only session would not advance
> CURRENT_TIMESTAMP at all in the typical "autocommit off" mode that
> the Spec is all about.
True, but the spec also says to default to serializable transaction
mode. So in a single-transaction session like you are picturing,
the successive SELECTs would all see a frozen snapshot of the database.
Freezing CURRENT_TIMESTAMP goes right along with that, and in fact makes
a lot of sense, because it tells you exactly what time your snapshot
of the database state was taken.
This line of thought opens another can of worms: should the behavior
of CURRENT_TIMESTAMP depend on serializable vs. read-committed mode?
Maybe SetQuerySnapshot is the routine that ought to capture the
"statement-start-time" timestamp value. We could define
CURRENT_TIMESTAMP as the time of the active database snapshot.
Or at least offer a fourth parameter to that parameterized now() to
return this time.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes: > > Note also, that a typical SELECT only session would not advance > > CURRENT_TIMESTAMP at all in the typical "autocommit off" mode that > > the Spec is all about. > > True, but the spec also says to default to serializable transaction > mode. So in a single-transaction session like you are picturing, > the successive SELECTs would all see a frozen snapshot of the database. > Freezing CURRENT_TIMESTAMP goes right along with that, and in fact makes > a lot of sense, because it tells you exactly what time your snapshot > of the database state was taken. > > This line of thought opens another can of worms: should the behavior > of CURRENT_TIMESTAMP depend on serializable vs. read-committed mode? > Maybe SetQuerySnapshot is the routine that ought to capture the > "statement-start-time" timestamp value. We could define > CURRENT_TIMESTAMP as the time of the active database snapshot. > Or at least offer a fourth parameter to that parameterized now() to > return this time. > > regards, tom lane That is a very good point. At least with serializable transactions it seems perfectly reasonable to return a frozen CURRENT_TIMESTAMP. What do you think about read-commited level? Can time be commited? ;-) It would be even more surprising to new users if the implementation of CURRENT_TIMESTAMP would depend on trx serialization level. Regards, Michael Paesold
Michael Paesold wrote: > Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes: > > > Note also, that a typical SELECT only session would not advance > > > CURRENT_TIMESTAMP at all in the typical "autocommit off" mode that > > > the Spec is all about. > > > > True, but the spec also says to default to serializable transaction > > mode. So in a single-transaction session like you are picturing, > > the successive SELECTs would all see a frozen snapshot of the database. > > Freezing CURRENT_TIMESTAMP goes right along with that, and in fact makes > > a lot of sense, because it tells you exactly what time your snapshot > > of the database state was taken. > > > > This line of thought opens another can of worms: should the behavior > > of CURRENT_TIMESTAMP depend on serializable vs. read-committed mode? > > Maybe SetQuerySnapshot is the routine that ought to capture the > > "statement-start-time" timestamp value. We could define > > CURRENT_TIMESTAMP as the time of the active database snapshot. > > Or at least offer a fourth parameter to that parameterized now() to > > return this time. > > > > regards, tom lane > > That is a very good point. At least with serializable transactions it seems > perfectly reasonable to return a frozen CURRENT_TIMESTAMP. What do you think > about read-commited level? Can time be commited? ;-) > It would be even more surprising to new users if the implementation of > CURRENT_TIMESTAMP would depend on trx serialization level. Yes, CURRENT_TIMESTAMP changing based on transaction serializable/read commited would be quite confusing. Also, because our default is read committed, we would end up with CURRENT_TIMESTAMP being statement level, which actually does give us a logical place to allow CURRENT_TIMESTAMP to change, but I thought people voted against that. However, imagine a query that used CURRENT_TIMESTAMP in the WHERE clause to find items that were not in the future. Would a CURRENT_TIMESTAMP test in a multi-statement transaction want to check based on transaction start, or on the tuples visible at the time the statement started? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > That is a very good point. At least with serializable transactions it seems > > perfectly reasonable to return a frozen CURRENT_TIMESTAMP. What do you think > > about read-commited level? Can time be commited? ;-) > > It would be even more surprising to new users if the implementation of > > CURRENT_TIMESTAMP would depend on trx serialization level. > > Yes, CURRENT_TIMESTAMP changing based on transaction serializable/read > commited would be quite confusing. Also, because our default is read > committed, we would end up with CURRENT_TIMESTAMP being statement level, > which actually does give us a logical place to allow CURRENT_TIMESTAMP > to change, but I thought people voted against that. > > However, imagine a query that used CURRENT_TIMESTAMP in the WHERE clause > to find items that were not in the future. Would a CURRENT_TIMESTAMP > test in a multi-statement transaction want to check based on transaction > start, or on the tuples visible at the time the statement started? Well, in a serializable transaction there would be no difference at all, at least if CURRENT_TIMESTAMP is consistent within the transaction. Any changes outside the transaction after SetQuerySnapshot would not be seen by the transaction anyway. In read-commited, I think it's different. If CURRENT_TIMESTAMP is frozen, than the behavior would be the same as in serializable level, if CURRENT_TIMESTAMP advances with each statement, the result would also change. That is an inherent problem with read-commited though and has not so much to do with the timestamp behavior. Regards, Michael Paesold
OK, are we agreed to leave CURRENT_TIMESTAMP/now() alone and just add
now("string")? If no one replies, I will assume that is a yes and I
will add it to TODO.
---------------------------------------------------------------------------
Michael Paesold wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> > > That is a very good point. At least with serializable transactions it
> seems
> > > perfectly reasonable to return a frozen CURRENT_TIMESTAMP. What do you
> think
> > > about read-commited level? Can time be commited? ;-)
> > > It would be even more surprising to new users if the implementation of
> > > CURRENT_TIMESTAMP would depend on trx serialization level.
> >
> > Yes, CURRENT_TIMESTAMP changing based on transaction serializable/read
> > commited would be quite confusing. Also, because our default is read
> > committed, we would end up with CURRENT_TIMESTAMP being statement level,
> > which actually does give us a logical place to allow CURRENT_TIMESTAMP
> > to change, but I thought people voted against that.
> >
> > However, imagine a query that used CURRENT_TIMESTAMP in the WHERE clause
> > to find items that were not in the future. Would a CURRENT_TIMESTAMP
> > test in a multi-statement transaction want to check based on transaction
> > start, or on the tuples visible at the time the statement started?
>
> Well, in a serializable transaction there would be no difference at all, at
> least if CURRENT_TIMESTAMP is consistent within the transaction. Any changes
> outside the transaction after SetQuerySnapshot would not be seen by the
> transaction anyway.
>
> In read-commited, I think it's different. If CURRENT_TIMESTAMP is frozen,
> than the behavior would be the same as in serializable level, if
> CURRENT_TIMESTAMP advances with each statement, the result would also
> change. That is an inherent problem with read-commited though and has not so
> much to do with the timestamp behavior.
>
> Regards,
> Michael Paesold
>
>
>
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
On Sat, 5 Oct 2002 00:29:03 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
>
>OK, are we agreed to leave CURRENT_TIMESTAMP/now() alone and just add
>now("string")? If no one replies, I will assume that is a yes and I
>will add it to TODO.
So my view of CURRENT_TIMESTAMP not being spec compliant didn't find
much agreement. No problem, such is life.
May I suggest that a "Compatibility" section is added to the bottom of
functions-datetime.html?
In case this issue is revisited later let me add for the archives:
On Fri, 04 Oct 2002 09:54:42 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>Freezing CURRENT_TIMESTAMP goes right along with that, and in fact makes
>a lot of sense, because it tells you exactly what time your snapshot
>of the database state was taken.
I like this interpretation. But bear in mind that a transaction's own
actions are visible to later commands in the same transaction.
Looking at the clock is an "own action", so this is perfectly
compatible with (my reading of) General Rule 1.
A statement does not see its own modifications which corresponds to
(my interpretation of) General Rule 3.
And one last thought: There are applications out there that are not
written for one specific database backend. Having to replace
CURRENT_TIMESTAMP by PG-specific now('statement') is just one more
pain in trying to be portable across different backends.
ServusManfred
Manfred Koizar <mkoi-pg@aon.at> writes:
> And one last thought: There are applications out there that are not
> written for one specific database backend. Having to replace
> CURRENT_TIMESTAMP by PG-specific now('statement') is just one more
> pain in trying to be portable across different backends.
Based on this discussion, it seems any application that depends on a
specific behavior of CURRENT_TIMESTAMP is going to have portability
problems anyway. Even if we did change CURRENT_TIMESTAMP to match
now('statement'), it would not act exactly like anyone else's.
regards, tom lane
Yes, I agree with you Manfred, but more people _don't_ want it to
change, and like it the way it is, so we will just keep it and add
now("string").
Added to TODO:
* Add now("transaction|statement|clock") functionality
I have attached an SGML patch that explains the issues with
CURRENT_TIMESTAMP in more detail.
---------------------------------------------------------------------------
Manfred Koizar wrote:
> On Sat, 5 Oct 2002 00:29:03 -0400 (EDT), Bruce Momjian
> <pgman@candle.pha.pa.us> wrote:
> >
> >OK, are we agreed to leave CURRENT_TIMESTAMP/now() alone and just add
> >now("string")? If no one replies, I will assume that is a yes and I
> >will add it to TODO.
>
> So my view of CURRENT_TIMESTAMP not being spec compliant didn't find
> much agreement. No problem, such is life.
>
> May I suggest that a "Compatibility" section is added to the bottom of
> functions-datetime.html?
>
>
> In case this issue is revisited later let me add for the archives:
>
> On Fri, 04 Oct 2002 09:54:42 -0400, Tom Lane <tgl@sss.pgh.pa.us>
> wrote:
> >Freezing CURRENT_TIMESTAMP goes right along with that, and in fact makes
> >a lot of sense, because it tells you exactly what time your snapshot
> >of the database state was taken.
>
> I like this interpretation. But bear in mind that a transaction's own
> actions are visible to later commands in the same transaction.
> Looking at the clock is an "own action", so this is perfectly
> compatible with (my reading of) General Rule 1.
>
> A statement does not see its own modifications which corresponds to
> (my interpretation of) General Rule 3.
>
> And one last thought: There are applications out there that are not
> written for one specific database backend. Having to replace
> CURRENT_TIMESTAMP by PG-specific now('statement') is just one more
> pain in trying to be portable across different backends.
>
> 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
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.126
diff -c -c -r1.126 func.sgml
*** doc/src/sgml/func.sgml 24 Sep 2002 20:14:58 -0000 1.126
--- doc/src/sgml/func.sgml 5 Oct 2002 19:00:15 -0000
***************
*** 4293,4304 ****
</informalexample>
<para>
! 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.
</para>
<para>
All the date/time data types also accept the special literal value
--- 4293,4309 ----
</informalexample>
<para>
! It is important to realize that
! <function>CURRENT_TIMESTAMP</function> and related functions return
! the start time of the current transaction; their values do not
! change during the transaction. <function>timeofday()</function>
! returns the wall clock time and does advance during transactions.
</para>
+
+ <note>
+ Many other database systems advance these values more
+ frequently.
+ </note>
<para>
All the date/time data types also accept the special literal value
Hello!
On Sat, 5 Oct 2002, Bruce Momjian wrote:
>
> Yes, I agree with you Manfred, but more people _don't_ want it to
> change, and like it the way it is, so we will just keep it and add
> now("string").
IMHO the best way could be GUC-default/SET session-based variable
controlling the behaviour. By default old Pg one, but ppl can set
standard-compliant. Such changes were done often in past, look at "group
by" behaviour changes 6.4->6.5, default Pg datetime representation format
change, etc. I think those who concern interoperability confirm that it's
much easy to add one SET per session then replace all CURRENT_STAMP to
now(blah-blah-blah). Moreover, ppl who need old behaviour can easily
revert to this by just one SET (in case GUC is set to new behaviour).
>
> Added to TODO:
>
> * Add now("transaction|statement|clock") functionality
>
> I have attached an SGML patch that explains the issues with
> CURRENT_TIMESTAMP in more detail.
--
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.
Yury Bokhoncovich wrote:
> Hello!
>
> On Sat, 5 Oct 2002, Bruce Momjian wrote:
>
> >
> > Yes, I agree with you Manfred, but more people _don't_ want it to
> > change, and like it the way it is, so we will just keep it and add
> > now("string").
>
> IMHO the best way could be GUC-default/SET session-based variable
> controlling the behaviour. By default old Pg one, but ppl can set
> standard-compliant. Such changes were done often in past, look at "group
> by" behaviour changes 6.4->6.5, default Pg datetime representation format
> change, etc. I think those who concern interoperability confirm that it's
> much easy to add one SET per session then replace all CURRENT_STAMP to
> now(blah-blah-blah). Moreover, ppl who need old behaviour can easily
> revert to this by just one SET (in case GUC is set to new behaviour).
Let's see if people want the more standards-compliant behavior before
adding a GUC, no?
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073