please define 'statement' in the glossary

On Fri, 2025-07-11 at 10:34 +0000, PG Doc comments form wrote:

the glossary (https://www.postgresql.org/docs/current/glossary.html) does
not define 'statement' but a definition would be important to understand
other chapters such as this sentence: "statement_timestamp() returns the
start time of the current statement (more specifically, the time of receipt
of the latest command message from the client)." The sentence is from
https://www.postgresql.org/docs/17/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT.
From the same page is this snippet: "start of current statement", in Table
9.33. Date/Time Functions.
It seems 'statement' can mean different things such as 'one operation, such
as a SELECT statement', but it can also mean 'all operations since the last
command message from the client, which can be several SELECTs'.

In the setting of a relational database system like PostgreSQL, "statement"
means "SQL statement". I don't think that that requires a glossary entry.
"SQL statement" is pretty well defined.

I guess your difficulties come from the remark in parentheses, which is
intended to clarify, but clearly does not quite succeed in that.

After looking at the code, I guess what made Tom add the remark in commit
eaf8f312c754 was the fact that an SQL statement is not necessarily processed
in a single go: with the extended query protocol (see chapter 52.2.3),
there is a "parse", a "bind" and an "execute" message from the client, and
each one sets the timestamp reported by statement_timestamp() to a new
value. So, technically, statement_timestamp() has a different value when
the statement is parsed than when it is executed.

However, what matters to the client is the value when the statement starts
executing, because that's the value that will be reported.

So I'd argue that we should remove the parenthetical remark. It confuses
more than it enlightens, and whoever needs to know that level of detail
had better read the code anyway.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:

After looking at the code, I guess what made Tom add the remark in commit
eaf8f312c754 was the fact that an SQL statement is not necessarily processed
in a single go: with the extended query protocol (see chapter 52.2.3),
there is a "parse", a "bind" and an "execute" message from the client, and
each one sets the timestamp reported by statement_timestamp() to a new
value. So, technically, statement_timestamp() has a different value when
the statement is parsed than when it is executed.

However, what matters to the client is the value when the statement starts
executing, because that's the value that will be reported.

So I'd argue that we should remove the parenthetical remark. It confuses
more than it enlightens, and whoever needs to know that level of detail
had better read the code anyway.

After re-reading that text, I feel like the parenthetical remark is
fine, and the real problem is that I used "statement" and "command"
more or less interchangeably in successive sentences. Perhaps
s/command/statement/g throughout the paragraph would improve matters?
Although "statement message" doesn't feel right, so maybe leave that
one alone.

regards, tom lane

On Sun, 2025-07-13 at 11:27 -0400, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

After looking at the code, I guess what made Tom add the remark in commit
eaf8f312c754 was the fact that an SQL statement is not necessarily processed
in a single go: with the extended query protocol (see chapter 52.2.3),
there is a "parse", a "bind" and an "execute" message from the client, and
each one sets the timestamp reported by statement_timestamp() to a new
value. So, technically, statement_timestamp() has a different value when
the statement is parsed than when it is executed.

However, what matters to the client is the value when the statement starts
executing, because that's the value that will be reported.

So I'd argue that we should remove the parenthetical remark. It confuses
more than it enlightens, and whoever needs to know that level of detail
had better read the code anyway.

After re-reading that text, I feel like the parenthetical remark is
fine, and the real problem is that I used "statement" and "command"
more or less interchangeably in successive sentences. Perhaps
s/command/statement/g throughout the paragraph would improve matters?
Although "statement message" doesn't feel right, so maybe leave that
one alone.

Changing "command" to "statement" would be a good move.

I guess I get the remark now: it wants to say that
a) statement_timestamp() shows the time when the client message that
started the execution of the current SQL statement reached the server
and
b) the timestamp isn't reset for nested statements

Perhaps the remark should say "protocol message" or "frontend-backend
protocol message" to make clear that we are not talking about an
SQL statement here.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:

Perhaps the remark should say "protocol message" or "frontend-backend
protocol message" to make clear that we are not talking about an
SQL statement here.

Yeah, that seems like a good idea.

regards, tom lane

... so concretely, about like this?

regards, tom lane

On Sun, Jul 13, 2025 at 2:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

... so concretely, about like this?

