public inbox for [email protected]help / color / mirror / Atom feed
[16Beta1][doc] pgstat: Track time of the last scan of a relation 5+ messages / 3 participants [nested] [flat]
* [16Beta1][doc] pgstat: Track time of the last scan of a relation @ 2023-05-31 03:56 Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> 2023-05-31 06:13 ` Re: [16Beta1][doc] pgstat: Track time of the last scan of a relation David Rowley <[email protected]> 2023-06-05 05:36 ` Re: [16Beta1][doc] pgstat: Track time of the last scan of a relation David Rowley <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Shinoda, Noriyoshi (PN Japan FSIP) @ 2023-05-31 03:56 UTC (permalink / raw) To: pgsql-hackers; +Cc: [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]> Hi, hackers. PostgreSQL 16 Beta1, added last access time to pg_stat_all_tables and pg_stat_all_indexes views by this patch [1]. According to the documentation [2], the data type of the columns added to these views is 'timestamptz'. However, columns of the same data type in pg_stat_all_tables.last_vacuum, last_analyze and other tables are unified to 'timestamp with time zone'. The attached patch changes the data type of the added column from timestamptz to timestamp with time zone. [1] pgstat: Track time of the last scan of a relation https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c037471832e1ec3327f81eebbd8892e5c1042... [2] pg_stat_activity view https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW Regards, Noriyoshi Shinoda Attachments: [application/octet-stream] pg_stat_all_tables_doc_v1.diff (1.5K, ../../DM4PR84MB17348EA11FA90A9BE896AF89EE489@DM4PR84MB1734.NAMPRD84.PROD.OUTLOOK.COM/2-pg_stat_all_tables_doc_v1.diff) download | inline diff: diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 40ad419194..5cfdc70c03 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4863,7 +4863,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <row> <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>last_seq_scan</structfield> <type>timestamptz</type> + <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type> </para> <para> The time of the last sequential scan on this table, based on the @@ -4891,7 +4891,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <row> <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>last_idx_scan</structfield> <type>timestamptz</type> + <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type> </para> <para> The time of the last index scan on this table, based on the @@ -5170,7 +5170,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <row> <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>last_idx_scan</structfield> <type>timestamptz</type> + <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type> </para> <para> The time of the last scan on this index, based on the ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [16Beta1][doc] pgstat: Track time of the last scan of a relation 2023-05-31 03:56 [16Beta1][doc] pgstat: Track time of the last scan of a relation Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> @ 2023-05-31 06:13 ` David Rowley <[email protected]> 2023-06-01 09:38 ` RE: [16Beta1][doc] pgstat: Track time of the last scan of a relation Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: David Rowley @ 2023-05-31 06:13 UTC (permalink / raw) To: Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]> On Wed, 31 May 2023 at 15:57, Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> wrote: > According to the documentation [2], the data type of the columns added to these views is 'timestamptz'. > However, columns of the same data type in pg_stat_all_tables.last_vacuum, last_analyze and other tables are unified to 'timestamp with time zone'. The attached patch changes the data type of the added column from timestamptz to timestamp with time zone. I agree that it would be good to make those consistently use timestamp with time zone for all columns of that type in the docs for pg_stat_all_tables. More generally, it might be good if we did it for the entire docs: doc $ git grep "<type>timestamptz</type>" | wc -l 17 doc $ git grep "<type>timestamp with time zone</type>" | wc -l 74 Clearly "timestamp with time zone" is much more commonly used. The bar is probably set a bit higher for changing the longer-established ones, however. David ^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [16Beta1][doc] pgstat: Track time of the last scan of a relation 2023-05-31 03:56 [16Beta1][doc] pgstat: Track time of the last scan of a relation Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> 2023-05-31 06:13 ` Re: [16Beta1][doc] pgstat: Track time of the last scan of a relation David Rowley <[email protected]> @ 2023-06-01 09:38 ` Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Shinoda, Noriyoshi (PN Japan FSIP) @ 2023-06-01 09:38 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]> Hi, Thanks for your comment. As you say, it would be difficult to unify the data types in all documents right now. The patch I attached the other day unifies only the newly added columns in monitoring.sgml to "timestamp with time zone". Regards, Noriyoshi Shinoda -----Original Message----- From: David Rowley <[email protected]> Sent: Wednesday, May 31, 2023 3:14 PM To: Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> Cc: PostgreSQL-development <[email protected]>; [email protected]; [email protected]; [email protected]; [email protected] Subject: Re: [16Beta1][doc] pgstat: Track time of the last scan of a relation On Wed, 31 May 2023 at 15:57, Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> wrote: > According to the documentation [2], the data type of the columns added to these views is 'timestamptz'. > However, columns of the same data type in pg_stat_all_tables.last_vacuum, last_analyze and other tables are unified to 'timestamp with time zone'. The attached patch changes the data type of the added column from timestamptz to timestamp with time zone. I agree that it would be good to make those consistently use timestamp with time zone for all columns of that type in the docs for pg_stat_all_tables. More generally, it might be good if we did it for the entire docs: doc $ git grep "<type>timestamptz</type>" | wc -l 17 doc $ git grep "<type>timestamp with time zone</type>" | wc -l 74 Clearly "timestamp with time zone" is much more commonly used. The bar is probably set a bit higher for changing the longer-established ones, however. David ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [16Beta1][doc] pgstat: Track time of the last scan of a relation 2023-05-31 03:56 [16Beta1][doc] pgstat: Track time of the last scan of a relation Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> @ 2023-06-05 05:36 ` David Rowley <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: David Rowley @ 2023-06-05 05:36 UTC (permalink / raw) To: Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]> On Wed, 31 May 2023 at 15:57, Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> wrote: > PostgreSQL 16 Beta1, added last access time to pg_stat_all_tables and pg_stat_all_indexes views by this patch [1]. > According to the documentation [2], the data type of the columns added to these views is 'timestamptz'. > However, columns of the same data type in pg_stat_all_tables.last_vacuum, last_analyze and other tables are unified to 'timestamp with time zone'. The attached patch changes the data type of the added column from timestamptz to timestamp with time zone. I've now pushed this change. David ^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH v14 6/8] Row pattern recognition patch (docs). @ 2024-02-28 13:59 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Tatsuo Ishii @ 2024-02-28 13:59 UTC (permalink / raw) --- doc/src/sgml/advanced.sgml | 80 ++++++++++++++++++++++++++++++++++++ doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++ doc/src/sgml/ref/select.sgml | 38 ++++++++++++++++- 3 files changed, 170 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 755c9f1485..cf18dd887e 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -537,6 +537,86 @@ WHERE pos < 3; <literal>rank</literal> less than 3. </para> + <para> + Row pattern common syntax can be used to perform row pattern recognition + in a query. Row pattern common syntax includes two sub + clauses: <literal>DEFINE</literal> + and <literal>PATTERN</literal>. <literal>DEFINE</literal> defines + definition variables along with an expression. The expression must be a + logical expression, which means it must + return <literal>TRUE</literal>, <literal>FALSE</literal> + or <literal>NULL</literal>. The expression may comprise column references + and functions. Window functions, aggregate functions and subqueries are + not allowed. An example of <literal>DEFINE</literal> is as follows. + +<programlisting> +DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +</programlisting> + + Note that <function>PREV</function> returns the price column in the + previous row if it's called in a context of row pattern recognition. So in + the second line the definition variable "UP" is <literal>TRUE</literal> + when the price column in the current row is greater than the price column + in the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when + the price column in the current row is lower than the price column in the + previous row. + </para> + <para> + Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be + used. <literal>PATTERN</literal> defines a sequence of rows that satisfies + certain conditions. For example following <literal>PATTERN</literal> + defines that a row starts with the condition "LOWPRICE", then one or more + rows satisfy "UP" and finally one or more rows satisfy "DOWN". Note that + "+" means one or more matches. Also you can use "*", which means zero or + more matches. If a sequence of rows which satisfies the PATTERN is found, + in the starting row of the sequence of rows all window functions and + aggregates are shown in the target list. Note that aggregations only look + into the matched rows, rather than whole frame. In the second or + subsequent rows all window functions and aggregates are NULL. For rows + that do not match the PATTERN, all window functions and aggregates are + shown AS NULL too, except count which shows 0. This is because the + unmatched rows are in an empty frame. Example of + a <literal>SELECT</literal> using the <literal>DEFINE</literal> + and <literal>PATTERN</literal> clause is as follows. + +<programlisting> +SELECT company, tdate, price, + first_value(price) OVER w, + max(price) OVER w, + count(price) OVER w +FROM stock, + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +</programlisting> +<screen> + company | tdate | price | first_value | max | count +----------+------------+-------+-------------+-----+------- + company1 | 2023-07-01 | 100 | 100 | 200 | 4 + company1 | 2023-07-02 | 200 | | | + company1 | 2023-07-03 | 150 | | | + company1 | 2023-07-04 | 140 | | | + company1 | 2023-07-05 | 150 | | | 0 + company1 | 2023-07-06 | 90 | 90 | 130 | 4 + company1 | 2023-07-07 | 110 | | | + company1 | 2023-07-08 | 130 | | | + company1 | 2023-07-09 | 120 | | | + company1 | 2023-07-10 | 130 | | | 0 +</screen> + </para> + <para> When a query involves multiple window functions, it is possible to write out each one with a separate <literal>OVER</literal> clause, but this is diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index e5fa82c161..8dd97501a7 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -22364,6 +22364,7 @@ SELECT count(*) FROM sometable; returns <literal>NULL</literal> if there is no such row. </para></entry> </row> + </tbody> </tgroup> </table> @@ -22403,6 +22404,59 @@ SELECT count(*) FROM sometable; Other frame specifications can be used to obtain other effects. </para> + <para> + Row pattern recognition navigation functions are listed in + <xref linkend="functions-rpr-navigation-table"/>. These functions + can be used to describe DEFINE clause of Row pattern recognition. + </para> + + <table id="functions-rpr-navigation-table"> + <title>Row Pattern Navigation Functions</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="func_table_entry"><para role="func_signature"> + Function + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>prev</primary> + </indexterm> + <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> ) + <returnvalue>anyelement</returnvalue> + </para> + <para> + Returns the column value at the previous row; + returns NULL if there is no previous row in the window frame. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>next</primary> + </indexterm> + <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> ) + <returnvalue>anyelement</returnvalue> + </para> + <para> + Returns the column value at the next row; + returns NULL if there is no next row in the window frame. + </para></entry> + </row> + + </tbody> + </tgroup> + </table> + <note> <para> The SQL standard defines a <literal>RESPECT NULLS</literal> or diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 9917df7839..1575fc2167 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -969,8 +969,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl The <replaceable class="parameter">frame_clause</replaceable> can be one of <synopsis> -{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] -{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] +{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax] +{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax] </synopsis> where <replaceable>frame_start</replaceable> @@ -1077,6 +1077,40 @@ EXCLUDE NO OTHERS a given peer group will be in the frame or excluded from it. </para> + <para> + The + optional <replaceable class="parameter">row_pattern_common_syntax</replaceable> + defines the <firstterm>row pattern recognition condition</firstterm> for + this + window. <replaceable class="parameter">row_pattern_common_syntax</replaceable> + includes following subclauses. <literal>AFTER MATCH SKIP PAST LAST + ROW</literal> or <literal>AFTER MATCH SKIP TO NEXT ROW</literal> controls + how to proceed to next row position after a match + found. With <literal>AFTER MATCH SKIP PAST LAST ROW</literal> (the + default) next row position is next to the last row of previous match. On + the other hand, with <literal>AFTER MATCH SKIP TO NEXT ROW</literal> next + row position is always next to the last row of previous + match. <literal>DEFINE</literal> defines definition variables along with a + boolean expression. <literal>PATTERN</literal> defines a sequence of rows + that satisfies certain conditions using variables defined + in <literal>DEFINE</literal> clause. If the variable is not defined in + the <literal>DEFINE</literal> clause, it is implicitly assumed + following is defined in the <literal>DEFINE</literal> clause. + +<synopsis> +<literal>variable_name</literal> AS TRUE +</synopsis> + + Note that the maximu number of variables defined + in <literal>DEFINE</literal> clause is 26. + +<synopsis> +[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ] +PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...] +DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...] +</synopsis> + </para> + <para> The purpose of a <literal>WINDOW</literal> clause is to specify the behavior of <firstterm>window functions</firstterm> appearing in the query's -- 2.25.1 ----Next_Part(Thu_Feb_29_09_19_54_2024_640)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v14-0007-Row-pattern-recognition-patch-tests.patch" ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-02-28 13:59 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-05-31 03:56 [16Beta1][doc] pgstat: Track time of the last scan of a relation Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> 2023-05-31 06:13 ` David Rowley <[email protected]> 2023-06-01 09:38 ` Shinoda, Noriyoshi (PN Japan FSIP) <[email protected]> 2023-06-05 05:36 ` David Rowley <[email protected]> 2024-02-28 13:59 [PATCH v14 6/8] Row pattern recognition patch (docs). Tatsuo Ishii <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox