Add example to sort results by two composite types
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/17/rowtypes.html Description: The doc has example: All of these ORDER BY clauses specify the row's composite value, resulting in sorting the rows according to the rules described in Section 9.25.6. However, if inventory_item contained a column named c, the first case would be different from the others, as it would mean to sort by that column only. Given the column names previously shown, these queries are also equivalent to those above: SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price); SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price); I suggest to extend the example to sort results by first composite type and then by the second: SELECT * FROM invertory_type t, inventory_item c ORDER BY (c.name, c.supplier_id, c.price), (t.name, t.value); Though I did not checked this if it possible or not. It would be nice to have it documented. Thank you.
Re: please define 'statement' in the glossary
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 +|
-- 2|2025-07-10 10:50:55.424 +|
-- 3|2025-07-10 10:50:55.424 +|
--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 +|
-- 2|2025-07-10 10:58:25.413 +|
-- 3|2025-07-10 10:58:28.429 +|
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
wrote:
> On Sun, 2025-07-13 at 17:32 -0700, David G. Johnston wrote:
> > On Sun, Jul 13, 2025 at 2:57 PM Tom Lane 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 t
Re: please define 'statement' in the glossary
Laurenz Albe 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
Re: please define 'statement' in the glossary
"David G. Johnston" 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
Re: please define 'statement' in the glossary
"David G. Johnston" 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
Re: please define 'statement' in the glossary
"David G. Johnston" 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 statement_timestamp() and transaction_timestamp() -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 diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 6b327d4fd81..5818457bf48 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11245,12 +11245,12 @@ now() what it returns. statement_timestamp() 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). statement_timestamp() and transaction_timestamp() -return the same value during the first command of a transaction, but might -differ during subsequent commands. +return the same value during the first statement of a transaction, but might +differ during subsequent statements. clock_timestamp() returns the actual current time, and -therefore its value changes even within a single SQL command. +therefore its value changes even within a single SQL statement. timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual current time, diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 4cfd9767f7c..f14bcdfcaeb 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -886,6 +886,16 @@ SELCT 1/0; Errors detected at semantic analysis or later, such as a misspelled table or column name, do not have this effect. + + + Lastly, note that all the statements within the Query message will + observe the same value of statement_timestamp(), + since that timestamp is updated only upon receipt of the Query + message. This will typically result in them all observing the same + value of transaction_timestamp() as well, + except in the case where the query string ends a previously-started + transaction and begins a new one. +
Re: please define 'statement' in the glossary
On Mon, Jul 14, 2025 at 9:08 AM Tom Lane wrote: > "David G. Johnston" 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.
Re: Add example to sort results by two composite types
On Sunday, July 13, 2025, PG Doc comments form wrote: > > I suggest to extend the example to sort results by first composite type and > then by the second: > SELECT * FROM invertory_type t, inventory_item c ORDER BY (c.name, > c.supplier_id, c.price), (t.name, t.value); > > Though I did not checked this if it possible or not. It would be nice to > have it documented. > What new information does that add or uncertainty does it clarify? This isn’t a section teaching order by - the reader is expected to already understand that an order by clause can accept two column names and how those are treated (i.e., ties among the first are broken by the second). This is a section about how within that structure the comparisons happen. A single column suffices to make that explanation. David J.
Re: please define 'statement' in the glossary
On Monday, July 14, 2025, Tom Lane wrote: > "David G. Johnston" 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.
Re: please define 'statement' in the glossary
"David G. Johnston" 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
Re: please define 'statement' in the glossary
On Mon, Jul 14, 2025 at 8:45 AM David G. Johnston < david.g.johns...@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.
Re: please define 'statement' in the glossary
On Mon, 2025-07-14 at 13:09 -0400, Tom Lane wrote: > "David G. Johnston" 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 > > statement_timestamp() and > transaction_timestamp() > -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
Re: Clarify VACUUM FULL exclusion in total_vacuum_time docs
On Tue, 2025-07-15 at 01:51 +0900, Fujii Masao wrote: > > On 2025/06/18 6:53, Robert Treat wrote: > > I think the more cases where you document this behavior (and I do like > > the idea of documenting it for total_vacuum_time), the more one is > > likely to think that places where it is not documented operate > > differently. To that end, I think documenting it for > > n_ins_since_vacuum as well is a good idea, but I don't feel strongly > > that it needs to be backpatched; the old documentation wasn't wrong > > per se, rather this is a documentation improvement as a result of new > > development. > > Agreed. The attached patch updates the docs to clarify that both > total_vacuum_time and n_ins_since_vacuum exclude VACUUM FULL. > > Unless there are any objections, I'll commit this to master and > back-patch it to v18 only. I think the patch is good. One question for me is whether we should use "VACUUM (FULL)" rather than "VACUUM FULL". On the one hand, the documentation (and most users) still use the old syntax without parentheses almost everywhere. On the other hand, reading the VACUUM reference page, I get the feeling that the new syntax with parentheses should be favored. After all, the old syntax doesn't support any of the recently added options and restricts the option order. So perhaps we should start propagating the parentheses more, and the documentation is the perfect place to do that. Yours, Laurenz Albe
Re: please define 'statement' in the glossary
On Mon, Jul 14, 2025 at 8:17 AM Tom Lane wrote: > Laurenz Albe 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.
Re: please define 'statement' in the glossary
On Mon, Jul 14, 2025 at 10:09 AM Tom Lane wrote: > "David G. Johnston" 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 > > statement_timestamp() and > transaction_timestamp() > -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: statement_timestamp() 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: statement_timestamp() returns the start time of the current client-issued statement (technically, query). David J.
Re: Clarify VACUUM FULL exclusion in total_vacuum_time docs
On 2025/06/18 6:53, Robert Treat wrote: I think the more cases where you document this behavior (and I do like the idea of documenting it for total_vacuum_time), the more one is likely to think that places where it is not documented operate differently. To that end, I think documenting it for n_ins_since_vacuum as well is a good idea, but I don't feel strongly that it needs to be backpatched; the old documentation wasn't wrong per se, rather this is a documentation improvement as a result of new development. Agreed. The attached patch updates the docs to clarify that both total_vacuum_time and n_ins_since_vacuum exclude VACUUM FULL. Unless there are any objections, I'll commit this to master and back-patch it to v18 only. Regards, -- Fujii Masao NTT DATA Japan Corporation From b1e64ee62915b883369a46e6add417acde15da88 Mon Sep 17 00:00:00 2001 From: Fujii Masao Date: Tue, 15 Jul 2025 01:21:24 +0900 Subject: [PATCH v1] doc: Clarify that total_vacuum_time excludes VACUUM FULL. The last_vacuum and vacuum_count fields in pg_stat_all_tables already state that they do not include VACUUM FULL. However, total_vacuum_time, which also excludes VACUUM FULL, did not mention this. This could mislead users into thinking VACUUM FULL time is included. To address this, this commit updates the documentation for pg_stat_all_tables to explicitly state that total_vacuum_time does not count VACUUM FULL. Back-patched to v18, where total_vacuum_time was introduced. Additionally, this commit clarifies that n_ins_since_vacuum also excludes VACUUM FULL. Although n_ins_since_vacuum was added in v13, we are not back-patching this change to stable branches, as it is a documentation improvement, not a bug fix. Author: Fujii Masao Reviewed-by: David G. Johnston Reviewed-by: Robert Treat Discussion: https://postgr.es/m/2ac375d1-591b-4f1b-a2af-f24335567...@oss.nttdata.com Backpatch-through: 18 --- doc/src/sgml/monitoring.sgml | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 4265a22d4de..823afe1b30b 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -3980,6 +3980,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage Estimated number of rows inserted since this table was last vacuumed + (not counting VACUUM FULL) @@ -4066,7 +4067,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage total_vacuum_time double precision - Total time this table has been manually vacuumed, in milliseconds. + Total time this table has been manually vacuumed, in milliseconds + (not counting VACUUM FULL). (This includes the time spent sleeping due to cost-based delays.) -- 2.49.0