We seldom if ever resort to including descriptions involving the fe/be
protocol in the SQL portion of the documentation - rightly considering
(IMO) those to be implementation details (e.g., we don't even directly
mention simple protocol in "psql -c" - though we do link to it under
"multi-statement commands"). Is there no way to avoid that here? I'd be
ok if we'd limit this to a distinction between the simple protocol and the
extended protocol since, as a volatile function, it isn't even like
statement_timestamp can be seen in extended protocol aside from when
execute is sent. So the special case where it doesn't behave as expected
is a simple protocol multi-statement command. An example in psql would
serve to make this much more clear than any wording can do. Possibly added
here or as part of the existing documentation that 'psql -c' points to
[1]: https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-MULTI-STATEMENT

Seems also like maybe SPI should be mentioned explicitly here since it
seems to act like a client in a relevant way. I'm assuming a
statement_timestamp executed within a function will return the same
timestamp the calling statement would.

David J.

[1]: https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-MULTI-STATEMENT
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-MULTI-STATEMENT

On Sun, 2025-07-13 at 17:32 -0700, David G. Johnston wrote:

On Sun, Jul 13, 2025 at 2:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

... so concretely, about like this?

I am fine with the patch as it is.

We seldom if ever resort to including descriptions involving the fe/be protocol
in the SQL portion of the documentation - rightly considering (IMO) those to be
implementation details (e.g., we don't even directly mention simple protocol in
"psql -c" - though we do link to it under "multi-statement commands").
Is there no way to avoid that here?

Well, I would have gladly removed the parenthetical remark, thinking that if
somebody needed to know precisely, she'd read up in the code.
But there is also nothing evil about hints for the initiated, lest they are
of a kind that can confuse beginners.

  I'd be ok if we'd limit this to a
distinction between the simple protocol and the extended protocol since, as a
volatile function, it isn't even like statement_timestamp can be seen in extended
protocol aside from when execute is sent.  So the special case where it doesn't
behave as expected is a simple protocol multi-statement command.

It is STABLE, not VOLATILE, as befits the name, but yes, I see your point.

  An example in
psql would serve to make this much more clear than any wording can do.
Possibly added here or as part of the existing documentation that 'psql -c'
points to [1].  Which probably could be pointed to from here as well.

Perhaps - but I feel uneasy about adding even more documentation. If we show
how statement_timestamp() does *not* work as expected with a multi-statement
command, we might confuse the reader even more. With the improved parenthetical
remark, I'd expect anybody with superficial knowledge of PostgreSQL to just
skip over the remark, with little damage done ("Ah, some comment about internals
that they couldn't help making.").

But if we add examples, we should be ready to explain in depth why it is the way
it is, and then we would have to get even deeper into the discussion of the
protocol that you bemoaned at the beginning of your mail.

Seems also like maybe SPI should be mentioned explicitly here since it seems to
act like a client in a relevant way.  I'm assuming a statement_timestamp executed
within a function will return the same timestamp the calling statement would.

Well, in this case it doesn't act like a client. That would mean dragging up
even more details from a section of the documentation that is only of interest
to hackers.

I think we should let the lions sleep. The documentation of the built-in
functions is mostly of interest to application developers and writers of SQL
and PL/pgSQL, and expanding on SPI and the client-server protocol isn't what's
asked for here. The documentation should be detailed, but there is a fine
line that you shouldn't cross if you don't want to confuse the reader.

The parenthetical remark is hopefully enough to get the interested reader
on the right track.

Yours,
Laurenz Albe

Hello,

I think you are saying that 'statement' in the documentation shall mean
'SQL statement' and not 'the time of receipt of the latest command message
from the client'.

I also think that statement_timestamp() will keep its name, even though the
name can be seen as misleading by some.

I therefore suggest to change the wording in the table on top

from

"Current date and time (start of current statement)"

to

"Current date and time (receipt of the latest command message from the
client)"

and to change the explanatory sentence further down

from

"statement_timestamp() returns the start time of the current statement
(more specifically, the time of receipt of the latest command message from
the client)."

to

"statement_timestamp() returns the time of receipt of the latest command
message from the client."

And leave it to the reader to discover 'well that function's naming is a
bit unfortunate', but not elaborate on that in the documentation.

