transction_timestamp() inside of procedures
This function shows that only clock_timestamp() advances inside a
procedure, not statement_timestamp() or transaction_timestamp():
CREATE OR REPLACE PROCEDURE test_timestamp () AS $$
DECLARE
str TEXT;
BEGIN
WHILE TRUE LOOP
-- clock_timestamp() is updated on every loop
SELECT clock_timestamp() INTO str;
RAISE NOTICE 'clock %', str;
SELECT statement_timestamp() INTO str;
RAISE NOTICE 'statement %', str;
SELECT transaction_timestamp() INTO str;
RAISE NOTICE 'transaction %', str;
COMMIT;
PERFORM pg_sleep(2);
END LOOP;
END
$$ LANGUAGE plpgsql;
CALL test_timestamp();
NOTICE: clock 2018-09-20 19:38:22.575794-04
NOTICE: statement 2018-09-20 19:38:22.575685-04
NOTICE: transaction 2018-09-20 19:38:22.575685-04
--> NOTICE: clock 2018-09-20 19:38:24.578027-04
NOTICE: statement 2018-09-20 19:38:22.575685-04
NOTICE: transaction 2018-09-20 19:38:22.575685-04
This surprised me since I expected a new timestamp after commit. Is
this something we want to change or document? Are there other
per-transaction behaviors we should adjust?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
On Thu, Sep 20, 2018 at 07:40:40PM -0400, Bruce Momjian wrote:
This surprised me since I expected a new timestamp after commit. Is
this something we want to change or document? Are there other
per-transaction behaviors we should adjust?
I don't quite follow your argument here. clock_timestamp is known to be
volatile, while the two others are stable, so its value can change
within a transaction.
--
Michael
Hi,
On 2018-09-21 13:55:36 +0900, Michael Paquier wrote:
On Thu, Sep 20, 2018 at 07:40:40PM -0400, Bruce Momjian wrote:
This surprised me since I expected a new timestamp after commit. Is
this something we want to change or document? Are there other
per-transaction behaviors we should adjust?I don't quite follow your argument here. clock_timestamp is known to be
volatile, while the two others are stable, so its value can change
within a transaction.
Isn't the point that transaction_timestamp() does *not* currently change
its value, even though the transaction (although not the outermost
statement) has finished?
I think Bruce has quite the point here.
Greetings,
Andres Freund
On Thu, Sep 20, 2018 at 10:12:06PM -0700, Andres Freund wrote:
Isn't the point that transaction_timestamp() does *not* currently change
its value, even though the transaction (although not the outermost
statement) has finished?
Ouch, yes. I see the point now. Indeed that's strange to not have a
new transaction timestamp after commit within the DO block..
I need a break of a couple of minutes.
--
Michael
On Fri, Sep 21, 2018 at 02:34:25PM +0900, Michael Paquier wrote:
On Thu, Sep 20, 2018 at 10:12:06PM -0700, Andres Freund wrote:
Isn't the point that transaction_timestamp() does *not* currently change
its value, even though the transaction (although not the outermost
statement) has finished?Ouch, yes. I see the point now. Indeed that's strange to not have a
new transaction timestamp after commit within the DO block..
So, this puts us in an odd position. Right now everyone knows that
statement_timestamp() is only changed by the outer statement, i.e., a
SELECT in a function doesn't change statement_timestamp(). So, there
is an argument that transaction_timestamp() should do the same and not
change in a function --- in fact, if it does change, it would mean that
transaction_timestamp() changes in a function, but statement_timestamp()
doesn't --- that seems very odd. It would mean that new statements in a
function don't change statement_timestamp(), but new transctions in a
function do --- again, very odd.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +On Fri, Sep 21, 2018 at 06:28:22AM -0400, Bruce Momjian wrote:
On Fri, Sep 21, 2018 at 02:34:25PM +0900, Michael Paquier wrote:
On Thu, Sep 20, 2018 at 10:12:06PM -0700, Andres Freund wrote:
Isn't the point that transaction_timestamp() does *not* currently change
its value, even though the transaction (although not the outermost
statement) has finished?Ouch, yes. I see the point now. Indeed that's strange to not have a
new transaction timestamp after commit within the DO block..So, this puts us in an odd position. Right now everyone knows that
statement_timestamp() is only changed by the outer statement, i.e., a
SELECT in a function doesn't change statement_timestamp(). So, there
is an argument that transaction_timestamp() should do the same and not
change in a function --- in fact, if it does change, it would mean that
transaction_timestamp() changes in a function, but statement_timestamp()
doesn't --- that seems very odd. It would mean that new statements in a
function don't change statement_timestamp(), but new transctions in a
function do --- again, very odd.
Sorry I was unclear about this. It is only the third loop that proves
it is not advancing:
NOTICE: clock 2018-09-21 18:01:00.63704-04
NOTICE: statement 2018-09-21 18:01:00.636509-04
NOTICE: transaction 2018-09-21 18:01:00.636509-04
NOTICE: clock 2018-09-21 18:01:02.640033-04
NOTICE: statement 2018-09-21 18:01:00.636509-04
NOTICE: transaction 2018-09-21 18:01:00.636509-04
NOTICE: clock 2018-09-21 18:01:04.642266-04
NOTICE: statement 2018-09-21 18:01:00.636509-04
--> NOTICE: transaction 2018-09-21 18:01:00.636509-04
Keep in mind that transaction_timestamp() is CURRENT_TIMESTAMP.
I have always thought of clock/statement/transation as decreasing levels
of time precision, and it might be odd to change that. I don't think we
want to change the behavior of statement_timestamp() in procedures, so
that kind of requires us not to change transaction_timestamp() inside of
procedures.
However, no change in behavior causes the problem that if you have a
transaction block using transaction_timestamp() or CURRENT_TIMESTAMP,
and you move it into a procedure, the behavior of those functions will
change, but this was always true of moving statement_timestamp() into a
function, and I have never heard a complaint about that.
Does the SQL standard have anything to say about CURRENT_TIMESTAMP in
procedures? Do we need another function that does advance on procedure
commit?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +On Fri, Sep 21, 2018 at 06:35:02PM -0400, Bruce Momjian wrote:
Does the SQL standard have anything to say about CURRENT_TIMESTAMP in
procedures? Do we need another function that does advance on procedure
commit?
I found a section in the SQL standards that talks about it, but I don't
understand it. Can I quote the paragraph here?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +Hi,
On 2018-09-20 19:40:40 -0400, Bruce Momjian wrote:
This function shows that only clock_timestamp() advances inside a
procedure, not statement_timestamp() or transaction_timestamp():CREATE OR REPLACE PROCEDURE test_timestamp () AS $$
DECLARE
str TEXT;
BEGIN
WHILE TRUE LOOP
-- clock_timestamp() is updated on every loop
SELECT clock_timestamp() INTO str;
RAISE NOTICE 'clock %', str;
SELECT statement_timestamp() INTO str;
RAISE NOTICE 'statement %', str;
SELECT transaction_timestamp() INTO str;
RAISE NOTICE 'transaction %', str;
COMMIT;PERFORM pg_sleep(2);
END LOOP;
END
$$ LANGUAGE plpgsql;CALL test_timestamp();
NOTICE: clock 2018-09-20 19:38:22.575794-04
NOTICE: statement 2018-09-20 19:38:22.575685-04
NOTICE: transaction 2018-09-20 19:38:22.575685-04--> NOTICE: clock 2018-09-20 19:38:24.578027-04
NOTICE: statement 2018-09-20 19:38:22.575685-04
NOTICE: transaction 2018-09-20 19:38:22.575685-04This surprised me since I expected a new timestamp after commit. Is
this something we want to change or document? Are there other
per-transaction behaviors we should adjust?
ISTM this is an issue that belongs on the open items list. Peter, could
you comment?
Greetings,
Andres Freund
On Sat, Sep 22, 2018 at 5:27 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Fri, Sep 21, 2018 at 06:35:02PM -0400, Bruce Momjian wrote:
Does the SQL standard have anything to say about CURRENT_TIMESTAMP in
procedures? Do we need another function that does advance on procedure
commit?I found a section in the SQL standards that talks about it, but I don't
understand it. Can I quote the paragraph here?
I've seen others do it; and small sections of copyrighted material posted
for commentary or critique would likely be covered by "fair use" exemptions.
David J.
On 2018-09-25 14:50:02 -0700, Andres Freund wrote:
ISTM this is an issue that belongs on the open items list. Peter, could
you comment?
Done so, per discussion with the rest of the RMT.
On Tue, Sep 25, 2018 at 03:01:31PM -0700, David G. Johnston wrote:
On Sat, Sep 22, 2018 at 5:27 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Fri, Sep 21, 2018 at 06:35:02PM -0400, Bruce Momjian wrote:
Does the SQL standard have anything to say about CURRENT_TIMESTAMP in
procedures?� Do we need another function that does advance on procedure
commit?I found a section in the SQL standards that talks about it, but I don't
understand it.� Can I quote the paragraph here?I've seen others do it; and small sections of copyrighted material posted for
commentary or critique would likely be covered by "fair use" exemptions.
Well, it is an entire paragraph, so it might be too much. If you
download the zip file here:
http://www.wiscorp.com/sql200n.zip
and open 5CD2-02-Foundation-2006-01.pdf, at the top of page 289 under
General Rules, paragraph label 3 has the description. It talks about
procedure statements and trigger functions, which seems promising.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
On 22/09/2018 00:35, Bruce Momjian wrote:
I have always thought of clock/statement/transation as decreasing levels
of time precision, and it might be odd to change that. I don't think we
want to change the behavior of statement_timestamp() in procedures, so
that kind of requires us not to change transaction_timestamp() inside of
procedures.However, no change in behavior causes the problem that if you have a
transaction block using transaction_timestamp() or CURRENT_TIMESTAMP,
and you move it into a procedure, the behavior of those functions will
change, but this was always true of moving statement_timestamp() into a
function, and I have never heard a complaint about that.
Right. Statement timestamp actually means the timestamp of the
top-level statement, because it's set where the protocol interaction
happens. The transaction timestamp is implemented as the statement
timestamp when the transaction starts, but for intra-procedural
transactions, the statement timestamp does not advance, so the
transaction timestamp doesn't change.
Note that this also affects the xact_start column in pg_stat_activity.
We could certainly address this by adding three or four or five new
timestamps that cover all these varieties. But perhaps it's worth
asking what these timestamps are useful for and which ones we really need.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Sep 26, 2018 at 02:38:25PM +0200, Peter Eisentraut wrote:
On 22/09/2018 00:35, Bruce Momjian wrote:
I have always thought of clock/statement/transation as decreasing levels
of time precision, and it might be odd to change that. I don't think we
want to change the behavior of statement_timestamp() in procedures, so
that kind of requires us not to change transaction_timestamp() inside of
procedures.However, no change in behavior causes the problem that if you have a
transaction block using transaction_timestamp() or CURRENT_TIMESTAMP,
and you move it into a procedure, the behavior of those functions will
change, but this was always true of moving statement_timestamp() into a
function, and I have never heard a complaint about that.Right. Statement timestamp actually means the timestamp of the
top-level statement, because it's set where the protocol interaction
happens. The transaction timestamp is implemented as the statement
timestamp when the transaction starts, but for intra-procedural
transactions, the statement timestamp does not advance, so the
transaction timestamp doesn't change.Note that this also affects the xact_start column in pg_stat_activity.
We could certainly address this by adding three or four or five new
timestamps that cover all these varieties. But perhaps it's worth
asking what these timestamps are useful for and which ones we really need.
Frankly, we might be fine with just documenting it and see if anyone
complains. I think the top-level statement part is obvious, but I am
not sure the top-level transaction part is. This is because it is clear
the top-level statement causes all the lower statements underneath it,
but the top-level transaction doesn't control all the transactions under
it in the same way.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Bruce Momjian <bruce@momjian.us> writes:
On Wed, Sep 26, 2018 at 02:38:25PM +0200, Peter Eisentraut wrote:
We could certainly address this by adding three or four or five new
timestamps that cover all these varieties. But perhaps it's worth
asking what these timestamps are useful for and which ones we really need.
Frankly, we might be fine with just documenting it and see if anyone
complains.
I'm not for adding a bunch of new action-start timestamps without very
clear use-cases for them, because each one we add means more gettimeday()
overhead that might or might not ever be useful.
I agree that it would be surprising for transaction timestamp to be newer
than statement timestamp. So for now at least, I'd be satisfied with
documenting the behavior.
regards, tom lane
On 2018-Sep-25, Bruce Momjian wrote:
Well, it is an entire paragraph, so it might be too much. If you
download the zip file here:http://www.wiscorp.com/sql200n.zip
and open 5CD2-02-Foundation-2006-01.pdf, at the top of page 289 under
General Rules, paragraph label 3 has the description. It talks about
procedure statements and trigger functions, which seems promising.
I have the 2011 draft, not the 2006 one; you seem to be referring to
<datetime value function> (which is 6.32 in the 2011 draft I have).
General rule 3 is entirely unreadable, and is followed by this note:
WG3:LCY-025 took no action on the preceding instance of general containment.
It was felt that this rule is too complicated, to the point of being virtually
unintelligible. In addition, the rule does not recognize that <datetime value
function>s can be evaluated implicitly as <default option>s. It is believed
that this rule does not reflect actual practice and should be rewritten to
align it with implementations. Note that Subclause 15.1, “Effect of opening a
cursor”, also has a General Rule on this subject. See
Possible Problem FND-992 .
In SQL2016, this rule was removed completely.
I don't think this offers any practical guidance.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-Sep-26, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
On Wed, Sep 26, 2018 at 02:38:25PM +0200, Peter Eisentraut wrote:
We could certainly address this by adding three or four or five new
timestamps that cover all these varieties. But perhaps it's worth
asking what these timestamps are useful for and which ones we really need.Frankly, we might be fine with just documenting it and see if anyone
complains.I'm not for adding a bunch of new action-start timestamps without very
clear use-cases for them, because each one we add means more gettimeday()
overhead that might or might not ever be useful.I agree that it would be surprising for transaction timestamp to be newer
than statement timestamp. So for now at least, I'd be satisfied with
documenting the behavior.
Really? I thought it was practically obvious that for transaction-
controlling procedures, the transaction timestamp would not necessarily
be aligned with the statement timestamp. The surprise would come
together with the usage of the new feature, so existing users would not
be surprised in any way.
I do wonder how do other systems behave in this area, though.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
On 2018-Sep-26, Tom Lane wrote:
I agree that it would be surprising for transaction timestamp to be newer
than statement timestamp. So for now at least, I'd be satisfied with
documenting the behavior.
Really? I thought it was practically obvious that for transaction-
controlling procedures, the transaction timestamp would not necessarily
be aligned with the statement timestamp. The surprise would come
together with the usage of the new feature, so existing users would not
be surprised in any way.
Nope. That's the same poor reasoning we've fallen into in some other
cases, of assuming that "the user" is a point source of knowledge.
But DBMSes tend to interact with lots of different code. If some part
of application A starts using intraprocedure transactions, and then
application B breaks because it wasn't expecting to see xact_start
later than query_start in pg_stat_activity, you've still got a problem.
I'm also a bit hesitant to invent new semantics here based on the
assumption that we've got only one, nonoverlapping, top-level transaction
at a time. It's not terribly hard to imagine suspend-and-resume-
transaction features coming down the pike at some point. What will
we do then? We'll already have a definitional issue for xact_start,
but it'll get worse the more different kinds of xact_start we have.
regards, tom lane
On 2018-Sep-26, Tom Lane wrote:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
On 2018-Sep-26, Tom Lane wrote:
I agree that it would be surprising for transaction timestamp to be newer
than statement timestamp. So for now at least, I'd be satisfied with
documenting the behavior.Really? I thought it was practically obvious that for transaction-
controlling procedures, the transaction timestamp would not necessarily
be aligned with the statement timestamp. The surprise would come
together with the usage of the new feature, so existing users would not
be surprised in any way.Nope. That's the same poor reasoning we've fallen into in some other
cases, of assuming that "the user" is a point source of knowledge.
But DBMSes tend to interact with lots of different code. If some part
of application A starts using intraprocedure transactions, and then
application B breaks because it wasn't expecting to see xact_start
later than query_start in pg_stat_activity, you've still got a problem.
While that's true, I think it's also highly hypothetical.
What could be the use for the transaction timestamp? I think one of the
most important uses (at least in pg_stat_activity) is to verify that
transactions are not taking excessively long time to complete; that's
known to cause all sorts of trouble in Postgres, and probably other
DBMSs too. If we don't accurately measure what it really is, and
instead keep the compatibility behavior, we risk panicking people
because they think some transaction has been running for a long time
when in reality it's just a very long procedure which commits frequently
enough not to be a problem.
I'm also a bit hesitant to invent new semantics here based on the
assumption that we've got only one, nonoverlapping, top-level transaction
at a time. It's not terribly hard to imagine suspend-and-resume-
transaction features coming down the pike at some point. What will
we do then? We'll already have a definitional issue for xact_start,
but it'll get worse the more different kinds of xact_start we have.
This is even more hypothetical.
If we can have a list or stack of running transactions, clearly a single
timestamp value is not sufficient. We could report a single value for
"the oldest transaction", or perhaps "the transaction that's currently
active". But if we wanted to be really thorough about it, we'd need to
report the list of timestamps for each running transaction in the
current session. However, I don't think those future developments would
change what the transaction timestamp is, namely, the start of the
current transaction, not the start of the statement that (after possibly
many iterations) gave rise to the current transaction.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-09-26 12:54:43 -0300, Alvaro Herrera wrote:
On 2018-Sep-26, Tom Lane wrote:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
On 2018-Sep-26, Tom Lane wrote:
I agree that it would be surprising for transaction timestamp to be newer
than statement timestamp. So for now at least, I'd be satisfied with
documenting the behavior.Really? I thought it was practically obvious that for transaction-
controlling procedures, the transaction timestamp would not necessarily
be aligned with the statement timestamp. The surprise would come
together with the usage of the new feature, so existing users would not
be surprised in any way.Nope. That's the same poor reasoning we've fallen into in some other
cases, of assuming that "the user" is a point source of knowledge.
But DBMSes tend to interact with lots of different code. If some part
of application A starts using intraprocedure transactions, and then
application B breaks because it wasn't expecting to see xact_start
later than query_start in pg_stat_activity, you've still got a problem.While that's true, I think it's also highly hypothetical.
What could be the use for the transaction timestamp? I think one of the
most important uses (at least in pg_stat_activity) is to verify that
transactions are not taking excessively long time to complete; that's
known to cause all sorts of trouble in Postgres, and probably other
DBMSs too. If we don't accurately measure what it really is, and
instead keep the compatibility behavior, we risk panicking people
because they think some transaction has been running for a long time
when in reality it's just a very long procedure which commits frequently
enough not to be a problem.
+1
I'm also a bit hesitant to invent new semantics here based on the
assumption that we've got only one, nonoverlapping, top-level transaction
at a time. It's not terribly hard to imagine suspend-and-resume-
transaction features coming down the pike at some point. What will
we do then? We'll already have a definitional issue for xact_start,
but it'll get worse the more different kinds of xact_start we have.This is even more hypothetical.
If we can have a list or stack of running transactions, clearly a single
timestamp value is not sufficient. We could report a single value for
"the oldest transaction", or perhaps "the transaction that's currently
active". But if we wanted to be really thorough about it, we'd need to
report the list of timestamps for each running transaction in the
current session. However, I don't think those future developments would
change what the transaction timestamp is, namely, the start of the
current transaction, not the start of the statement that (after possibly
many iterations) gave rise to the current transaction.
+1
Greetings,
Andres Freund
Tom Lane wrote:
I agree that it would be surprising for transaction timestamp to be newer
than statement timestamp.
To me it's more surprising to start a new transaction and having
transaction_timestamp() still pointing at the start of a previous
transaction.
This feels like a side-effect of being spawned by a procedure,
and an exception to what transaction_timestamp() normally means
or meant until now.
OTOH transaction_timestamp() being possibly newer than
statement_timestamp() seems like a normal consequence of
transactions being created intra-statement.
+1 for transaction_timestamp() and pg_stat_activity being updated
to follow intra-procedure transactions.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
