public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 5/6] Documentation for slot-limit feature 6+ messages / 3 participants [nested] [flat]
* [PATCH 5/6] Documentation for slot-limit feature @ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Kyotaro Horiguchi @ 2018-01-11 06:00 UTC (permalink / raw) --- doc/src/sgml/catalogs.sgml | 30 ++++++++++++++++++++++++++++++ doc/src/sgml/config.sgml | 23 +++++++++++++++++++++++ doc/src/sgml/high-availability.sgml | 8 +++++--- 3 files changed, 58 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 68ad5071ca..dc9679283a 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9975,6 +9975,36 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </entry> </row> + <row> + <entry><structfield>wal_status</structfield></entry> + <entry><type>text</type></entry> + <entry></entry> + + <entry>Availability of WAL records claimed by this + slot. <literal>streaming</literal>, <literal>keeping</literal>, + <literal>lost</literal> + or <literal>unknown</literal>. <literal>streaming</literal> means that + the claimed records are available within + max_wal_size. <literal>keeping</literal> means max_wal_size is exceeded + but still required records are held by replication slots or + wal_keep_segments. + <literal>lost</literal> means that some of them are on the verge of + removal or no longer available. This state is seen only when + <xref linkend="guc-max-slot-wal-keep-size"/> is + non-negative. If <structfield>restart_lsn</structfield> is NULL, this + field is <literal>unknown</literal>. + </entry> + </row> + + <row> + <entry><structfield>remain</structfield></entry> + <entry><type>bigint</type></entry> + <entry></entry> + <entry>The amount in bytes that WAL location (LSN) can advance until + this slot may lose required WAL records. + </entry> + </row> + </tbody> </tgroup> </table> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index c91e3e1550..c345538c8f 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3650,6 +3650,29 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows </listitem> </varlistentry> + <varlistentry id="guc-max-slot-wal-keep-size" xreflabel="max_slot_wal_keep_size"> + <term><varname>max_slot_wal_keep_size</varname> (<type>integer</type>) + <indexterm> + <primary><varname>max_slot_wal_keep_size</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Specify the maximum size of WAL files + that <link linkend="streaming-replication-slots">replication + slots</link> are allowed to retain in the <filename>pg_wal</filename> + directory at checkpoint time. + If <varname>max_slot_wal_keep_size</varname> is -1 (the default), + replication slots retain unlimited amount of WAL files. If + restart_lsn of a replication slot gets behind more than that megabytes + from the current LSN, the standby using the slot may no longer be able + to continue replication due to removal of required WAL records. You + can see the WAL availability of replication slots + in <link linkend="view-pg-replication-slots">pg_replication_slots</link>. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-wal-sender-timeout" xreflabel="wal_sender_timeout"> <term><varname>wal_sender_timeout</varname> (<type>integer</type>) <indexterm> diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 543691dad4..ae8c3a2aca 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -925,9 +925,11 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' <xref linkend="guc-archive-command"/>. However, these methods often result in retaining more WAL segments than required, whereas replication slots retain only the number of segments - known to be needed. An advantage of these methods is that they bound - the space requirement for <literal>pg_wal</literal>; there is currently no way - to do this using replication slots. + known to be needed. On the other hand, replication slots can retain so + many WAL segments that they fill up the space allocated + for <literal>pg_wal</literal>; + <xref linkend="guc-max-slot-wal-keep-size"/> limits the size of WAL files + retained by replication slots. </para> <para> Similarly, <xref linkend="guc-hot-standby-feedback"/> -- 2.16.3 ----Next_Part(Tue_Jul_30_21_30_45_2019_680)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v15-0006-Check-removal-of-in-reading-segment-file.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH 5/6] Documentation for slot-limit feature @ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Kyotaro Horiguchi @ 2018-01-11 06:00 UTC (permalink / raw) --- doc/src/sgml/catalogs.sgml | 37 +++++++++++++++++++++++++++++++++++++ doc/src/sgml/config.sgml | 23 +++++++++++++++++++++++ doc/src/sgml/high-availability.sgml | 8 +++++--- 3 files changed, 65 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 68ad5071ca..3605e34149 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9975,6 +9975,43 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </entry> </row> + <row> + <entry><structfield>wal_status</structfield></entry> + <entry><type>text</type></entry> + <entry></entry> + + <entry>Availability of WAL records claimed by this + slot. <literal>streaming</literal>, <literal>keeping</literal>, + <literal>losing</literal> or <literal>lost</literal>. + <literal>streaming</literal> means that the claimed records are + available within max_wal_size. <literal>keeping</literal> means + max_wal_size is exceeded but still held by replication slots or + wal_keep_segments. + <literal>losing</literal> means that some of them are on the verge of + removal but the using session may go furether. + <literal>lost</literal> means that some of them are definitely lost and + the session that used this slot cannot continue replication. This state + also implies the using session has been stopped. + + The last two states are seen only when + <xref linkend="guc-max-slot-wal-keep-size"/> is + non-negative. If <structfield>restart_lsn</structfield> is NULL, this + field is null. + </entry> + </row> + + <row> + <entry><structfield>remain</structfield></entry> + <entry><type>bigint</type></entry> + <entry></entry> + <entry>The amount in bytes WAL location (LSN) can advance that bytes + until this slot may lose required WAL + records. If <structfield>restart_lsn</structfield> is null + or <structfield>wal_status</structfield> is <literal>losing</literal> + or <literal>lost</literal>, this field is null. + </entry> + </row> + </tbody> </tgroup> </table> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index c91e3e1550..c345538c8f 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3650,6 +3650,29 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows </listitem> </varlistentry> + <varlistentry id="guc-max-slot-wal-keep-size" xreflabel="max_slot_wal_keep_size"> + <term><varname>max_slot_wal_keep_size</varname> (<type>integer</type>) + <indexterm> + <primary><varname>max_slot_wal_keep_size</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Specify the maximum size of WAL files + that <link linkend="streaming-replication-slots">replication + slots</link> are allowed to retain in the <filename>pg_wal</filename> + directory at checkpoint time. + If <varname>max_slot_wal_keep_size</varname> is -1 (the default), + replication slots retain unlimited amount of WAL files. If + restart_lsn of a replication slot gets behind more than that megabytes + from the current LSN, the standby using the slot may no longer be able + to continue replication due to removal of required WAL records. You + can see the WAL availability of replication slots + in <link linkend="view-pg-replication-slots">pg_replication_slots</link>. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-wal-sender-timeout" xreflabel="wal_sender_timeout"> <term><varname>wal_sender_timeout</varname> (<type>integer</type>) <indexterm> diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 543691dad4..ae8c3a2aca 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -925,9 +925,11 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' <xref linkend="guc-archive-command"/>. However, these methods often result in retaining more WAL segments than required, whereas replication slots retain only the number of segments - known to be needed. An advantage of these methods is that they bound - the space requirement for <literal>pg_wal</literal>; there is currently no way - to do this using replication slots. + known to be needed. On the other hand, replication slots can retain so + many WAL segments that they fill up the space allocated + for <literal>pg_wal</literal>; + <xref linkend="guc-max-slot-wal-keep-size"/> limits the size of WAL files + retained by replication slots. </para> <para> Similarly, <xref linkend="guc-hot-standby-feedback"/> -- 2.16.3 ----Next_Part(Wed_Jul_31_16_56_16_2019_668)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v16-0006-Check-removal-of-in-reading-segment-file.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH 5/6] Documentation for slot-limit feature @ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Kyotaro Horiguchi @ 2018-01-11 06:00 UTC (permalink / raw) --- doc/src/sgml/catalogs.sgml | 28 ++++++++++++++++++++++++++++ doc/src/sgml/config.sgml | 23 +++++++++++++++++++++++ doc/src/sgml/high-availability.sgml | 8 +++++--- 3 files changed, 56 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index f2b9d404cb..acfc80f921 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9975,6 +9975,34 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </entry> </row> + <row> + <entry><structfield>wal_status</structfield></entry> + <entry><type>text</type></entry> + <entry></entry> + + <entry>Availability of WAL records claimed by this + slot. <literal>streaming</literal>, <literal>keeping</literal>, + <literal>lost</literal> + or <literal>unknown</literal>. <literal>streaming</literal> means that + the claimed records are available. <literal>keeping</literal> means that + some of them are to be removed by the next checkpoint. + <literal>lost</literal> means that some of them are no longer + available. The last two states are seen only when + <xref linkend="guc-max-slot-wal-keep-size"/> is + non-negative. If <structfield>restart_lsn</structfield> is NULL, this + field is <literal>unknown</literal>. + </entry> + </row> + + <row> + <entry><structfield>remain</structfield></entry> + <entry><type>bigint</type></entry> + <entry></entry> + <entry>The amount in bytes that WAL location (LSN) can advance until + this slot may lose required WAL records. + </entry> + </row> + </tbody> </tgroup> </table> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 84341a30e5..f203d42921 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3650,6 +3650,29 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows </listitem> </varlistentry> + <varlistentry id="guc-max-slot-wal-keep-size" xreflabel="max_slot_wal_keep_size"> + <term><varname>max_slot_wal_keep_size</varname> (<type>integer</type>) + <indexterm> + <primary><varname>max_slot_wal_keep_size</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Specify the maximum size of WAL files + that <link linkend="streaming-replication-slots">replication + slots</link> are allowed to retain in the <filename>pg_wal</filename> + directory at checkpoint time. + If <varname>max_slot_wal_keep_size</varname> is -1 (the default), + replication slots retain unlimited size of WAL files. If restart_lsn + of a replication slot gets behind more than that bytes from the + current LSN, the standby using the slot may no longer be able to + reconnect due to removal of required WAL records. You can see the WAL + availability of replication slots + in <link linkend="view-pg-replication-slots">pg_replication_slots</link>. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-wal-sender-timeout" xreflabel="wal_sender_timeout"> <term><varname>wal_sender_timeout</varname> (<type>integer</type>) <indexterm> diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 543691dad4..ae8c3a2aca 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -925,9 +925,11 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' <xref linkend="guc-archive-command"/>. However, these methods often result in retaining more WAL segments than required, whereas replication slots retain only the number of segments - known to be needed. An advantage of these methods is that they bound - the space requirement for <literal>pg_wal</literal>; there is currently no way - to do this using replication slots. + known to be needed. On the other hand, replication slots can retain so + many WAL segments that they fill up the space allocated + for <literal>pg_wal</literal>; + <xref linkend="guc-max-slot-wal-keep-size"/> limits the size of WAL files + retained by replication slots. </para> <para> Similarly, <xref linkend="guc-hot-standby-feedback"/> -- 2.20.1 --MP_/68vU=6Ib.yoYOTdA.M=EVne Content-Type: text/x-patch Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename=v14-0006-Check-removal-of-in-reading-segment-file.patch ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH 5/6] Documentation for slot-limit feature @ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Kyotaro Horiguchi @ 2018-01-11 06:00 UTC (permalink / raw) --- doc/src/sgml/catalogs.sgml | 28 ++++++++++++++++++++++++++++ doc/src/sgml/config.sgml | 23 +++++++++++++++++++++++ doc/src/sgml/high-availability.sgml | 8 +++++--- 3 files changed, 56 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0fd792ff1a..ad5931800d 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9865,6 +9865,34 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </entry> </row> + <row> + <entry><structfield>wal_status</structfield></entry> + <entry><type>text</type></entry> + <entry></entry> + + <entry>Availability of WAL records claimed by this + slot. <literal>streaming</literal>, <literal>keeping</literal>, + <literal>lost</literal> + or <literal>unknown</literal>. <literal>streaming</literal> means that + the claimed records are available. <literal>keeping</literal> means that + some of them are to be removed by the next checkpoint. + <literal>lost</literal> means that some of them are no longer + available. The last two states are seen only when + <xref linkend="guc-max-slot-wal-keep-size"/> is + non-negative. If <structfield>restart_lsn</structfield> is NULL, this + field is <literal>unknown</literal>. + </entry> + </row> + + <row> + <entry><structfield>remain</structfield></entry> + <entry><type>bigint</type></entry> + <entry></entry> + <entry>The amount in bytes that WAL location (LSN) can advance until + this slot may lose required WAL records. + </entry> + </row> + </tbody> </tgroup> </table> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 8bd57f376b..59b5c1b03a 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3573,6 +3573,29 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows </listitem> </varlistentry> + <varlistentry id="guc-max-slot-wal-keep-size" xreflabel="max_slot_wal_keep_size"> + <term><varname>max_slot_wal_keep_size</varname> (<type>integer</type>) + <indexterm> + <primary><varname>max_slot_wal_keep_size</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Specify the maximum size of WAL files + that <link linkend="streaming-replication-slots">replication + slots</link> are allowed to retain in the <filename>pg_wal</filename> + directory at checkpoint time. + If <varname>max_slot_wal_keep_size</varname> is -1 (the default), + replication slots retain unlimited size of WAL files. If restart_lsn + of a replication slot gets behind more than that bytes from the + current LSN, the standby using the slot may no longer be able to + reconnect due to removal of required WAL records. You can see the WAL + availability of replication slots + in <link linkend="view-pg-replication-slots">pg_replication_slots</link>. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-wal-sender-timeout" xreflabel="wal_sender_timeout"> <term><varname>wal_sender_timeout</varname> (<type>integer</type>) <indexterm> diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 2b4dcd03c8..d52ffd97a3 100644 --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml @@ -925,9 +925,11 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' <xref linkend="guc-archive-command"/>. However, these methods often result in retaining more WAL segments than required, whereas replication slots retain only the number of segments - known to be needed. An advantage of these methods is that they bound - the space requirement for <literal>pg_wal</literal>; there is currently no way - to do this using replication slots. + known to be needed. On the other hand, replication slots can retain so + many WAL segments that they fill up the space allocated + for <literal>pg_wal</literal>; + <xref linkend="guc-max-slot-wal-keep-size"/> limits the size of WAL files + retained by replication slots. </para> <para> Similarly, <xref linkend="guc-hot-standby-feedback"/> -- 2.16.3 ----Next_Part(Fri_Feb_22_14_12_28_2019_076)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v13-0006-Check-removal-of-in-reading-segment-file.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v4 5/7] Row pattern recognition patch (docs). @ 2023-08-09 07:56 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Tatsuo Ishii @ 2023-08-09 07:56 UTC (permalink / raw) --- doc/src/sgml/advanced.sgml | 52 ++++++++++++++++++++++++++++++++++ doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/select.sgml | 38 +++++++++++++++++++++++-- 3 files changed, 142 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 755c9f1485..eda3612822 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -537,6 +537,58 @@ WHERE pos < 3; <literal>rank</literal> less than 3. </para> + <para> + Row pattern common syntax can be used with row pattern common syntax to + perform row pattern recognition in a query. Row pattern common syntax + includes two sub clauses. <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>. Moreover if the expression comprises a column + reference, it must be the argument of <function>rpr</function>. 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 price column in the previous + row if it's called in a context of row pattern recognition. So in the + second line means the definition variable "UP" is <literal>TRUE</literal> + when price column in the current row is greater than the price column in + the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when + 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". If a + sequence of rows found, rpr returns the column at the starting row. + Example of a <literal>SELECT</literal> using the <literal>DEFINE</literal> + and <literal>PATTERN</literal> clause is as follows. + +<programlisting> +SELECT company, tdate, price, max(price) OVER w FROM stock + WINDOW w AS ( + PARTITION BY company + 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> + </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 be2f54c914..6cda164522 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -21715,6 +21715,7 @@ SELECT count(*) FROM sometable; returns <literal>NULL</literal> if there is no such row. </para></entry> </row> + </tbody> </tgroup> </table> @@ -21754,6 +21755,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 0ee0cc7e64..8d3becd57a 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -966,8 +966,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> @@ -1074,6 +1074,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(Wed_Aug__9_17_41_12_2023_134)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v4-0006-Row-pattern-recognition-patch-tests.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Have pg_basebackup write "dbname" in "primary_conninfo"? @ 2024-03-13 16:04 vignesh C <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: vignesh C @ 2024-03-13 16:04 UTC (permalink / raw) To: Amit Kapila <[email protected]>; +Cc: Hayato Kuroda (Fujitsu) <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>; pgsql-hackers; Kyotaro Horiguchi <[email protected]> On Wed, 13 Mar 2024 at 16:58, Amit Kapila <[email protected]> wrote: > > On Tue, Mar 12, 2024 at 5:13 PM vignesh C <[email protected]> wrote: > > > > On Mon, 11 Mar 2024 at 17:16, Amit Kapila <[email protected]> wrote: > > > > > > On Tue, Feb 27, 2024 at 2:07 PM Hayato Kuroda (Fujitsu) > > > <[email protected]> wrote: > > > > > > > > > PSA the patch for implementing it. It is basically same as Ian's one. > > > > > However, this patch still cannot satisfy the condition 3). > > > > > > > > > > `pg_basebackup -D data_N2 -d "user=postgres" -R` > > > > > -> dbname would not be appeared in primary_conninfo. > > > > > > > > > > This is because `if (connection_string)` case in GetConnection() explicy override > > > > > a dbname to "replication". I've tried to add a dummy entry {"dbname", NULL} pair > > > > > before the overriding, but it is no-op. Because The replacement of the dbname in > > > > > pqConnectOptions2() would be done only for the valid (=lastly specified) > > > > > connection options. > > > > > > > > Oh, this patch missed the straightforward case: > > > > > > > > pg_basebackup -D data_N2 -d "user=postgres dbname=replication" -R > > > > -> dbname would not be appeared in primary_conninfo. > > > > > > > > So I think it cannot be applied as-is. Sorry for sharing the bad item. > > > > > > > > > > Can you please share the patch that can be considered for review? > > > > Here is a patch with few changes: a) Removed the version check based > > on discussion from [1] b) updated the documentation. > > I have tested various scenarios with the attached patch, the dbname > > that is written in postgresql.auto.conf for each of the cases with > > pg_basebackup is given below: > > 1) ./pg_basebackup -U vignesh -R > > -> primary_conninfo = "dbname=vignesh" (In this case primary_conninfo > > will have dbname as username specified) > > 2) ./pg_basebackup -D data -R > > -> primary_conninfo = "dbname=vignesh" (In this case primary_conninfo > > will have the dbname as username (which is the same as the OS user > > while setting defaults)) > > 3) ./pg_basebackup -d "user=vignesh" -D data -R > > -> primary_conninfo = "dbname=replication" (In this case > > primary_conninfo will have dbname as replication which is the default > > value from GetConnection as connection string is specified) > > 4) ./pg_basebackup -d "user=vignesh dbname=postgres" -D data -R > > -> primary_conninfo = "dbname=postgres" (In this case primary_conninfo > > will have the dbname as the dbname specified) > > 5) ./pg_basebackup -d "" -D data -R > > -> primary_conninfo = "dbname=replication" (In this case > > primary_conninfo will have dbname as replication which is the default > > value from GetConnection as connection string is specified) > > > > I have mentioned the reasons as to why dbname is being set to > > replication or username in each of the cases. > > > > IIUC, the dbname is already allowed in connstring for pg_basebackup by > commit cca97ce6a6 and with this patch we are just writing it in > postgresql.auto.conf if -R option is used to write recovery info. This > will help slot syncworker to automatically connect with database > without user manually specifying the dbname and replication > connection, the same will still be ignored. I don't see any problem > with this. Does anyone else see any problem? > > The <filename>postgresql.auto.conf</filename> file will record the connection > settings and, if specified, the replication slot > that <application>pg_basebackup</application> is using, so that > - streaming replication will use the same settings later on. > + a) synchronization of logical replication slots on the primary to the > + hot_standby from <link linkend="pg-sync-replication-slots"> > + <function>pg_sync_replication_slots</function></link> or slot > sync worker > + and b) streaming replication will use the same settings later on. > > We can simply modify the last line as: ".. streaming replication or > logical replication slots synchronization will use the same settings > later on." Additionally, you can give a link reference to [1]. Thanks for the comments, the attached v4 version patch has the changes for the same. Regards, Vignesh Attachments: [text/x-patch] pg_basebackup-write-dbname.v0004.patch (1.3K, ../../CALDaNm2Z827GyPTM8uj=Y6JcbtmDKVBa73XOK+2ztTkETq=75A@mail.gmail.com/2-pg_basebackup-write-dbname.v0004.patch) download | inline diff: diff --git a/doc/src/sgml/ref/pg_basebackup.sgml b/doc/src/sgml/ref/pg_basebackup.sgml index 88c689e725..563346dd71 100644 --- a/doc/src/sgml/ref/pg_basebackup.sgml +++ b/doc/src/sgml/ref/pg_basebackup.sgml @@ -243,7 +243,9 @@ PostgreSQL documentation The <filename>postgresql.auto.conf</filename> file will record the connection settings and, if specified, the replication slot that <application>pg_basebackup</application> is using, so that - streaming replication will use the same settings later on. + streaming replication or <link linkend="logicaldecoding-replication-slots-synchronization"> + logical replication slots synchronization</link> will use the same + settings later on. </para> </listitem> diff --git a/src/fe_utils/recovery_gen.c b/src/fe_utils/recovery_gen.c index 2585f11939..cb37703ab9 100644 --- a/src/fe_utils/recovery_gen.c +++ b/src/fe_utils/recovery_gen.c @@ -49,7 +49,6 @@ GenerateRecoveryConfig(PGconn *pgconn, char *replication_slot) { /* Omit empty settings and those libpqwalreceiver overrides. */ if (strcmp(opt->keyword, "replication") == 0 || - strcmp(opt->keyword, "dbname") == 0 || strcmp(opt->keyword, "fallback_application_name") == 0 || (opt->val == NULL) || (opt->val != NULL && opt->val[0] == '\0')) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-03-13 16:04 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-01-11 06:00 [PATCH 5/6] Documentation for slot-limit feature Kyotaro Horiguchi <[email protected]> 2018-01-11 06:00 [PATCH 5/6] Documentation for slot-limit feature Kyotaro Horiguchi <[email protected]> 2018-01-11 06:00 [PATCH 5/6] Documentation for slot-limit feature Kyotaro Horiguchi <[email protected]> 2018-01-11 06:00 [PATCH 5/6] Documentation for slot-limit feature Kyotaro Horiguchi <[email protected]> 2023-08-09 07:56 [PATCH v4 5/7] Row pattern recognition patch (docs). Tatsuo Ishii <[email protected]> 2024-03-13 16:04 Re: Have pg_basebackup write "dbname" in "primary_conninfo"? vignesh C <[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