A bonus would be to define 'command message' in the glossary.

Side note: This is the SQL that I used to teach myself that
'statement_timestamp' does not mean 'the data in the DB is seen by the
calling statement as of this time' but that it just means when the last
message from the client arrived:

--1
do
$body$
begin
drop table if exists st;
create table st(
id serial primary key,
started_at timestamp with time zone
);
insert into st(started_at) values(statement_timestamp());
execute pg_sleep(3);
insert into st(started_at) values(statement_timestamp());
execute pg_sleep(3);
insert into st(started_at) values(statement_timestamp());
end;
$body$;

select * from st;
--id|started_at |
----+-----------------------------+
-- 1|2025-07-10 10:50:55.424 +0000|
-- 2|2025-07-10 10:50:55.424 +0000|
-- 3|2025-07-10 10:50:55.424 +0000|

--2
drop table if exists st;
create table st(
id serial primary key,
started_at timestamp with time zone
);
begin;
insert into st(started_at) values(statement_timestamp());
select pg_sleep(3);
insert into st(started_at) values(statement_timestamp());
select pg_sleep(3);
insert into st(started_at) values(statement_timestamp());
end;
select * from st;

--id|started_at |
----+-----------------------------+
-- 1|2025-07-10 10:58:22.397 +0000|
-- 2|2025-07-10 10:58:25.413 +0000|
-- 3|2025-07-10 10:58:28.429 +0000|

Hope this helps, thank you for all your thoughts on the matter

Kind regards

Peter

On Mon, Jul 14, 2025 at 8:24 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Sun, 2025-07-13 at 17:32 -0700, David G. Johnston wrote:

We seldom if ever resort to including descriptions involving the fe/be protocol
in the SQL portion of the documentation - rightly considering (IMO) those to be
implementation details (e.g., we don't even directly mention simple protocol in
"psql -c" - though we do link to it under "multi-statement commands").
Is there no way to avoid that here?

Well, I would have gladly removed the parenthetical remark, thinking that if
somebody needed to know precisely, she'd read up in the code.

The point that I wanted to convey in this para is that
statement_timestamp() advances when we receive a command from the
client. I don't think that that concept is too deep for the average
user, we just need to choose the right words to convey it. Sadly,
"SQL statement" doesn't have the right connotations, since for example
a command within a SQL-language function is surely a "SQL statement"
for most purposes. We're stuck with the function name, but how can we
explain it?

I understand David's allergy to mentioning the wire protocol. Would
"client message" be better than "protocol message"? I also still like
"command message", even if we're avoiding the word "command" elsewhere
in the para.

regards, tom lane

On Mon, Jul 14, 2025 at 8:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Sun, 2025-07-13 at 17:32 -0700, David G. Johnston wrote:

We seldom if ever resort to including descriptions involving the fe/be

protocol

in the SQL portion of the documentation - rightly considering (IMO)

those to be

implementation details (e.g., we don't even directly mention simple

protocol in

"psql -c" - though we do link to it under "multi-statement commands").
Is there no way to avoid that here?

Well, I would have gladly removed the parenthetical remark, thinking

that if

somebody needed to know precisely, she'd read up in the code.

The point that I wanted to convey in this para is that
statement_timestamp() advances when we receive a command from the
client. I don't think that that concept is too deep for the average
user, we just need to choose the right words to convey it. Sadly,
"SQL statement" doesn't have the right connotations, since for example
a command within a SQL-language function is surely a "SQL statement"
for most purposes. We're stuck with the function name, but how can we
explain it?

I understand David's allergy to mentioning the wire protocol. Would
"client message" be better than "protocol message"? I also still like
"command message", even if we're avoiding the word "command" elsewhere
in the para.

I dislike the word message.

It would be nice if we could say/document: Command means top-level SQL;
Statement references a sub-component of a command.

statement_timestamp() returns the start time of the current top-level
command being executed (but see the note below). statement_timestamp() and
transaction_timestamp() return the same value during the first command of a
transaction, but the statement_timeout will normally advance for each
subsequent command therein.

NOTE: When sending multiple commands in the same physical query (see
53.2.2.1) all included top-level commands will see the same
statement_timestamp() value.

I would then add an example In 53.2.2.1 showing this happening using "psql
-c"

