public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 5/6] Documentation for slot-limit feature
9+ messages / 4 participants
[nested] [flat]
* [PATCH 4/4] Documentation for slot-limit feature
@ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 9+ 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 | 22 ++++++++++++++++++++++
doc/src/sgml/high-availability.sgml | 8 +++++---
3 files changed, 55 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 07e8b3325f..7e31267d68 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9887,6 +9887,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 the
+ 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 have been removed. The
+ last two states are seen only when
+ <xref linkend="guc-max-slot-wal-keep-size"/> is not zero. If the slot
+ doesn't have valid restart_lsn, 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 the
+ 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 bee4afbe4e..9d190c3daa 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3116,6 +3116,28 @@ include_dir 'conf.d'
</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 zero (the default),
+ replication slots retain unlimited size of WAL files.
+ </para>
+ <para>
+ This parameter is used being rounded down to the multiples of WAL file
+ size.
+ </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 8cb77f85ec..04cdccb10d 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -927,9 +927,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 allotted
+ 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_Sep_04_19_52_50_2018_992)----
^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 5/6] Documentation for slot-limit feature
@ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 9+ 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 af4d0625ea..7ec8764ce5 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9825,6 +9825,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 b6f5822b84..7177c6122a 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3537,6 +3537,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 bbab7395a2..79901c5f06 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_Jan_30_10_42_04_2019_681)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v12-0006-Check-removal-of-in-reading-segment-file.patch"
^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 5/6] Documentation for slot-limit feature
@ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 9+ 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 af4d0625ea..7ec8764ce5 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9825,6 +9825,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 4a7121a51f..3d034ac0d1 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3531,6 +3531,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 d8fd195da0..e30eaaeebe 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(Thu_Dec_20_16_24_38_2018_792)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v11-0006-Check-removal-of-in-reading-segment-file.patch"
^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 4/4] Documentation for slot-limit feature
@ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 9+ 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 9edba96fab..fc4cbc9239 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9881,6 +9881,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 the
+ 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 the
+ slot doesn't have valid restart_lsn, 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 the
+ 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 7554cba3f9..f3e504862c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3117,6 +3117,29 @@ include_dir 'conf.d'
</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 ebcb3daaed..15a98340a6 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -927,9 +927,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 allotted
+ 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(Thu_Oct_25_21_55_18_2018_318)----
^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 4/4] Documentation for slot-limit feature
@ 2018-01-11 06:00 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 9+ 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 0179deea2e..84a937e1fe 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9879,6 +9879,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 the
+ 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 the
+ slot doesn't have valid restart_lsn, 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 the
+ 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 bee4afbe4e..edd5419ec6 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3116,6 +3116,29 @@ include_dir 'conf.d'
</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 8cb77f85ec..04cdccb10d 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -927,9 +927,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 allotted
+ 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(Thu_Sep_13_18_29_31_2018_032)----
^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH 6/7] Fix WAL skipping feature.
@ 2019-04-02 04:29 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Kyotaro Horiguchi @ 2019-04-02 04:29 UTC (permalink / raw)
This patch replaces WAL-skipping means from HEAP_INSERT_SKIP_WAL to
the new infrastructure.
---
src/backend/access/heap/heapam.c | 114 +++++++++++++++++++++++--------
src/backend/access/heap/heapam_handler.c | 88 ++++++++++++++++++------
src/backend/access/heap/pruneheap.c | 3 +-
src/backend/access/heap/rewriteheap.c | 28 ++------
src/backend/access/heap/vacuumlazy.c | 6 +-
src/backend/access/heap/visibilitymap.c | 3 +-
src/backend/commands/cluster.c | 27 ++++++++
src/backend/commands/copy.c | 15 +++-
src/backend/commands/createas.c | 7 +-
src/backend/commands/matview.c | 7 +-
src/backend/commands/tablecmds.c | 8 ++-
src/include/access/rewriteheap.h | 2 +-
12 files changed, 219 insertions(+), 89 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 267570b461..cc516e599d 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -28,6 +28,27 @@
* the POSTGRES heap access method used for all POSTGRES
* relations.
*
+ * WAL CONSIDERATIONS
+ * All heap operations are normally WAL-logged. but there are a few
+ * exceptions. Temporary and unlogged relations never need to be
+ * WAL-logged, but we can also skip WAL-logging for a table that was
+ * created in the same transaction, if we don't need WAL for PITR or WAL
+ * archival purposes (i.e. if wal_level=minimal), and we fsync() the file
+ * to disk at COMMIT instead.
+ *
+ * The same-relation optimization is not employed automatically on all
+ * updates to a table that was created in the same transaction, because for
+ * a small number of changes, it's cheaper to just create the WAL records
+ * than fsync()ing the whole relation at COMMIT. It is only worthwhile for
+ * (presumably) large operations like COPY, CLUSTER, or VACUUM FULL. Use
+ * table_relation_register_sync() to initiate such an operation; it will
+ * cause any subsequent updates to the table to skip WAL-logging, if
+ * possible, and cause the heap to be synced to disk at COMMIT.
+ *
+ * To make that work, all modifications to heap must use
+ * BufferNeedsWAL() to check if WAL-logging is needed in this transaction
+ * for the given block.
+ *
*-------------------------------------------------------------------------
*/
#include "postgres.h"
@@ -51,6 +72,7 @@
#include "access/xloginsert.h"
#include "access/xlogutils.h"
#include "catalog/catalog.h"
+#include "catalog/storage.h"
#include "miscadmin.h"
#include "pgstat.h"
#include "port/atomics.h"
@@ -1948,7 +1970,7 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid,
MarkBufferDirty(buffer);
/* XLOG stuff */
- if (!(options & HEAP_INSERT_SKIP_WAL) && RelationNeedsWAL(relation))
+ if (BufferNeedsWAL(relation, buffer))
{
XLogRecPtr recptr;
@@ -2058,7 +2080,6 @@ heap_multi_insert(Relation relation, HeapTuple *tuples, int ntuples,
int ndone;
PGAlignedBlock scratch;
Page page;
- bool needwal;
Size saveFreeSpace;
bool need_tuple_data = RelationIsLogicallyLogged(relation);
bool need_cids = RelationIsAccessibleInLogicalDecoding(relation);
@@ -2066,7 +2087,6 @@ heap_multi_insert(Relation relation, HeapTuple *tuples, int ntuples,
/* currently not needed (thus unsupported) for heap_multi_insert() */
AssertArg(!(options & HEAP_INSERT_NO_LOGICAL));
- needwal = !(options & HEAP_INSERT_SKIP_WAL) && RelationNeedsWAL(relation);
saveFreeSpace = RelationGetTargetPageFreeSpace(relation,
HEAP_DEFAULT_FILLFACTOR);
@@ -2108,6 +2128,7 @@ heap_multi_insert(Relation relation, HeapTuple *tuples, int ntuples,
Buffer vmbuffer = InvalidBuffer;
bool all_visible_cleared = false;
int nthispage;
+ bool needwal;
CHECK_FOR_INTERRUPTS();
@@ -2119,6 +2140,7 @@ heap_multi_insert(Relation relation, HeapTuple *tuples, int ntuples,
InvalidBuffer, options, bistate,
&vmbuffer, NULL);
page = BufferGetPage(buffer);
+ needwal = BufferNeedsWAL(relation, buffer);
/* NO EREPORT(ERROR) from here till changes are logged */
START_CRIT_SECTION();
@@ -2671,7 +2693,7 @@ l1:
* NB: heap_abort_speculative() uses the same xlog record and replay
* routines.
*/
- if (RelationNeedsWAL(relation))
+ if (BufferNeedsWAL(relation, buffer))
{
XLogRecPtr recptr;
@@ -2805,6 +2827,8 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
vmbuffer = InvalidBuffer,
vmbuffer_new = InvalidBuffer;
bool need_toast;
+ bool oldbuf_needs_wal,
+ newbuf_needs_wal;
Size newtupsize,
pagefree;
bool have_tuple_lock = false;
@@ -3356,7 +3380,7 @@ l2:
MarkBufferDirty(buffer);
- if (RelationNeedsWAL(relation))
+ if (BufferNeedsWAL(relation, buffer))
{
xl_heap_lock xlrec;
XLogRecPtr recptr;
@@ -3570,26 +3594,55 @@ l2:
MarkBufferDirty(newbuf);
MarkBufferDirty(buffer);
- /* XLOG stuff */
- if (RelationNeedsWAL(relation))
+ /*
+ * XLOG stuff
+ *
+ * Emit heap-update log. When wal_level = minimal, we may emit insert or
+ * delete record according to wal-optimization.
+ */
+ oldbuf_needs_wal = BufferNeedsWAL(relation, buffer);
+
+ if (newbuf == buffer)
+ newbuf_needs_wal = oldbuf_needs_wal;
+ else
+ newbuf_needs_wal = BufferNeedsWAL(relation, newbuf);
+
+ if (oldbuf_needs_wal || newbuf_needs_wal)
{
XLogRecPtr recptr;
/*
* For logical decoding we need combocids to properly decode the
- * catalog.
+ * catalog. Both oldbuf_needs_wal and newbuf_needs_wal must be true
+ * when logical decoding is active.
*/
if (RelationIsAccessibleInLogicalDecoding(relation))
{
+ Assert(oldbuf_needs_wal && newbuf_needs_wal);
+
log_heap_new_cid(relation, &oldtup);
log_heap_new_cid(relation, heaptup);
}
- recptr = log_heap_update(relation, buffer,
- newbuf, &oldtup, heaptup,
- old_key_tuple,
- all_visible_cleared,
- all_visible_cleared_new);
+ /*
+ * Insert log record. Using delete or insert log loses HOT chain
+ * information but that happens only when newbuf is different from
+ * buffer, where HOT cannot happen.
+ */
+ if (oldbuf_needs_wal && newbuf_needs_wal)
+ recptr = log_heap_update(relation, buffer, newbuf,
+ &oldtup, heaptup,
+ old_key_tuple,
+ all_visible_cleared,
+ all_visible_cleared_new);
+ else if (oldbuf_needs_wal)
+ recptr = log_heap_delete(relation, buffer, &oldtup, old_key_tuple,
+ xmax_old_tuple, false,
+ all_visible_cleared);
+ else
+ recptr = log_heap_insert(relation, buffer, newtup,
+ 0, all_visible_cleared_new);
+
if (newbuf != buffer)
{
PageSetLSN(BufferGetPage(newbuf), recptr);
@@ -4467,7 +4520,7 @@ failed:
* (Also, in a PITR log-shipping or 2PC environment, we have to have XLOG
* entries for everything anyway.)
*/
- if (RelationNeedsWAL(relation))
+ if (BufferNeedsWAL(relation, *buffer))
{
xl_heap_lock xlrec;
XLogRecPtr recptr;
@@ -5219,7 +5272,7 @@ l4:
MarkBufferDirty(buf);
/* XLOG stuff */
- if (RelationNeedsWAL(rel))
+ if (BufferNeedsWAL(rel, buf))
{
xl_heap_lock_updated xlrec;
XLogRecPtr recptr;
@@ -5379,7 +5432,7 @@ heap_finish_speculative(Relation relation, ItemPointer tid)
htup->t_ctid = *tid;
/* XLOG stuff */
- if (RelationNeedsWAL(relation))
+ if (BufferNeedsWAL(relation, buffer))
{
xl_heap_confirm xlrec;
XLogRecPtr recptr;
@@ -5511,7 +5564,7 @@ heap_abort_speculative(Relation relation, ItemPointer tid)
* The WAL records generated here match heap_delete(). The same recovery
* routines are used.
*/
- if (RelationNeedsWAL(relation))
+ if (BufferNeedsWAL(relation, buffer))
{
xl_heap_delete xlrec;
XLogRecPtr recptr;
@@ -5620,7 +5673,7 @@ heap_inplace_update(Relation relation, HeapTuple tuple)
MarkBufferDirty(buffer);
/* XLOG stuff */
- if (RelationNeedsWAL(relation))
+ if (BufferNeedsWAL(relation, buffer))
{
xl_heap_inplace xlrec;
XLogRecPtr recptr;
@@ -7030,8 +7083,8 @@ log_heap_clean(Relation reln, Buffer buffer,
xl_heap_clean xlrec;
XLogRecPtr recptr;
- /* Caller should not call me on a non-WAL-logged relation */
- Assert(RelationNeedsWAL(reln));
+ /* Caller should not call me on non-WAL-logged buffers */
+ Assert(BufferNeedsWAL(reln, buffer));
xlrec.latestRemovedXid = latestRemovedXid;
xlrec.nredirected = nredirected;
@@ -7078,8 +7131,8 @@ log_heap_freeze(Relation reln, Buffer buffer, TransactionId cutoff_xid,
xl_heap_freeze_page xlrec;
XLogRecPtr recptr;
- /* Caller should not call me on a non-WAL-logged relation */
- Assert(RelationNeedsWAL(reln));
+ /* Caller should not call me on non-WAL-logged buffers */
+ Assert(BufferNeedsWAL(reln, buffer));
/* nor when there are no tuples to freeze */
Assert(ntuples > 0);
@@ -7305,8 +7358,8 @@ log_heap_update(Relation reln, Buffer oldbuf,
bool init;
int bufflags;
- /* Caller should not call me on a non-WAL-logged relation */
- Assert(RelationNeedsWAL(reln));
+ /* Caller should not call me when no buffer needs WAL-logging */
+ Assert(BufferNeedsWAL(reln, newbuf) || BufferNeedsWAL(reln, oldbuf));
XLogBeginInsert();
@@ -8910,9 +8963,16 @@ heap2_redo(XLogReaderState *record)
* heap_sync - sync a heap, for use when no WAL has been written
*
* This forces the heap contents (including TOAST heap if any) down to disk.
- * If we skipped using WAL, and WAL is otherwise needed, we must force the
- * relation down to disk before it's safe to commit the transaction. This
- * requires writing out any dirty buffers and then doing a forced fsync.
+ * If we did any changes to the heap bypassing the buffer manager, we must
+ * force the relation down to disk before it's safe to commit the
+ * transaction, because the direct modifications will not be flushed by
+ * the next checkpoint.
+ *
+ * We used to also use this after batch operations like COPY and CLUSTER,
+ * if we skipped using WAL and WAL is otherwise needed, but there were
+ * corner-cases involving other WAL-logged operations to the same
+ * relation, where that was not enough. table_relation_register_sync() should
+ * be used for that purpose instead.
*
* Indexes are not touched. (Currently, index operations associated with
* the commands that use this are WAL-logged and so do not need fsync.
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 5c96fc91b7..bddf026b81 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -57,6 +57,9 @@ static bool SampleHeapTupleVisible(TableScanDesc scan, Buffer buffer,
HeapTuple tuple,
OffsetNumber tupoffset);
+static void heapam_relation_register_walskip(Relation rel);
+static void heapam_relation_invalidate_walskip(Relation rel);
+
static const TableAmRoutine heapam_methods;
@@ -541,14 +544,10 @@ tuple_lock_retry:
}
static void
-heapam_finish_bulk_insert(Relation relation, int options)
+heapam_finish_bulk_insert(RelFileNode rnode, ForkNumber forkNum)
{
- /*
- * If we skipped writing WAL, then we need to sync the heap (but not
- * indexes since those use WAL anyway / don't go through tableam)
- */
- if (options & HEAP_INSERT_SKIP_WAL)
- heap_sync(relation);
+ /* Sync the file immedately */
+ smgrimmedsync(smgropen(rnode, InvalidBackendId), forkNum);
}
@@ -616,6 +615,12 @@ heapam_relation_copy_data(Relation rel, RelFileNode newrnode)
dstrel = smgropen(newrnode, rel->rd_backend);
RelationOpenSmgr(rel);
+ /*
+ * Register WAL-skipping for the relation. WAL-logging is skipped and sync
+ * the file at commit if the AM supports the feature.
+ */
+ table_relation_register_walskip(rel);
+
/*
* Create and copy all forks of the relation, and schedule unlinking of
* old physical files.
@@ -626,8 +631,7 @@ heapam_relation_copy_data(Relation rel, RelFileNode newrnode)
RelationCreateStorage(newrnode, rel->rd_rel->relpersistence);
/* copy main fork */
- RelationCopyStorage(rel->rd_smgr, dstrel, MAIN_FORKNUM,
- rel->rd_rel->relpersistence);
+ RelationCopyStorage(rel, dstrel, MAIN_FORKNUM);
/* copy those extra forks that exist */
for (ForkNumber forkNum = MAIN_FORKNUM + 1;
@@ -645,8 +649,7 @@ heapam_relation_copy_data(Relation rel, RelFileNode newrnode)
(rel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED &&
forkNum == INIT_FORKNUM))
log_smgrcreate(&newrnode, forkNum);
- RelationCopyStorage(rel->rd_smgr, dstrel, forkNum,
- rel->rd_rel->relpersistence);
+ RelationCopyStorage(rel, dstrel, forkNum);
}
}
@@ -670,7 +673,6 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
IndexScanDesc indexScan;
TableScanDesc tableScan;
HeapScanDesc heapScan;
- bool use_wal;
bool is_system_catalog;
Tuplesortstate *tuplesort;
TupleDesc oldTupDesc = RelationGetDescr(OldHeap);
@@ -684,15 +686,6 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
/* Remember if it's a system catalog */
is_system_catalog = IsSystemRelation(OldHeap);
- /*
- * We need to log the copied data in WAL iff WAL archiving/streaming is
- * enabled AND it's a WAL-logged rel.
- */
- use_wal = XLogIsNeeded() && RelationNeedsWAL(NewHeap);
-
- /* use_wal off requires smgr_targblock be initially invalid */
- Assert(RelationGetTargetBlock(NewHeap) == InvalidBlockNumber);
-
/* Preallocate values/isnull arrays */
natts = newTupDesc->natts;
values = (Datum *) palloc(natts * sizeof(Datum));
@@ -700,7 +693,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
/* Initialize the rewrite operation */
rwstate = begin_heap_rewrite(OldHeap, NewHeap, OldestXmin, FreezeXid,
- MultiXactCutoff, use_wal);
+ MultiXactCutoff);
/* Set up sorting if wanted */
@@ -946,6 +939,55 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
pfree(isnull);
}
+/*
+ * heapam_relation_register_walskip - register a heap to be WAL-skipped then
+ * synced to disk at commit
+ *
+ * This can be used to skip WAL-logging changes on a relation file. This makes
+ * note of the current size of the relation, and ensures that when the
+ * relation is extended, any changes to the new blocks in the heap, in the
+ * same transaction, will not be WAL-logged. Instead, the heap contents are
+ * flushed to disk at commit.
+ *
+ * This does the same for the TOAST heap, if any. Indexes are not affected.
+ */
+static void
+heapam_relation_register_walskip(Relation rel)
+{
+ /* non-WAL-logged tables never need fsync */
+ if (!RelationNeedsWAL(rel))
+ return;
+
+ RecordWALSkipping(rel);
+ if (OidIsValid(rel->rd_rel->reltoastrelid))
+ {
+ Relation toastrel;
+
+ toastrel = heap_open(rel->rd_rel->reltoastrelid, AccessShareLock);
+ RecordWALSkipping(toastrel);
+ heap_close(toastrel, AccessShareLock);
+ }
+
+ return;
+}
+
+/*
+ * heapam_relation_invalidate_walskip - invalidate registered WAL skipping
+ *
+ * After some file-replacing operations like CLUSTER, the old file no longe
+ * needs to be synced to disk. This function invalidates the registered
+ * WAL-skipping on the current relfilenode of the relation.
+ */
+static void
+heapam_relation_invalidate_walskip(Relation rel)
+{
+ /* non-WAL-logged tables never need fsync */
+ if (!RelationNeedsWAL(rel))
+ return;
+
+ RelationInvalidateWALSkip(rel);
+}
+
static bool
heapam_scan_analyze_next_block(TableScanDesc scan, BlockNumber blockno,
BufferAccessStrategy bstrategy)
@@ -2423,6 +2465,8 @@ static const TableAmRoutine heapam_methods = {
.relation_nontransactional_truncate = heapam_relation_nontransactional_truncate,
.relation_copy_data = heapam_relation_copy_data,
.relation_copy_for_cluster = heapam_relation_copy_for_cluster,
+ .relation_register_walskip = heapam_relation_register_walskip,
+ .relation_invalidate_walskip = heapam_relation_invalidate_walskip,
.relation_vacuum = heap_vacuum_rel,
.scan_analyze_next_block = heapam_scan_analyze_next_block,
.scan_analyze_next_tuple = heapam_scan_analyze_next_tuple,
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index a3e51922d8..a05659b168 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -20,6 +20,7 @@
#include "access/htup_details.h"
#include "access/xlog.h"
#include "catalog/catalog.h"
+#include "catalog/storage.h"
#include "miscadmin.h"
#include "pgstat.h"
#include "storage/bufmgr.h"
@@ -258,7 +259,7 @@ heap_page_prune(Relation relation, Buffer buffer, TransactionId OldestXmin,
/*
* Emit a WAL HEAP_CLEAN record showing what we did
*/
- if (RelationNeedsWAL(relation))
+ if (BufferNeedsWAL(relation, buffer))
{
XLogRecPtr recptr;
diff --git a/src/backend/access/heap/rewriteheap.c b/src/backend/access/heap/rewriteheap.c
index bce4274362..494f7fcd41 100644
--- a/src/backend/access/heap/rewriteheap.c
+++ b/src/backend/access/heap/rewriteheap.c
@@ -116,6 +116,7 @@
#include "access/xloginsert.h"
#include "catalog/catalog.h"
+#include "catalog/storage.h"
#include "lib/ilist.h"
@@ -144,7 +145,6 @@ typedef struct RewriteStateData
Page rs_buffer; /* page currently being built */
BlockNumber rs_blockno; /* block where page will go */
bool rs_buffer_valid; /* T if any tuples in buffer */
- bool rs_use_wal; /* must we WAL-log inserts? */
bool rs_logical_rewrite; /* do we need to do logical rewriting */
TransactionId rs_oldest_xmin; /* oldest xmin used by caller to determine
* tuple visibility */
@@ -238,15 +238,13 @@ static void logical_end_heap_rewrite(RewriteState state);
* oldest_xmin xid used by the caller to determine which tuples are dead
* freeze_xid xid before which tuples will be frozen
* min_multi multixact before which multis will be removed
- * use_wal should the inserts to the new heap be WAL-logged?
*
* Returns an opaque RewriteState, allocated in current memory context,
* to be used in subsequent calls to the other functions.
*/
RewriteState
begin_heap_rewrite(Relation old_heap, Relation new_heap, TransactionId oldest_xmin,
- TransactionId freeze_xid, MultiXactId cutoff_multi,
- bool use_wal)
+ TransactionId freeze_xid, MultiXactId cutoff_multi)
{
RewriteState state;
MemoryContext rw_cxt;
@@ -271,7 +269,6 @@ begin_heap_rewrite(Relation old_heap, Relation new_heap, TransactionId oldest_xm
/* new_heap needn't be empty, just locked */
state->rs_blockno = RelationGetNumberOfBlocks(new_heap);
state->rs_buffer_valid = false;
- state->rs_use_wal = use_wal;
state->rs_oldest_xmin = oldest_xmin;
state->rs_freeze_xid = freeze_xid;
state->rs_cutoff_multi = cutoff_multi;
@@ -330,7 +327,7 @@ end_heap_rewrite(RewriteState state)
/* Write the last page, if any */
if (state->rs_buffer_valid)
{
- if (state->rs_use_wal)
+ if (BlockNeedsWAL(state->rs_new_rel, state->rs_blockno))
log_newpage(&state->rs_new_rel->rd_node,
MAIN_FORKNUM,
state->rs_blockno,
@@ -344,19 +341,7 @@ end_heap_rewrite(RewriteState state)
(char *) state->rs_buffer, true);
}
- /*
- * If the rel is WAL-logged, must fsync before commit. We use heap_sync
- * to ensure that the toast table gets fsync'd too.
- *
- * It's obvious that we must do this when not WAL-logging. It's less
- * obvious that we have to do it even if we did WAL-log the pages. The
- * reason is the same as in tablecmds.c's copy_relation_data(): we're
- * writing data that's not in shared buffers, and so a CHECKPOINT
- * occurring during the rewriteheap operation won't have fsync'd data we
- * wrote before the checkpoint.
- */
- if (RelationNeedsWAL(state->rs_new_rel))
- heap_sync(state->rs_new_rel);
+ /* If we skipped using WAL, we will sync the relation at commit */
logical_end_heap_rewrite(state);
@@ -654,9 +639,6 @@ raw_heap_insert(RewriteState state, HeapTuple tup)
{
int options = HEAP_INSERT_SKIP_FSM;
- if (!state->rs_use_wal)
- options |= HEAP_INSERT_SKIP_WAL;
-
/*
* While rewriting the heap for VACUUM FULL / CLUSTER, make sure data
* for the TOAST table are not logically decoded. The main heap is
@@ -695,7 +677,7 @@ raw_heap_insert(RewriteState state, HeapTuple tup)
/* Doesn't fit, so write out the existing page */
/* XLOG stuff */
- if (state->rs_use_wal)
+ if (BlockNeedsWAL(state->rs_new_rel, state->rs_blockno))
log_newpage(&state->rs_new_rel->rd_node,
MAIN_FORKNUM,
state->rs_blockno,
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index b5b464e4a9..45139ec70e 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -945,7 +945,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
* page has been previously WAL-logged, and if not, do that
* now.
*/
- if (RelationNeedsWAL(onerel) &&
+ if (BufferNeedsWAL(onerel, buf) &&
PageGetLSN(page) == InvalidXLogRecPtr)
log_newpage_buffer(buf, true);
@@ -1209,7 +1209,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
}
/* Now WAL-log freezing if necessary */
- if (RelationNeedsWAL(onerel))
+ if (BufferNeedsWAL(onerel, buf))
{
XLogRecPtr recptr;
@@ -1591,7 +1591,7 @@ lazy_vacuum_page(Relation onerel, BlockNumber blkno, Buffer buffer,
MarkBufferDirty(buffer);
/* XLOG stuff */
- if (RelationNeedsWAL(onerel))
+ if (BufferNeedsWAL(onerel, buffer))
{
XLogRecPtr recptr;
diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c
index 64dfe06b26..1f5f7d92dd 100644
--- a/src/backend/access/heap/visibilitymap.c
+++ b/src/backend/access/heap/visibilitymap.c
@@ -88,6 +88,7 @@
#include "access/heapam_xlog.h"
#include "access/visibilitymap.h"
#include "access/xlog.h"
+#include "catalog/storage.h"
#include "miscadmin.h"
#include "port/pg_bitutils.h"
#include "storage/bufmgr.h"
@@ -276,7 +277,7 @@ visibilitymap_set(Relation rel, BlockNumber heapBlk, Buffer heapBuf,
map[mapByte] |= (flags << mapOffset);
MarkBufferDirty(vmBuf);
- if (RelationNeedsWAL(rel))
+ if (BufferNeedsWAL(rel, heapBuf))
{
if (XLogRecPtrIsInvalid(recptr))
{
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 4f4be1efbf..b5db26fda5 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -612,6 +612,18 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
relpersistence,
AccessExclusiveLock);
+ /*
+ * If wal_level is minimal, we skip WAL-logging even for WAL-logging
+ * relations. The filenode is synced at commit.
+ */
+ if (!XLogIsNeeded())
+ {
+ /* make_new_heap doesn't lock OIDNewHeap */
+ Relation newheap = table_open(OIDNewHeap, AccessShareLock);
+ table_relation_register_walskip(newheap);
+ table_close(newheap, AccessShareLock);
+ }
+
/* Copy the heap data into the new table in the desired order */
copy_table_data(OIDNewHeap, tableOid, indexOid, verbose,
&swap_toast_by_content, &frozenXid, &cutoffMulti);
@@ -1355,6 +1367,21 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
/* Zero out possible results from swapped_relation_files */
memset(mapped_tables, 0, sizeof(mapped_tables));
+ /*
+ * Unregister useless pending file-sync. table_relation_unregister_sync
+ * relies on a premise that relation cache has the correct relfilenode and
+ * related members. After swap_relation_files, the relcache entry for the
+ * heaps gets inconsistent with pg_class entry so we should do this before
+ * the call.
+ */
+ if (!XLogIsNeeded())
+ {
+ Relation oldheap = table_open(OIDOldHeap, AccessShareLock);
+
+ table_relation_invalidate_walskip(oldheap);
+ table_close(oldheap, AccessShareLock);
+ }
+
/*
* Swap the contents of the heap relations (including any toast tables).
* Also set old heap's relfrozenxid to frozenXid.
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index c1fd7b78ce..6a85ab890e 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -2437,9 +2437,13 @@ CopyFrom(CopyState cstate)
(cstate->rel->rd_createSubid != InvalidSubTransactionId ||
cstate->rel->rd_newRelfilenodeSubid != InvalidSubTransactionId))
{
- ti_options |= TABLE_INSERT_SKIP_FSM;
+ /*
+ * We can skip WAL-logging the insertions, unless PITR or streaming
+ * replication is in use. We can skip the FSM in any case.
+ */
if (!XLogIsNeeded())
- ti_options |= TABLE_INSERT_SKIP_WAL;
+ table_relation_register_walskip(cstate->rel);
+ ti_options |= TABLE_INSERT_SKIP_FSM;
}
/*
@@ -3106,7 +3110,12 @@ CopyFrom(CopyState cstate)
FreeExecutorState(estate);
- table_finish_bulk_insert(cstate->rel, ti_options);
+ /*
+ * If we skipped writing WAL, then we will sync the heap at the end of
+ * the transaction. (We used to do it here, but it was later found out
+ * that to be safe, we must also avoid WAL-logging any subsequent
+ * actions on the pages we skipped WAL for). Indexes always use WAL.
+ */
return processed;
}
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 43c2fa9124..8b73654413 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -558,8 +558,9 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
* We can skip WAL-logging the insertions, unless PITR or streaming
* replication is in use. We can skip the FSM in any case.
*/
- myState->ti_options = TABLE_INSERT_SKIP_FSM |
- (XLogIsNeeded() ? 0 : TABLE_INSERT_SKIP_WAL);
+ if (!XLogIsNeeded())
+ table_relation_register_walskip(intoRelationDesc);
+ myState->ti_options = HEAP_INSERT_SKIP_FSM;
myState->bistate = GetBulkInsertState();
/* Not using WAL requires smgr_targblock be initially invalid */
@@ -604,7 +605,7 @@ intorel_shutdown(DestReceiver *self)
FreeBulkInsertState(myState->bistate);
- table_finish_bulk_insert(myState->rel, myState->ti_options);
+ /* If we skipped using WAL, we will sync the relation at commit */
/* close rel, but keep lock until commit */
table_close(myState->rel, NoLock);
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 2aac63296b..33b7bc4c16 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -462,9 +462,10 @@ transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
* We can skip WAL-logging the insertions, unless PITR or streaming
* replication is in use. We can skip the FSM in any case.
*/
- myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN;
if (!XLogIsNeeded())
- myState->ti_options |= TABLE_INSERT_SKIP_WAL;
+ table_relation_register_walskip(transientrel);
+ myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN;
+
myState->bistate = GetBulkInsertState();
/* Not using WAL requires smgr_targblock be initially invalid */
@@ -509,7 +510,7 @@ transientrel_shutdown(DestReceiver *self)
FreeBulkInsertState(myState->bistate);
- table_finish_bulk_insert(myState->transientrel, myState->ti_options);
+ /* If we skipped using WAL, we will sync the relation at commit */
/* close transientrel, but keep lock until commit */
table_close(myState->transientrel, NoLock);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8908b77d98..deb147c45a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4716,7 +4716,11 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
ti_options = TABLE_INSERT_SKIP_FSM;
if (!XLogIsNeeded())
- ti_options |= TABLE_INSERT_SKIP_WAL;
+ {
+ /* Forget old relation's registerd sync */
+ table_relation_invalidate_walskip(oldrel);
+ table_relation_register_walskip(newrel);
+ }
}
else
{
@@ -5000,7 +5004,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode)
{
FreeBulkInsertState(bistate);
- table_finish_bulk_insert(newrel, ti_options);
+ /* If we skipped writing WAL, then it will be done at commit. */
table_close(newrel, NoLock);
}
diff --git a/src/include/access/rewriteheap.h b/src/include/access/rewriteheap.h
index 6006249d96..64efecf48b 100644
--- a/src/include/access/rewriteheap.h
+++ b/src/include/access/rewriteheap.h
@@ -23,7 +23,7 @@ typedef struct RewriteStateData *RewriteState;
extern RewriteState begin_heap_rewrite(Relation OldHeap, Relation NewHeap,
TransactionId OldestXmin, TransactionId FreezeXid,
- MultiXactId MultiXactCutoff, bool use_wal);
+ MultiXactId MultiXactCutoff);
extern void end_heap_rewrite(RewriteState state);
extern void rewrite_heap_tuple(RewriteState state, HeapTuple oldTuple,
HeapTuple newTuple);
--
2.16.3
----Next_Part(Tue_Apr_02_19_54_06_2019_801)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v10-0007-Remove-TABLE-HEAP_INSERT_SKIP_WAL.patch"
^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v3 6/7] Row pattern recognition patch (tests).
@ 2023-07-26 10:49 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Tatsuo Ishii @ 2023-07-26 10:49 UTC (permalink / raw)
---
src/test/regress/expected/rpr.out | 425 +++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/rpr.sql | 214 +++++++++++++++
3 files changed, 640 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/rpr.out
create mode 100644 src/test/regress/sql/rpr.sql
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
new file mode 100644
index 0000000000..19c054a0b8
--- /dev/null
+++ b/src/test/regress/expected/rpr.out
@@ -0,0 +1,425 @@
+--
+-- Test for row pattern definition clause
+--
+CREATE TEMP TABLE stock (
+ company TEXT,
+ tdate DATE,
+ price INTEGER
+ );
+INSERT INTO stock VALUES ('company1', '2023-07-01', 100);
+INSERT INTO stock VALUES ('company1', '2023-07-02', 200);
+INSERT INTO stock VALUES ('company1', '2023-07-03', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-04', 140);
+INSERT INTO stock VALUES ('company1', '2023-07-05', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-06', 90);
+INSERT INTO stock VALUES ('company1', '2023-07-07', 110);
+INSERT INTO stock VALUES ('company1', '2023-07-08', 130);
+INSERT INTO stock VALUES ('company1', '2023-07-09', 120);
+INSERT INTO stock VALUES ('company1', '2023-07-10', 130);
+INSERT INTO stock VALUES ('company2', '2023-07-01', 50);
+INSERT INTO stock VALUES ('company2', '2023-07-02', 2000);
+INSERT INTO stock VALUES ('company2', '2023-07-03', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-04', 1400);
+INSERT INTO stock VALUES ('company2', '2023-07-05', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-06', 60);
+INSERT INTO stock VALUES ('company2', '2023-07-07', 1100);
+INSERT INTO stock VALUES ('company2', '2023-07-08', 1300);
+INSERT INTO stock VALUES ('company2', '2023-07-09', 1200);
+INSERT INTO stock VALUES ('company2', '2023-07-10', 1300);
+SELECT * FROM stock;
+ company | tdate | price
+----------+------------+-------
+ company1 | 07-01-2023 | 100
+ company1 | 07-02-2023 | 200
+ company1 | 07-03-2023 | 150
+ company1 | 07-04-2023 | 140
+ company1 | 07-05-2023 | 150
+ company1 | 07-06-2023 | 90
+ company1 | 07-07-2023 | 110
+ company1 | 07-08-2023 | 130
+ company1 | 07-09-2023 | 120
+ company1 | 07-10-2023 | 130
+ company2 | 07-01-2023 | 50
+ company2 | 07-02-2023 | 2000
+ company2 | 07-03-2023 | 1500
+ company2 | 07-04-2023 | 1400
+ company2 | 07-05-2023 | 1500
+ company2 | 07-06-2023 | 60
+ company2 | 07-07-2023 | 1100
+ company2 | 07-08-2023 | 1300
+ company2 | 07-09-2023 | 1200
+ company2 | 07-10-2023 | 1300
+(20 rows)
+
+-- basic test using PREV
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value | nth_second
+----------+------------+-------+-------------+------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023
+ company1 | 07-02-2023 | 200 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | | |
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023
+ company1 | 07-07-2023 | 110 | | |
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | |
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | | |
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023
+ company2 | 07-07-2023 | 1100 | | |
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ company2 | 07-10-2023 | 1300 | | |
+(20 rows)
+
+-- the first row start with less than or equal to 100
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | 90 | 120
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1400
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | 60 | 1200
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- second row raises 120%
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price) * 1.2,
+ DOWN AS price < PREV(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 140
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1400
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- using NEXT
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 200
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | 140 | 150
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 110 | 130
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 2000
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | 1400 | 1500
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1300
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 200
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | 140 | 150
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 110 | 130
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 2000
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | 1400 | 1500
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 1100 | 1300
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- match everything
+SELECT company, tdate, price, first_value(price) OVER w, last_value(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 (A+)
+ DEFINE
+ A AS TRUE
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | 100 | 130
+ company1 | 07-02-2023 | 200 | |
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | |
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | 50 | 1300
+ company2 | 07-02-2023 | 2000 | |
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | |
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) 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 (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | |
+ company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023
+ company1 | 07-03-2023 | 150 | |
+ company1 | 07-04-2023 | 140 | |
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023
+ company1 | 07-08-2023 | 130 | |
+ company1 | 07-09-2023 | 120 | |
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | |
+ company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023
+ company2 | 07-03-2023 | 1500 | |
+ company2 | 07-04-2023 | 1400 | |
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023
+ company2 | 07-08-2023 | 1300 | |
+ company2 | 07-09-2023 | 1200 | |
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | first_value | last_value
+----------+------------+-------+-------------+------------
+ company1 | 07-01-2023 | 100 | |
+ company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023
+ company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023
+ company1 | 07-04-2023 | 140 | 07-04-2023 | 07-05-2023
+ company1 | 07-05-2023 | 150 | |
+ company1 | 07-06-2023 | 90 | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023
+ company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023
+ company1 | 07-09-2023 | 120 | 07-09-2023 | 07-10-2023
+ company1 | 07-10-2023 | 130 | |
+ company2 | 07-01-2023 | 50 | |
+ company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023
+ company2 | 07-03-2023 | 1500 | 07-03-2023 | 07-05-2023
+ company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-05-2023
+ company2 | 07-05-2023 | 1500 | |
+ company2 | 07-06-2023 | 60 | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023
+ company2 | 07-08-2023 | 1300 | 07-08-2023 | 07-10-2023
+ company2 | 07-09-2023 | 1200 | 07-09-2023 | 07-10-2023
+ company2 | 07-10-2023 | 1300 | |
+(20 rows)
+
+--
+-- Error cases
+--
+-- row pattern definition variable name must not appear more than once
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ORDER BY tdate
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+ERROR: syntax error at or near "ORDER"
+LINE 6: ORDER BY tdate
+ ^
+-- pattern variable name must appear in DEFINE
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+ END)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: syntax error at or near "END"
+LINE 8: PATTERN (START UP+ DOWN+ END)
+ ^
+-- FRAME must start at current row when row patttern recognition is used
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: FRAME must start at current row when row patttern recognition is used
+-- SEEK is not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ SEEK
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: SEEK is not supported
+LINE 8: SEEK
+ ^
+HINT: Use INITIAL.
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..896531002b 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -98,7 +98,7 @@ test: publication subscription
# Another group of parallel tests
# select_views depends on create_view
# ----------
-test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass
+test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass rpr
# ----------
# Another group of parallel tests (JSON related)
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
new file mode 100644
index 0000000000..01459e619b
--- /dev/null
+++ b/src/test/regress/sql/rpr.sql
@@ -0,0 +1,214 @@
+--
+-- Test for row pattern definition clause
+--
+
+CREATE TEMP TABLE stock (
+ company TEXT,
+ tdate DATE,
+ price INTEGER
+ );
+INSERT INTO stock VALUES ('company1', '2023-07-01', 100);
+INSERT INTO stock VALUES ('company1', '2023-07-02', 200);
+INSERT INTO stock VALUES ('company1', '2023-07-03', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-04', 140);
+INSERT INTO stock VALUES ('company1', '2023-07-05', 150);
+INSERT INTO stock VALUES ('company1', '2023-07-06', 90);
+INSERT INTO stock VALUES ('company1', '2023-07-07', 110);
+INSERT INTO stock VALUES ('company1', '2023-07-08', 130);
+INSERT INTO stock VALUES ('company1', '2023-07-09', 120);
+INSERT INTO stock VALUES ('company1', '2023-07-10', 130);
+INSERT INTO stock VALUES ('company2', '2023-07-01', 50);
+INSERT INTO stock VALUES ('company2', '2023-07-02', 2000);
+INSERT INTO stock VALUES ('company2', '2023-07-03', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-04', 1400);
+INSERT INTO stock VALUES ('company2', '2023-07-05', 1500);
+INSERT INTO stock VALUES ('company2', '2023-07-06', 60);
+INSERT INTO stock VALUES ('company2', '2023-07-07', 1100);
+INSERT INTO stock VALUES ('company2', '2023-07-08', 1300);
+INSERT INTO stock VALUES ('company2', '2023-07-09', 1200);
+INSERT INTO stock VALUES ('company2', '2023-07-10', 1300);
+
+SELECT * FROM stock;
+
+-- basic test using PREV
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w,
+ nth_value(tdate, 2) OVER w AS nth_second
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- the first row start with less than or equal to 100
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- second row raises 120%
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price) * 1.2,
+ DOWN AS price < PREV(price)
+);
+
+-- using NEXT
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (START UPDOWN)
+ DEFINE
+ START AS TRUE,
+ UPDOWN AS price > PREV(price) AND price > NEXT(price)
+);
+
+-- match everything
+
+SELECT company, tdate, price, first_value(price) OVER w, last_value(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 (A+)
+ DEFINE
+ A AS TRUE
+);
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP PAST LAST ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) 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 (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
+-- backtracking with reclassification of rows
+-- using AFTER MATCH SKIP TO NEXT ROW
+SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
+--
+-- Error cases
+--
+
+-- row pattern definition variable name must not appear more than once
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ORDER BY tdate
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+
+-- pattern variable name must appear in DEFINE
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+ END)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- FRAME must start at current row when row patttern recognition is used
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- SEEK is not supported
+SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w
+ FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ SEEK
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
--
2.25.1
----Next_Part(Wed_Jul_26_21_21_34_2023_317)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v3-0007-Allow-to-print-raw-parse-tree.patch"
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: change default default_toast_compression to lz4?
@ 2026-02-24 08:01 Michael Paquier <[email protected]>
2026-02-24 21:54 ` Re: change default default_toast_compression to lz4? Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Paquier @ 2026-02-24 08:01 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers; Peter Eisentraut <[email protected]>
On Thu, Feb 19, 2026 at 01:12:09PM -0300, Euler Taveira wrote:
> Changes from last version:
>
> - adjust CI
> - set not_found_dep instead of warning message
> - remove quotes from default_toast_compression value
>
> It is aligned with ICU and readline behavior.
Works for me, and I would like to apply this one.
diff --git a/meson.build b/meson.build
index 055e96315d0..13ef9c18477 100644
--- a/meson.build
+++ b/meson.build
@@ -1133,6 +1133,8 @@ if not lz4opt.disabled()
if lz4.found()
cdata.set('USE_LZ4', 1)
cdata.set('HAVE_LIBLZ4', 1)
+ else
+ lz4 = not_found_dep
endif
Hmm. Isn't this bit something that we should actually backpatch? It
feels wrong to not enforce lz4 to not_found_dep in this meson path.
So this looks unrelated to the switch of the default value.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: change default default_toast_compression to lz4?
2026-02-24 08:01 Re: change default default_toast_compression to lz4? Michael Paquier <[email protected]>
@ 2026-02-24 21:54 ` Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Peter Eisentraut @ 2026-02-24 21:54 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; Euler Taveira <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers
On 24.02.26 09:01, Michael Paquier wrote:
> diff --git a/meson.build b/meson.build
> index 055e96315d0..13ef9c18477 100644
> --- a/meson.build
> +++ b/meson.build
> @@ -1133,6 +1133,8 @@ if not lz4opt.disabled()
> if lz4.found()
> cdata.set('USE_LZ4', 1)
> cdata.set('HAVE_LIBLZ4', 1)
> + else
> + lz4 = not_found_dep
> endif
>
> Hmm. Isn't this bit something that we should actually backpatch? It
> feels wrong to not enforce lz4 to not_found_dep in this meson path.
> So this looks unrelated to the switch of the default value.
I don't think this change is correct, or at least it's not required, and
you won't find it in the handling of other dependencies.
The point of assigning not_found_dep is to make lz4.found() return
false, but in that particular code location lz4.found() is already false.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-02-24 21:54 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-11 06:00 [PATCH 4/4] 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 4/4] Documentation for slot-limit feature Kyotaro Horiguchi <[email protected]>
2018-01-11 06:00 [PATCH 4/4] Documentation for slot-limit feature Kyotaro Horiguchi <[email protected]>
2019-04-02 04:29 [PATCH 6/7] Fix WAL skipping feature. Kyotaro Horiguchi <[email protected]>
2023-07-26 10:49 [PATCH v3 6/7] Row pattern recognition patch (tests). Tatsuo Ishii <[email protected]>
2026-02-24 08:01 Re: change default default_toast_compression to lz4? Michael Paquier <[email protected]>
2026-02-24 21:54 ` Re: change default default_toast_compression to lz4? Peter Eisentraut <[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