clock_timestamp() and transaction_timestamp() function

Wang Mike wrote:

add clock_timestamp() and transaction_timestamp() function

see TODO list get more

-----------------------------------------------------------------

diff -u -r ../cvs/pgsql/src/backend/utils/adt/timestamp.c 
../pgsql/src/backend/utils/adt/timestamp.c
--- ../cvs/pgsql/src/backend/utils/adt/timestamp.c	2003-07-18 
21:59:07.000000000 +0800
+++ ../pgsql/src/backend/utils/adt/timestamp.c	2003-07-18 
22:45:03.000000000 +0800
@@ -28,6 +28,7 @@
#include "miscadmin.h"
#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/nabstime.h"

/*
* gcc's -ffast-math switch breaks routines that expect exact results from
@@ -835,6 +836,9 @@
return TRUE;
} /* EncodeSpecialTimestamp() */

+/* see pg_proc.h function now()  [line 1602]
+ * deleted by Xiongjian Wang (Mike Wang)
+ * Email: mikewang@zhengmai.com.cn
Datum
now(PG_FUNCTION_ARGS)
{
@@ -848,6 +852,56 @@

PG_RETURN_TIMESTAMPTZ(result);
}
+*/
+
+/*
+ * Function transaction_timestamp() return current transaction timestamp.
+ * Added by Xiongjian Wang (Mike Wang)
+ * Email: mikewang@zhengmai.com.cn
+*/
+Datum
+transaction_timestamp(PG_FUNCTION_ARGS)
+{
+	TimestampTz result;
+	AbsoluteTime sec;
+	int			usec;
+
+	sec = GetCurrentTransactionStartTimeUsec(&usec);
+
+	result = AbsoluteTimeUsecToTimestampTz(sec, usec);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}	/* transaction_timestamp() */
+
+/*
+ * Function clock_timestamp() return current clock timestamp.
+ * Added by Xiongjian Wang (Mike Wang)
+ * Email: mikewang@zhengmai.com.cn
+*/
+Datum
+clock_timestamp(PG_FUNCTION_ARGS)
+{
+	TimestampTz result;
+	AbsoluteTime sec;
+	int			usec;
+
+	sec = GetCurrentAbsoluteTimeUsec(&usec);
+
+	result = AbsoluteTimeUsecToTimestampTz(sec, usec);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}	/* clock_timestamp() */
+
+/* Please tell me what is statement_timestamp() ?
+ * Email: mikewang@zhengmai.com.cn
+Datum
+statement_timestamp(PG_FUNCTION_ARGS)
+{
+	TimestampTz result;
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+*/

void
dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec)
diff -u -r ../cvs/pgsql/src/include/catalog/pg_proc.h 
../pgsql/src/include/catalog/pg_proc.h
--- ../cvs/pgsql/src/include/catalog/pg_proc.h	2003-07-01 
08:04:38.000000000 +0800
+++ ../pgsql/src/include/catalog/pg_proc.h	2003-07-18 21:47:15.000000000 
+0800
@@ -1598,7 +1598,9 @@
DESCR("convert date and time with time zone to timestamp with time zone");
DATA(insert OID = 1298 (  timetzdate_pl    PGNSP PGUID 14 f f t f i 2 1184 
"1266 1082"	"select ($2 + $1)" - _null_ ));
DESCR("convert time with time zone and date to timestamp with time zone");
-DATA(insert OID = 1299 (  now			   PGNSP PGUID 12 f f t f s 0 1184 ""  now 
- _null_ ));
+
+/* Function Now() only a alias of transaction_timestamp() */
+DATA(insert OID = 1299 (now   PGNSP PGUID 12 f f t f s 0 1184 "" 
transaction_timestamp - _null_ ));
DESCR("current transaction time");

/* OIDS 1300 - 1399 */
@@ -3406,6 +3408,12 @@
DESCR("I/O");

+DATA(insert OID = 2510 (transaction_timestamp PGNSP PGUID 12 f f t f s 0 
1184 ""  transaction_timestamp - _null_ ));
+DESCR("current transaction time");
+
+DATA(insert OID = 2511 (clock_timestamp PGNSP PGUID 12 f f t f v 0 1184 "" 
clock_timestamp - _null_ ));
+DESCR("current clock time");
+
/*
* Symbolic values for provolatile column: these indicate whether the 
result
* of a function is dependent *only* on the values of its explicit 
arguments,
diff -u -r ../cvs/pgsql/src/include/utils/timestamp.h 
../pgsql/src/include/utils/timestamp.h
--- ../cvs/pgsql/src/include/utils/timestamp.h	2003-05-13 
07:08:52.000000000 +0800
+++ ../pgsql/src/include/utils/timestamp.h	2003-07-18 21:24:12.000000000 
+0800
@@ -231,7 +231,9 @@
extern Datum timestamptz_trunc(PG_FUNCTION_ARGS);
extern Datum timestamptz_part(PG_FUNCTION_ARGS);

-extern Datum now(PG_FUNCTION_ARGS);
+/* extern Datum now(PG_FUNCTION_ARGS); */
+extern Datum transaction_timestamp(PG_FUNCTION_ARGS);
+extern Datum transaction_timestamp(PG_FUNCTION_ARGS);