David J.

On Mon, Jul 14, 2025 at 8:45 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

I would then add an example In 53.2.2.1 showing this happening using "psql
-c"

Cannot readily test this presently but I wonder what the following produces:

psql -c "begin; select statement_timestamp(), transaction_timestamp();
select statement_timestamp(), transaction_timestamp(); commit; begin;
select statement_timestamp(), transaction_timestamp(); commit;"

Transaction timestamp should progress while statement timestamp should not,
right?

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

I dislike the word message.

It would be nice if we could say/document: Command means top-level SQL;
Statement references a sub-component of a command.

I think there are enough places where we use those words
interchangeably that it would be very difficult to get to a point of
consistently making the distinction. Moreover, the distinction isn't
important for most purposes. So I think the right route is to make
this para provide a self-contained description of what it's talking
about, rather than to reference a glossary entry that would likely
be misleading for many many other usages.

Perhaps the phrase "top-level statement" could be used, but I'd still
rather provide a parenthetical specification of what we mean by that.

I agree with Laurenz that we don't have to get into details as fine as
what happens with a multi-statement command message. As long as we
can tie statement_timestamp() to the notion of receiving something
from the client, I think that's enough detail for here. Somebody
who wants to probe the edges of that can read the code or experiment,
and the docs will have given them the right mind-set about what is
important.

regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Cannot readily test this presently but I wonder what the following produces:

psql -c "begin; select statement_timestamp(), transaction_timestamp();
select statement_timestamp(), transaction_timestamp(); commit; begin;
select statement_timestamp(), transaction_timestamp(); commit;"

Transaction timestamp should progress while statement timestamp should not,
right?

AFAICT neither one progresses. I think the reason is that (1)
statement timestamp is set by arrival of the command message
and (2) transaction timestamp is set by copying statement timestamp
at the moment of beginning a transaction.

regards, tom lane

On Mon, Jul 14, 2025 at 9:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Cannot readily test this presently but I wonder what the following

produces:

psql -c "begin; select statement_timestamp(), transaction_timestamp();
select statement_timestamp(), transaction_timestamp(); commit; begin;
select statement_timestamp(), transaction_timestamp(); commit;"

Transaction timestamp should progress while statement timestamp should

not,

right?

AFAICT neither one progresses. I think the reason is that (1)
statement timestamp is set by arrival of the command message
and (2) transaction timestamp is set by copying statement timestamp
at the moment of beginning a transaction.

Ok. That explains why "statement_timestamp() and transaction_timestamp()
return the same value during the first command of a transaction," isn't
just stating the obvious. transaction_timestamp() literally returns the
value statement_timestamp().

But when talking about current_timestamp first and saying "Since these
functions return the start time of the current transaction" it does read
more as coincidence as opposed to definition.

I'm fine with this entire section assuming/stating that extended protocol
is in effect and that 53.2.2.1 explains how these behave when executing a
multi-statement simple protocol "script".

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Ok. That explains why "statement_timestamp() and transaction_timestamp()
return the same value during the first command of a transaction," isn't
just stating the obvious. transaction_timestamp() literally returns the
value statement_timestamp().

Right. I'm tempted to be more explicit about that, along the lines of

     <function>statement_timestamp()</function> and <function>transaction_timestamp()</function>
-    return the same value during the first statement of a transaction, but might
-    differ during subsequent statements.
+    return the same value during the first statement of a transaction,
+    because the transaction timestamp is set by copying the statement
+    timestamp when a new transaction starts.

I didn't include that change below though; perhaps it has too much
whiff of implementation detail.

I'm fine with this entire section assuming/stating that extended protocol
is in effect and that 53.2.2.1 explains how these behave when executing a
multi-statement simple protocol "script".

It's incorrect to claim that this only applies to extended protocol,
and besides I thought you didn't want to mention protocol details
here. I can't argue with documenting it in the protocol chapter
though. So about like this?

regards, tom lane

On Mon, Jul 14, 2025 at 10:09 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Ok. That explains why "statement_timestamp() and transaction_timestamp()
return the same value during the first command of a transaction," isn't
just stating the obvious. transaction_timestamp() literally returns the
value statement_timestamp().

Right. I'm tempted to be more explicit about that, along the lines of