/* Internal routines (not fmgr-callable) */

_________________________________________________________________
?????????????? MSN Messenger: http://messenger.msn.com/cn

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

We have started 7.5 development so we can now work with you to complete
this item. Your question was what does statement_timestamp() show.
That has been discussed and it is a little tricky. The idea is that is
should be start of a single statement that arrives from the user or is
requestd by a client. However, this gets muddled because SPI can issue
queries and plpgsql functions can too.

I am now thinking of doing it in exec_simple_query().

I see your mailbox is now not availble so perhaps we will have to
continue this work ourselves.

---------------------------------------------------------------------------

Wang Mike wrote:

add clock_timestamp() and transaction_timestamp() function

see TODO list get more

-----------------------------------------------------------------

diff -u -r ../cvs/pgsql/src/backend/utils/adt/timestamp.c 
../pgsql/src/backend/utils/adt/timestamp.c
--- ../cvs/pgsql/src/backend/utils/adt/timestamp.c	2003-07-18 
21:59:07.000000000 +0800
+++ ../pgsql/src/backend/utils/adt/timestamp.c	2003-07-18 
22:45:03.000000000 +0800
@@ -28,6 +28,7 @@
#include "miscadmin.h"
#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/nabstime.h"

/*
* gcc's -ffast-math switch breaks routines that expect exact results from
@@ -835,6 +836,9 @@
return TRUE;
} /* EncodeSpecialTimestamp() */

+/* see pg_proc.h function now()  [line 1602]
+ * deleted by Xiongjian Wang (Mike Wang)
+ * Email: mikewang@zhengmai.com.cn
Datum
now(PG_FUNCTION_ARGS)
{
@@ -848,6 +852,56 @@

PG_RETURN_TIMESTAMPTZ(result);
}
+*/
+
+/*
+ * Function transaction_timestamp() return current transaction timestamp.
+ * Added by Xiongjian Wang (Mike Wang)
+ * Email: mikewang@zhengmai.com.cn
+*/
+Datum
+transaction_timestamp(PG_FUNCTION_ARGS)
+{
+	TimestampTz result;
+	AbsoluteTime sec;
+	int			usec;
+
+	sec = GetCurrentTransactionStartTimeUsec(&usec);
+
+	result = AbsoluteTimeUsecToTimestampTz(sec, usec);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}	/* transaction_timestamp() */
+
+/*
+ * Function clock_timestamp() return current clock timestamp.
+ * Added by Xiongjian Wang (Mike Wang)
+ * Email: mikewang@zhengmai.com.cn
+*/
+Datum
+clock_timestamp(PG_FUNCTION_ARGS)
+{
+	TimestampTz result;
+	AbsoluteTime sec;
+	int			usec;
+
+	sec = GetCurrentAbsoluteTimeUsec(&usec);
+
+	result = AbsoluteTimeUsecToTimestampTz(sec, usec);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}	/* clock_timestamp() */
+
+/* Please tell me what is statement_timestamp() ?
+ * Email: mikewang@zhengmai.com.cn
+Datum
+statement_timestamp(PG_FUNCTION_ARGS)
+{
+	TimestampTz result;
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+*/

void
dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec)
diff -u -r ../cvs/pgsql/src/include/catalog/pg_proc.h 
../pgsql/src/include/catalog/pg_proc.h
--- ../cvs/pgsql/src/include/catalog/pg_proc.h	2003-07-01 
08:04:38.000000000 +0800
+++ ../pgsql/src/include/catalog/pg_proc.h	2003-07-18 21:47:15.000000000 
+0800
@@ -1598,7 +1598,9 @@
DESCR("convert date and time with time zone to timestamp with time zone");
DATA(insert OID = 1298 (  timetzdate_pl    PGNSP PGUID 14 f f t f i 2 1184 
"1266 1082"	"select ($2 + $1)" - _null_ ));
DESCR("convert time with time zone and date to timestamp with time zone");
-DATA(insert OID = 1299 (  now			   PGNSP PGUID 12 f f t f s 0 1184 ""  now 
- _null_ ));
+
+/* Function Now() only a alias of transaction_timestamp() */
+DATA(insert OID = 1299 (now   PGNSP PGUID 12 f f t f s 0 1184 "" 
transaction_timestamp - _null_ ));
DESCR("current transaction time");