<function>statement_timestamp()</function> and
<function>transaction_timestamp()</function>
-    return the same value during the first statement of a transaction,
but might
-    differ during subsequent statements.
+    return the same value during the first statement of a transaction,
+    because the transaction timestamp is set by copying the statement
+    timestamp when a new transaction starts.

I didn't include that change below though; perhaps it has too much
whiff of implementation detail.

I'm fine with this entire section assuming/stating that extended protocol
is in effect and that 53.2.2.1 explains how these behave when executing a
multi-statement simple protocol "script".

It's incorrect to claim that this only applies to extended protocol,
and besides I thought you didn't want to mention protocol details
here.

The boundary for me is, we can/need to make it clear that there are two
modes in which queries can be sent: simple and extended/normal. We call
the first one "multi-query strings" in psql which probably suffices, but
the name "simple" is fine. It's the mention of "messages" that I consider
an implementation detail.

I can't argue with documenting it in the protocol chapter

though.

That paragraph is good.

So about like this?

If we accept that we use the words statement and command interchangeably
then the sole remaining use of command here sticks out because now we have
to explain why commands are different from statements. I'd rather just
remove the parenthetical. It's poorly clarifying a point that it seems you
don't want to clarify more fully here.

Instead of:
<function>statement_timestamp()</function> returns the start time of the
current
statement (more specifically, the time of receipt of the latest command
- message from the client).
+ from the client).

Maybe:
<function>statement_timestamp()</function> returns the start time of the
current client-issued statement (technically, query).

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

If we accept that we use the words statement and command interchangeably
then the sole remaining use of command here sticks out because now we have
to explain why commands are different from statements. I'd rather just
remove the parenthetical. It's poorly clarifying a point that it seems you
don't want to clarify more fully here.

[ shrug... ] I'm inclined to go back to the "command message" wording
then. I don't find "client-issued statement" to be helpful at all;
in particular, it's flat wrong for the multi-statement-query-message
case, because surely all those statements are client-issued. I'm okay
with this text leaving out nitpicky details, but it should leave the
reader with a mental model that more or less matches reality.

regards, tom lane

On Monday, July 14, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

If we accept that we use the words statement and command interchangeably
then the sole remaining use of command here sticks out because now we

have

to explain why commands are different from statements. I'd rather just
remove the parenthetical. It's poorly clarifying a point that it seems

you

don't want to clarify more fully here.

[ shrug... ] I'm inclined to go back to the "command message" wording
then. I don't find "client-issued statement" to be helpful at all;
in particular, it's flat wrong for the multi-statement-query-message
case, because surely all those statements are client-issued. I'm okay
with this text leaving out nitpicky details, but it should leave the
reader with a mental model that more or less matches reality.

Then let’s use command message. It basically the moral equivalent to my
(technically, query) parenthetical.

David J.

On Mon, 2025-07-14 at 13:09 -0400, Tom Lane wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Ok. That explains why "statement_timestamp() and transaction_timestamp()
return the same value during the first command of a transaction," isn't
just stating the obvious. transaction_timestamp() literally returns the
value statement_timestamp().

Right. I'm tempted to be more explicit about that, along the lines of

<function>statement_timestamp()</function> and <function>transaction_timestamp()</function>
-    return the same value during the first statement of a transaction, but might
-    differ during subsequent statements.
+    return the same value during the first statement of a transaction,
+    because the transaction timestamp is set by copying the statement
+    timestamp when a new transaction starts.

I didn't include that change below though; perhaps it has too much
whiff of implementation detail.

I'm fine with this entire section assuming/stating that extended protocol
is in effect and that 53.2.2.1 explains how these behave when executing a
multi-statement simple protocol "script".

It's incorrect to claim that this only applies to extended protocol,
and besides I thought you didn't want to mention protocol details
here. I can't argue with documenting it in the protocol chapter
though. So about like this?

+1

This thread doesn't look like we're going to find a wording that will
make everyone happy, but I believe that this patch is a clear improvement.

Quite contrary to David, I would have liked the word "protocol" with
the message, because it gives me personally the right idea, but I am
fine with "message" alone. After all, a message in PostgreSQL jargon
is a protocol message.

Yours,
Laurenz Albe