/* OIDS 1300 - 1399 */
@@ -3406,6 +3408,12 @@
DESCR("I/O");

+DATA(insert OID = 2510 (transaction_timestamp PGNSP PGUID 12 f f t f s 0 
1184 ""  transaction_timestamp - _null_ ));
+DESCR("current transaction time");
+
+DATA(insert OID = 2511 (clock_timestamp PGNSP PGUID 12 f f t f v 0 1184 "" 
clock_timestamp - _null_ ));
+DESCR("current clock time");
+
/*
* Symbolic values for provolatile column: these indicate whether the 
result
* of a function is dependent *only* on the values of its explicit 
arguments,
diff -u -r ../cvs/pgsql/src/include/utils/timestamp.h 
../pgsql/src/include/utils/timestamp.h
--- ../cvs/pgsql/src/include/utils/timestamp.h	2003-05-13 
07:08:52.000000000 +0800
+++ ../pgsql/src/include/utils/timestamp.h	2003-07-18 21:24:12.000000000 
+0800
@@ -231,7 +231,9 @@
extern Datum timestamptz_trunc(PG_FUNCTION_ARGS);
extern Datum timestamptz_part(PG_FUNCTION_ARGS);

-extern Datum now(PG_FUNCTION_ARGS);
+/* extern Datum now(PG_FUNCTION_ARGS); */
+extern Datum transaction_timestamp(PG_FUNCTION_ARGS);
+extern Datum transaction_timestamp(PG_FUNCTION_ARGS);

/* Internal routines (not fmgr-callable) */

_________________________________________________________________
?????????????? MSN Messenger: http://messenger.msn.com/cn

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

Bruce Momjian writes:

We have started 7.5 development so we can now work with you to complete
this item. Your question was what does statement_timestamp() show.
That has been discussed and it is a little tricky. The idea is that is
should be start of a single statement that arrives from the user or is
requestd by a client. However, this gets muddled because SPI can issue
queries and plpgsql functions can too.

Are these supposed to be user-visible functions? What are they supposed
to do?

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut wrote:

Bruce Momjian writes:

We have started 7.5 development so we can now work with you to complete
this item. Your question was what does statement_timestamp() show.
That has been discussed and it is a little tricky. The idea is that is
should be start of a single statement that arrives from the user or is
requestd by a client. However, this gets muddled because SPI can issue
queries and plpgsql functions can too.

Are these supposed to be user-visible functions? What are they supposed
to do?

Yes, user-visible. They give additional current timestamp information.
transaction_timestamp() is the same as current_timestamp,
clock_timestamp is the same as gettimeofday(), and statement_timestamp
is a new one that shows statement 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

Bruce Momjian writes:

Yes, user-visible. They give additional current timestamp information.
transaction_timestamp() is the same as current_timestamp,
clock_timestamp is the same as gettimeofday(), and statement_timestamp
is a new one that shows statement start time.

And what would be the point of that?

--
Peter Eisentraut peter_e@gmx.net

Yes, user-visible. They give additional current timestamp information.
transaction_timestamp() is the same as current_timestamp,
clock_timestamp is the same as gettimeofday(), and statement_timestamp
is a new one that shows statement start time.

And what would be the point of that?

Bean counters love this stuff, especially during audits. =D

--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

Peter Eisentraut wrote:

Bruce Momjian writes:

Yes, user-visible. They give additional current timestamp information.
transaction_timestamp() is the same as current_timestamp,
clock_timestamp is the same as gettimeofday(), and statement_timestamp
is a new one that shows statement start time.

And what would be the point of that?

The goal was to give a unified API to the various time measurements:

[clock|statement|transaction]_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

Bruce Momjian writes:

The goal was to give a unified API to the various time measurements:

[clock|statement|transaction]_timestamp

It would be very useful if we had a parameter that controlled whether
current_timestamp maps to statement_timestamp or to transaction_timestamp.
There seems to be quite some disagreement over this issue, and this would
help resolve it and also prevent users from unnecessarily using
nonstandard functions in their application.

The name "clock_timestamp" seems kind of unfortunate. Where else would a
time stamp come from if not from the clock? Why is this functionality
needed anyway? It seems that it could only serve to produce
nondeterministic, unreproduceable results.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut wrote:

Bruce Momjian writes:

The goal was to give a unified API to the various time measurements:

[clock|statement|transaction]_timestamp

It would be very useful if we had a parameter that controlled whether
current_timestamp maps to statement_timestamp or to transaction_timestamp.
There seems to be quite some disagreement over this issue, and this would
help resolve it and also prevent users from unnecessarily using
nonstandard functions in their application.

The name "clock_timestamp" seems kind of unfortunate. Where else would a
time stamp come from if not from the clock? Why is this functionality
needed anyway? It seems that it could only serve to produce
nondeterministic, unreproduceable results.

Agreed. You are thinking a GUC like statement_current_timestamp which
defaults to false?

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

Peter Eisentraut <peter_e@gmx.net> writes:

It would be very useful if we had a parameter that controlled whether
current_timestamp maps to statement_timestamp or to transaction_timestamp.

This is a nonstarter, as is the previous proposal to have a single
function with an explicit parameter that selects the behavior. The
reason is that any such function would have to be treated as completely
non-optimizable. It's really important that current_timestamp be STABLE
so that queries like
where entrytimestamp >= current_timestamp - '10 minutes'
can use an index. This means you can't have options that make it not
be STABLE.

The name "clock_timestamp" seems kind of unfortunate.

Agreed, it's not the best choice.

Why is this functionality needed anyway?

Performance measurements within plpgsql functions, for example.
I am unconvinced that anyone has really proven the need for
statement_timestamp, but a cleaner replacement for timeofday()
would be nice to have.

regards, tom lane

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

It would be very useful if we had a parameter that controlled whether
current_timestamp maps to statement_timestamp or to transaction_timestamp.

This is a nonstarter, as is the previous proposal to have a single
function with an explicit parameter that selects the behavior. The
reason is that any such function would have to be treated as completely
non-optimizable. It's really important that current_timestamp be STABLE
so that queries like
where entrytimestamp >= current_timestamp - '10 minutes'
can use an index. This means you can't have options that make it not
be STABLE.

Oh, I forgot about that. Peter seems only to want statement_timestamp
and transaction_timestamp. Aren't those both stable if
statement_timestamp is set from exec_simple_query?

The name "clock_timestamp" seems kind of unfortunate.

Agreed, it's not the best choice.

Why is this functionality needed anyway?

Performance measurements within plpgsql functions, for example.
I am unconvinced that anyone has really proven the need for
statement_timestamp, but a cleaner replacement for timeofday()
would be nice to have.

Yep, and if we say we need that then the GUC option doesn't work because
the certainly will not be stable.

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

Tom Lane writes:

This is a nonstarter, as is the previous proposal to have a single
function with an explicit parameter that selects the behavior. The
reason is that any such function would have to be treated as completely
non-optimizable.

Why? We would just need to ensure that the mode cannot be changed at
critical times.

I am unconvinced that anyone has really proven the need for
statement_timestamp,

Compatibility with other database systems might be one (if it is
accessible through current_timestamp).

--
Peter Eisentraut peter_e@gmx.net

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

This is a nonstarter,

Oh, I forgot about that. Peter seems only to want statement_timestamp
and transaction_timestamp. Aren't those both stable if
statement_timestamp is set from exec_simple_query?

Hm. If you restricted the option to only those two values, it might be
safe as far as the stability argument goes, but it would be the sort
of thing where we'd have to be perpetually on our guard against someone
making the "obvious extension" and thereby subtly breaking things.

In general, I do not like options that subtly change the behavior of
long-established functions, anyway. Seems like a great recipe for
breaking people's applications. I'm okay with adding new functions as
per the already-agreed-to set of names (though like Peter I wish we
could think of something clearer than clock_timestamp()). Rejiggering
the behavior of already-existing functions was not part of what had
been agreed to.

regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

This is a nonstarter, as is the previous proposal to have a single
function with an explicit parameter that selects the behavior. The
reason is that any such function would have to be treated as completely
non-optimizable.

Why? We would just need to ensure that the mode cannot be changed at
critical times.

"critical time" being anywhere within a session, I guess? Either that
or keep track of whether any cached plans depend on the stability
assumption.

regards, tom lane

Tom Lane wrote:

In general, I do not like options that subtly change the behavior of
long-established functions, anyway. Seems like a great recipe for
breaking people's applications. I'm okay with adding new functions as
per the already-agreed-to set of names (though like Peter I wish we
could think of something clearer than clock_timestamp()). Rejiggering
the behavior of already-existing functions was not part of what had
been agreed to.

instant_timestamp? immediate_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

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

This is a nonstarter, as is the previous proposal to have a single
function with an explicit parameter that selects the behavior. The
reason is that any such function would have to be treated as completely
non-optimizable.

Why? We would just need to ensure that the mode cannot be changed at
critical times.

"critical time" being anywhere within a session, I guess? Either that
or keep track of whether any cached plans depend on the stability
assumption.

Ewe, that isn't going to work --- making the value unchangable during the
session.

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