public inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
23+ messages / 3 participants
[nested] [flat]

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
@ 2021-01-20 11:57 Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Kyotaro Horiguchi @ 2021-01-20 11:57 UTC (permalink / raw)

---
 src/test/modules/snapshot_too_old/Makefile    | 12 ++++-
 .../expected/sto_wal_optimized.out            | 24 ++++++++++
 .../input_sto/sto_wal_optimized.spec          | 44 +++++++++++++++++++
 src/test/modules/snapshot_too_old/sto.conf    |  3 ++
 4 files changed, 82 insertions(+), 1 deletion(-)
 create mode 100644 src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
 create mode 100644 src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec

diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
index dfb4537f63..1e0b0b6efc 100644
--- a/src/test/modules/snapshot_too_old/Makefile
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -4,7 +4,7 @@
 # we have to clean those result files explicitly
 EXTRA_CLEAN = $(pg_regress_clean_files)
 
-ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index
+ISOLATION = sto_using_cursor sto_using_select sto_using_hash_index sto_wal_optimized
 ISOLATION_OPTS = --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf
 
 # Disabled because these tests require "old_snapshot_threshold" >= 0, which
@@ -22,6 +22,16 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
+.PHONY: tablespace-setup specfile-setup
+tablespace-setup:
+	rm -rf ./testtablespace
+	mkdir ./testtablespace
+
+specfile-setup: input_sto/*.spec
+	sed 's!@srcdir@!$(realpath $(top_srcdir))!g' $? > specs/$(notdir $?)
+
+check: tablespace-setup specfile-setup
+
 # But it can nonetheless be very helpful to run tests on preexisting
 # installation, allow to do so, but only if requested explicitly.
 installcheck-force:
diff --git a/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
new file mode 100644
index 0000000000..4038d0a713
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_wal_optimized.out
@@ -0,0 +1,24 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1a1 s1a2 s2a1 s2a2 s1b1 a3-0 a3-1 s3-2 s3-3 s3-4 s2b1
+step s1a1: BEGIN;
+step s1a2: DELETE FROM t;
+step s2a1: BEGIN ISOLATION LEVEL REPEATABLE READ;
+step s2a2: SELECT 1;
+?column?       
+
+1              
+step s1b1: COMMIT;
+step a3-0: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting        pg_sleep       
+
+0                             
+step a3-1: BEGIN;
+step s3-2: ALTER TABLE t SET TABLESPACE tsp1;
+step s3-3: SELECT count(*) FROM t;
+count          
+
+0              
+step s3-4: COMMIT;
+step s2b1: SELECT count(*) FROM t;
+ERROR:  snapshot too old
diff --git a/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
new file mode 100644
index 0000000000..02adb50581
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/input_sto/sto_wal_optimized.spec
@@ -0,0 +1,44 @@
+# This test provokes a "snapshot too old" error 
+#
+# The sleep is needed because with a threshold of zero a statement could error
+# on changes it made.  With more normal settings no external delay is needed,
+# but we don't want these tests to run long enough to see that, since
+# granularity is in minutes.
+#
+# Since results depend on the value of old_snapshot_threshold, sneak that into
+# the line generated by the sleep, so that a surprising values isn't so hard
+# to identify.
+
+setup
+{
+	CREATE TABLESPACE tsp1 LOCATION '@srcdir@/src/test/modules/snapshot_too_old/testtablespace';
+}
+
+setup
+{
+	CREATE TABLE t AS SELECT a FROM generate_series(0, 9) a;
+}
+
+session "s1"
+step "s1a1" { BEGIN; }
+step "s1a2" { DELETE FROM t; }
+step "s1b1" { COMMIT; }
+
+session "s2"
+# s2a : take snapshot
+step "s2a1" { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s2a2" { SELECT 1; }
+# s2b : wanted "snapshot too old"
+step "s2b1" { SELECT count(*) FROM t; }
+
+session "s3"
+# s3-0 : wait for snapshot is expired
+step "a3-0"   { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "a3-1"   { BEGIN; }
+# s3-2 : start skipping WAL when wal_level=minimal
+step "s3-2"   { ALTER TABLE t SET TABLESPACE tsp1; }
+# s3-3 : early pruning. w/o WAL and LSN changes when wal_level=minimal
+step "s3-3"   { SELECT count(*) FROM t; }
+step "s3-4"   { COMMIT; }
+
+permutation "s1a1" "s1a2" "s2a1" "s2a2" "s1b1" "a3-0" "a3-1" "s3-2" "s3-3" "s3-4" "s2b1"
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
index 7eeaeeb0dc..09887fc725 100644
--- a/src/test/modules/snapshot_too_old/sto.conf
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -1,2 +1,5 @@
 autovacuum = off
 old_snapshot_threshold = 0
+
+# needed when setting wal_level=minimal
+wal_skip_threshold = 0
-- 
2.27.0


----Next_Part(Thu_Jan_21_00_28_44_2021_003)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="v5-0002-Do-not-use-RelationNeedsWAL-to-identify-relation-.patch"



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* RE: [PoC] pg_upgrade: allow to upgrade publisher node
@ 2023-04-07 13:59 Hayato Kuroda (Fujitsu) <[email protected]>
  2023-04-11 08:20 ` Re: [PoC] pg_upgrade: allow to upgrade publisher node Peter Smith <[email protected]>
  0 siblings, 1 reply; 23+ messages in thread

From: Hayato Kuroda (Fujitsu) @ 2023-04-07 13:59 UTC (permalink / raw)
  To: 'Peter Smith' <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Julien Rouhaud <[email protected]>; Amit Kapila <[email protected]>

Dear Peter,

Thank you for reviewing briefly. PSA new version.
If you can I want to ask the opinion about the checking by pg_upgrade [1].

> ======
> General
> 
> 1.
> Since these two new options are made to work together, I think the
> names should be more similar. e.g.
> 
> pg_dump: "--slot_only" --> "--replication-slots-only"
> pg_upgrade: "--include-replication-slot" --> "--include-replication-slots"
> 
> help/comments/commit-message all should change accordingly, but I did
> not give separate review comments for each of these.

OK, I renamed. By the way, how do you think the suggestion raised by Julien?
Currently I did not address it because the restriction was caused by just lack of
analysis, and this may be not agreed in the community.
Or, should we keep the name anyway?

> 2.
> I felt there maybe should be some pg_dump test cases for that new
> option, rather than the current patch where it only seems to be
> testing the new pg_dump option via the pg_upgrade TAP tests.

Hmm, I supposed that the option shoul be used only for upgrading, so I'm not sure
it must be tested by only pg_dump.

> Commit message
> 
> 3.
> This commit introduces a new option called "--include-replication-slot".
> This allows nodes with logical replication slots to be upgraded. The commit can
> be divided into two parts: one for pg_dump and another for pg_upgrade.
> 
> ~
> 
> "new option" --> "new pg_upgrade" option

Fixed.

> 4.
> For pg_upgrade, when '--include-replication-slot' is specified, it
> executes pg_dump
> with added option and restore from the dump. Apart from restoring
> schema, pg_resetwal
> must not be called after restoring replicaiton slots. This is because
> the command
> discards WAL files and starts from a new segment, even if they are required by
> replication slots. This leads an ERROR: "requested WAL segment XXX has already
> been removed". To avoid this, replication slots are restored at a different time
> than other objects, after running pg_resetwal.
> 
> ~
> 
> 4a.
> "with added option and restore from the dump" --> "with the new
> "--slot-only" option and restores from the dump"

Fixed.

> 4b.
> Typo: /replicaiton/replication/

Fixed.

> 4c
> "leads an ERROR" --> "leads to an ERROR"

Fixed.

> doc/src/sgml/ref/pg_dump.sgml
> 
> 5.
> +     <varlistentry>
> +      <term><option>--slot-only</option></term>
> +      <listitem>
> +       <para>
> +        Dump only replication slots, neither the schema (data definitions) nor
> +        data. Mainly this is used for upgrading nodes.
> +       </para>
> +      </listitem>
> 
> SUGGESTION
> Dump only replication slots; not the schema (data definitions), nor
> data. This is mainly used when upgrading nodes.

Fixed.

> doc/src/sgml/ref/pgupgrade.sgml
> 
> 6.
> +       <para>
> +        Transport replication slots. Currently this can work only for logical
> +        slots, and temporary slots are ignored. Note that pg_upgrade does not
> +        check the installation of plugins.
> +       </para>
> 
> SUGGESTION
> Upgrade replication slots. Only logical replication slots are
> currently supported, and temporary slots are ignored. Note that...

Fixed.

> src/bin/pg_dump/pg_dump.c
> 
> 7. main
>   {"exclude-table-data-and-children", required_argument, NULL, 14},
> -
> + {"slot-only", no_argument, NULL, 15},
>   {NULL, 0, NULL, 0}
> 
> The blank line is misplaced.

Fixed.

> 8. main
> + case 15: /* dump onlu replication slot(s) */
> + dopt.slot_only = true;
> + dopt.include_everything = false;
> + break;
> 
> typo: /onlu/only/

Fixed.

> 9. main
> + if (dopt.slot_only && dopt.dataOnly)
> + pg_fatal("options --replicatin-slots and -a/--data-only cannot be
> used together");
> + if (dopt.slot_only && dopt.schemaOnly)
> + pg_fatal("options --replicatin-slots and -s/--schema-only cannot be
> used together");
> +
> 
> 9a.
> typo: /replicatin/replication/

Fixed. Additionally, wrong parameter reference was also fixed.

> 9b.
> I am wondering if these checks are enough. E.g. is "slots-only"
> compatible with "no-publications" ?

I think there are something what should be checked more. But I'm not sure about
"no-publication". There is a possibility that non-core logical replication is used,
and at that time these options are not contradicted.

> 10. main
> + /*
> + * If dumping replication slots are request, dumping them and skip others.
> + */
> + if (dopt.slot_only)
> + {
> + getRepliactionSlots(fout);
> + goto dump;
> + }
> 
> 10a.
> SUGGESTION
> If dump replication-slots-only was requested, dump only them and skip
> everything else.

Fixed.

> 10b.
> This code seems mutually exclusive to every other option. I'm
> wondering if this code even needs 'collectRoleNames', or should the
> slots option check be moved  above that (and also above the 'Dumping
> LOs' etc...)

I read again, and I found that collected username are used to check the owner of
objects. IIUC replicaiton slots are not owned by database users, so it is not
needed. Also, the LOs should not dumped here. Based on them, I moved getRepliactionSlots()
above them.

> 11. help
> 
> + printf(_("  --slot-only                  dump only replication
> slots, no schema and data\n"));
> 
> 11a.
> SUGGESTION
> "no schema and data" --> "no schema or data"

Fixed.

> 11b.
> This help is misplaced. It should be in alphabetical order consistent
> with all the other help.
> 
> ~~~
> 12. getRepliactionSlots
> 
> +/*
> + * getRepliactionSlots
> + *   get information about replication slots
> + */
> +static void
> +getRepliactionSlots(Archive *fout)
> 
> Function name typo / getRepliactionSlots/ getReplicationSlots/
> (also in the comment)

Fixed.

> 13. getRepliactionSlots
> 
> + /* Check whether we should dump or not */
> + if (fout->remoteVersion < 160000 && !dopt->slot_only)
> + return;
> 
> Hmmm, is that condition correct? Shouldn't the && be || here?

Right, fixed.

> 14. dumpReplicationSlot
> 
> +static void
> +dumpReplicationSlot(Archive *fout, const ReplicationSlotInfo *slotinfo)
> +{
> + DumpOptions *dopt = fout->dopt;
> + PQExpBuffer query;
> + char *slotname;
> +
> + if (!dopt->slot_only)
> + return;
> +
> + slotname = pg_strdup(slotinfo->dobj.name);
> + query = createPQExpBuffer();
> +
> + /*
> + * XXX: For simplification, pg_create_logical_replication_slot() is used.
> + * Is it sufficient?
> + */
> + appendPQExpBuffer(query, "SELECT pg_create_logical_replication_slot('%s', ",
> +   slotname);
> + appendStringLiteralAH(query, slotinfo->plugin, fout);
> + appendPQExpBuffer(query, ", ");
> + appendStringLiteralAH(query, slotinfo->twophase, fout);
> + appendPQExpBuffer(query, ");");
> +
> + if (slotinfo->dobj.dump & DUMP_COMPONENT_DEFINITION)
> + ArchiveEntry(fout, slotinfo->dobj.catId, slotinfo->dobj.dumpId,
> + ARCHIVE_OPTS(.tag = slotname,
> +   .description = "REPICATION SLOT",
> +   .section = SECTION_POST_DATA,
> +   .createStmt = query->data));
> +
> + /* XXX: do we have to dump security label? */
> +
> + if (slotinfo->dobj.dump & DUMP_COMPONENT_COMMENT)
> + dumpComment(fout, "REPICATION SLOT", slotname,
> + NULL, NULL,
> + slotinfo->dobj.catId, 0, slotinfo->dobj.dumpId);
> +
> + pfree(slotname);
> + destroyPQExpBuffer(query);
> +}
> 
> 14a.
> Wouldn't it be better to check the "slotinfo->dobj.dump &
> DUMP_COMPONENT_DEFINITION" condition first, before building the query?
> For example, see other function dumpIndexAttach().

The style was chosen because previously I referred dumpSubscription(). But I read
PG manual and understood that COMMENT and SECURITY LABEL cannot be set to replication
slots. Therefore, I removed comments and dump for DUMP_COMPONENT_COMMENT, then
followed the style.

> 14b.
> Typo: /REPICATION SLOT/REPLICATION SLOT/ in the ARCHIVE_OPTS
> description.
> 
> ~
> 
> 14c.
> Typo: /REPICATION SLOT/REPLICATION SLOT/ in the dumpComment parameter.

Both of them were fixed.

> src/bin/pg_dump/pg_dump.h
> 
> 15. DumpableObjectType
> 
> @@ -82,7 +82,8 @@ typedef enum
>   DO_PUBLICATION,
>   DO_PUBLICATION_REL,
>   DO_PUBLICATION_TABLE_IN_SCHEMA,
> - DO_SUBSCRIPTION
> + DO_SUBSCRIPTION,
> + DO_REPICATION_SLOT
>  } DumpableObjectType;
> 
> Typo /DO_REPICATION_SLOT/DO_REPLICATION_SLOT/

Fixed.

> src/bin/pg_upgrade/dump.c
> 
> 16. generate_old_dump
> 
> + /*
> + * Dump replicaiton slots if needed.
> + *
> + * XXX We cannot dump replication slots at the same time as the schema
> + * dump because we need to separate the timing of restoring replication
> + * slots and other objects. Replication slots, in particular, should
> + * not be restored before executing the pg_resetwal command because it
> + * will remove WALs that are required by the slots.
> + */
> 
> Typo: /replicaiton/replication/

Fixed.

> src/bin/pg_upgrade/pg_upgrade.c
> 
> 17. main
> 
> + /*
> + * Create replication slots if requested.
> + *
> + * XXX This must be done after doing pg_resetwal command because the
> + * command will remove required WALs.
> + */
> + if (user_opts.include_slots)
> + {
> + start_postmaster(&new_cluster, true);
> + create_replicaiton_slots();
> + stop_postmaster(false);
> + }
> +
> 
> I don't think that warrants a "XXX" style comment. It is just a "Note:".

Fixed. Could you please tell me the classification of them if you can?

> 18. create_replicaiton_slots
> +
> +/*
> + * create_replicaiton_slots()
> + *
> + * Similar to create_new_objects() but only restores replication slots.
> + */
> +static void
> +create_replicaiton_slots(void)
> 
> Typo: /create_replicaiton_slots/create_replication_slots/
> 
> (Function name and comment)

All of them were replaced.

> 19. create_replicaiton_slots
> 
> + for (dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
> + {
> + char slots_file_name[MAXPGPATH],
> + log_file_name[MAXPGPATH];
> + DbInfo    *old_db = &old_cluster.dbarr.dbs[dbnum];
> + char    *opts;
> +
> + pg_log(PG_STATUS, "%s", old_db->db_name);
> +
> + snprintf(slots_file_name, sizeof(slots_file_name),
> + DB_DUMP_FILE_MASK_FOR_SLOTS, old_db->db_oid);
> + snprintf(log_file_name, sizeof(log_file_name),
> + DB_DUMP_LOG_FILE_MASK, old_db->db_oid);
> +
> + opts = "--echo-queries --set ON_ERROR_STOP=on --no-psqlrc";
> +
> + parallel_exec_prog(log_file_name,
> +    NULL,
> +    "\"%s/psql\" %s %s --dbname %s -f \"%s/%s\"",
> +    new_cluster.bindir,
> +    cluster_conn_opts(&new_cluster),
> +    opts,
> +    old_db->db_name,
> +    log_opts.dumpdir,
> +    slots_file_name);
> + }
> 
> That 'opts' variable seems unnecessary. Why not just pass the string
> literal directly when invoking parallel_exec_prog()?
> 
> Or if not removed, then at make it const char psql_opts =
> "--echo-queries --set ON_ERROR_STOP=on --no-psqlrc";

I had tried to follow the prepare_new_globals() style, but
I preferred your suggestion. Fixed.

> src/bin/pg_upgrade/pg_upgrade.h
> 
> 20.
> +#define DB_DUMP_FILE_MASK_FOR_SLOTS
> "pg_upgrade_dump_%u_slots.custom"
> 
> 20a.
> For consistency with other mask names (e.g. DB_DUMP_LOG_FILE_MASK)
> probably this should be called DB_DUMP_SLOTS_FILE_MASK.

Fixed.

> 20b.
> Because the content of this dump/restore file is SQL (not custom
> binary) wouldn't a filename suffix ".sql" be better?

Right, fixed.

> .../pg_upgrade/t/003_logical_replication.pl
> 
> 21.
> Some parts (formatting, comments, etc) in this file are inconsistent.
> 
> 21a
> ");" is sometimes alone on a line, sometimes not

I ran pgperltidy and lonely ");" is removed.

> 21b.
> "Init" versus "Create" nodes.

"Initialize" was chosen.

> 21c.
> # Check whether changes on new publisher are shipped to subscriber
> 
> SUGGESTION
> Check whether changes on the new publisher get replicated to the subscriber

Fixed.

> 21d.
> $result =
>   $subscriber->safe_psql('postgres', "SELECT count(*) FROM tbl");
> is($result, qq(20),
>     'check changes are shipped to subscriber');
> 
> For symmetry with before/after, I think it would be better to do this
> same command before the upgrade to confirm q(10) rows.

Added.

[1]: https://www.postgresql.org/message-id/20230407024823.3j2s4doslsjemvis%40jrouhaud

Best Regards,
Hayato Kuroda
FUJITSU LIMITED



Attachments:

  [application/octet-stream] v2-0001-pg_upgrade-Add-include-replication-slots-option.patch (22.1K, ../../TYCPR01MB5870E212F5012FD6272CE1E3F5969@TYCPR01MB5870.jpnprd01.prod.outlook.com/2-v2-0001-pg_upgrade-Add-include-replication-slots-option.patch)
  download | inline diff:
From 3ac0cdd9fef5bbaa3cdd152e3128a8e4747208a1 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Tue, 4 Apr 2023 05:49:34 +0000
Subject: [PATCH v2] pg_upgrade: Add --include-replication-slots option

This commit introduces a new pg_upgrade option called "--include-replication-slots".
This allows nodes with logical replication slots to be upgraded. The commit can
be divided into two parts: one for pg_dump and another for pg_upgrade.

For pg_dump this commit includes a new option called "--replication-slots-only".
This option can be used to dump replication slots. When this option is specified,
the slot_name, plugin, and two_phase parameters are extracted from pg_replication_slots.
An SQL file is then generated which executes pg_create_logical_replication_slot()
with the extracted parameters.

For pg_upgrade, when '--include-replication-slots' is specified, it executes pg_dump
with the new "--replication-slots-only" option and restores from the dump. Apart
from restoring schema, pg_resetwal must not be called after restoring replication
slots. This is because the command discards WAL files and starts from a new segment,
even if they are required by replication slots. This leads to an ERROR: "requested
WAL segment XXX has already been removed". To avoid this, replication slots are
restored at a different time than other objects, after running pg_resetwal.

The significant advantage of this commit is that it makes it easy to continue
logical replication even after upgrading the publisher node. Previously, pg_upgrade
allowed copying publications to a new node. With this new commit, adjusting the
connection string to the new publisher will cause the apply worker on the subscriber
to connect to the new publisher automatically. This enables seamless continuation
of logical replication, even after an upgrade.

Author: Hayato Kuroda
Reviewed-by: Peter Smith, Julien Rouhaud
---
 doc/src/sgml/ref/pg_dump.sgml                 |  10 ++
 doc/src/sgml/ref/pgupgrade.sgml               |  11 ++
 src/bin/pg_dump/pg_backup.h                   |   1 +
 src/bin/pg_dump/pg_dump.c                     | 141 ++++++++++++++++++
 src/bin/pg_dump/pg_dump.h                     |  15 +-
 src/bin/pg_dump/pg_dump_sort.c                |   4 +
 src/bin/pg_upgrade/dump.c                     |  23 +++
 src/bin/pg_upgrade/meson.build                |   1 +
 src/bin/pg_upgrade/option.c                   |   6 +
 src/bin/pg_upgrade/pg_upgrade.c               |  61 ++++++++
 src/bin/pg_upgrade/pg_upgrade.h               |   2 +
 .../pg_upgrade/t/003_logical_replication.pl   |  89 +++++++++++
 12 files changed, 363 insertions(+), 1 deletion(-)
 create mode 100644 src/bin/pg_upgrade/t/003_logical_replication.pl

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index e81e35c13b..2cd4fd10b0 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1206,6 +1206,16 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--replication-slots-only</option></term>
+      <listitem>
+       <para>
+        Dump only replication slots; not the schema (data definitions), nor
+        data. This is mainly used when upgrading nodes.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
        <term><option>-?</option></term>
        <term><option>--help</option></term>
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index 7816b4c685..6505b0fd34 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -240,6 +240,17 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--include-replication-slots</option></term>
+      <listitem>
+       <para>
+        Upgrade replication slots. Only logical replication slots are currently
+        supported, and temporary slots are ignored. Note that pg_upgrade does
+        not check the installation of plugins.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>-?</option></term>
       <term><option>--help</option></term>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index aba780ef4b..8a6f25cf2c 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -187,6 +187,7 @@ typedef struct _dumpOptions
 	int			use_setsessauth;
 	int			enable_row_security;
 	int			load_via_partition_root;
+	int			slot_only;
 
 	/* default, if no "inclusion" switches appear, is to dump everything */
 	bool		include_everything;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7a504dfe25..78c7102d3e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -328,6 +328,9 @@ static void setupDumpWorker(Archive *AH);
 static TableInfo *getRootTableInfo(const TableInfo *tbinfo);
 static bool forcePartitionRootLoad(const TableInfo *tbinfo);
 
+static void getReplicationSlots(Archive *fout);
+static void dumpReplicationSlot(Archive *fout,
+								const ReplicationSlotInfo * slotinfo);
 
 int
 main(int argc, char **argv)
@@ -431,6 +434,7 @@ main(int argc, char **argv)
 		{"table-and-children", required_argument, NULL, 12},
 		{"exclude-table-and-children", required_argument, NULL, 13},
 		{"exclude-table-data-and-children", required_argument, NULL, 14},
+		{"replication-slots-only", no_argument, NULL, 15},
 
 		{NULL, 0, NULL, 0}
 	};
@@ -657,6 +661,11 @@ main(int argc, char **argv)
 										  optarg);
 				break;
 
+			case 15:			/* dump only replication slot(s) */
+				dopt.slot_only = true;
+				dopt.include_everything = false;
+				break;
+
 			default:
 				/* getopt_long already emitted a complaint */
 				pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -714,6 +723,11 @@ main(int argc, char **argv)
 	if (dopt.do_nothing && dopt.dump_inserts == 0)
 		pg_fatal("option --on-conflict-do-nothing requires option --inserts, --rows-per-insert, or --column-inserts");
 
+	if (dopt.slot_only && dopt.dataOnly)
+		pg_fatal("options --replication-slots-only and -a/--data-only cannot be used together");
+	if (dopt.slot_only && dopt.schemaOnly)
+		pg_fatal("options --replication-slots-only and -s/--schema-only cannot be used together");
+
 	/* Identify archive format to emit */
 	archiveFormat = parseArchiveFormat(format, &archiveMode);
 
@@ -876,6 +890,16 @@ main(int argc, char **argv)
 			pg_fatal("no matching extensions were found");
 	}
 
+	/*
+	 * If dump replication-slots-only was requested, dump only them and skip
+	 * everything else.
+	 */
+	if (dopt.slot_only)
+	{
+		getReplicationSlots(fout);
+		goto dump;
+	}
+
 	/*
 	 * Dumping LOs is the default for dumps where an inclusion switch is not
 	 * used (an "include everything" dump).  -B can be used to exclude LOs
@@ -936,6 +960,8 @@ main(int argc, char **argv)
 	if (!dopt.no_security_labels)
 		collectSecLabels(fout);
 
+dump:
+
 	/* Lastly, create dummy objects to represent the section boundaries */
 	boundaryObjs = createBoundaryObjects();
 
@@ -1119,6 +1145,7 @@ help(const char *progname)
 	printf(_("  --no-unlogged-table-data     do not dump unlogged table data\n"));
 	printf(_("  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands\n"));
 	printf(_("  --quote-all-identifiers      quote all identifiers, even if not key words\n"));
+	printf(_("  --replication-slots-only     dump only replication slots, no schema or data\n"));
 	printf(_("  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts\n"));
 	printf(_("  --section=SECTION            dump named section (pre-data, data, or post-data)\n"));
 	printf(_("  --serializable-deferrable    wait until the dump can run without anomalies\n"));
@@ -10252,6 +10279,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
 		case DO_SUBSCRIPTION:
 			dumpSubscription(fout, (const SubscriptionInfo *) dobj);
 			break;
+		case DO_REPLICATION_SLOT:
+			dumpReplicationSlot(fout, (const ReplicationSlotInfo *) dobj);
+			break;
 		case DO_PRE_DATA_BOUNDARY:
 		case DO_POST_DATA_BOUNDARY:
 			/* never dumped, nothing to do */
@@ -18227,6 +18257,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
 			case DO_PUBLICATION_REL:
 			case DO_PUBLICATION_TABLE_IN_SCHEMA:
 			case DO_SUBSCRIPTION:
+			case DO_REPLICATION_SLOT:
 				/* Post-data objects: must come after the post-data boundary */
 				addObjectDependency(dobj, postDataBound->dumpId);
 				break;
@@ -18488,3 +18519,113 @@ appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
 	if (!res)
 		pg_log_warning("could not parse %s array", "reloptions");
 }
+
+/*
+ * getReplicationSlots
+ *	  get information about replication slots
+ */
+static void
+getReplicationSlots(Archive *fout)
+{
+	PGresult   *res;
+	ReplicationSlotInfo *slotinfo;
+	PQExpBuffer query;
+	DumpOptions *dopt = fout->dopt;
+
+	int			i_slotname;
+	int			i_plugin;
+	int			i_twophase;
+	int			i,
+				ntups;
+
+	/* Check whether we should dump or not */
+	if (fout->remoteVersion < 160000 || !dopt->slot_only)
+		return;
+
+	query = createPQExpBuffer();
+
+	resetPQExpBuffer(query);
+
+	/*
+	 * Get replication slots.
+	 *
+	 * XXX: Which information must be extracted from old node? Currently three
+	 * attributes are extracted because they are used by
+	 * pg_create_logical_replication_slot().
+	 * XXX: Do we have to support physical slots?
+	 */
+	appendPQExpBufferStr(query,
+						 "SELECT r.slot_name, r.plugin, r.two_phase "
+						 "FROM pg_replication_slots r "
+						 "WHERE r.database = current_database() AND temporary = false "
+						 "AND wal_status IN ('reserved', 'extended');");
+
+	res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+
+	ntups = PQntuples(res);
+
+	i_slotname = PQfnumber(res, "slot_name");
+	i_plugin = PQfnumber(res, "plugin");
+	i_twophase = PQfnumber(res, "two_phase");
+
+	slotinfo = pg_malloc(ntups * sizeof(ReplicationSlotInfo));
+
+	for (i = 0; i < ntups; i++)
+	{
+		slotinfo[i].dobj.objType = DO_REPLICATION_SLOT;
+
+		slotinfo[i].dobj.catId.tableoid = InvalidOid;
+		slotinfo[i].dobj.catId.oid = InvalidOid;
+		AssignDumpId(&slotinfo[i].dobj);
+
+		slotinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_slotname));
+
+		slotinfo[i].plugin = pg_strdup(PQgetvalue(res, i, i_plugin));
+		slotinfo[i].twophase = pg_strdup(PQgetvalue(res, i, i_twophase));
+
+		/* FIXME: force dumping */
+		slotinfo[i].dobj.dump = DUMP_COMPONENT_ALL;
+	}
+	PQclear(res);
+
+	destroyPQExpBuffer(query);
+}
+
+/*
+ * dumpReplicationSlot
+ *	  write down a script for pg_restore command
+ */
+static void
+dumpReplicationSlot(Archive *fout, const ReplicationSlotInfo * slotinfo)
+{
+	DumpOptions *dopt = fout->dopt;
+
+	if (!dopt->slot_only)
+		return;
+
+	if (slotinfo->dobj.dump & DUMP_COMPONENT_DEFINITION)
+	{
+		PQExpBuffer query = createPQExpBuffer();
+		char	   *slotname = pg_strdup(slotinfo->dobj.name);
+
+		/*
+		 * XXX: For simplification, pg_create_logical_replication_slot() is
+		 * used. Is it sufficient?
+		 */
+		appendPQExpBuffer(query, "SELECT pg_create_logical_replication_slot('%s', ",
+						  slotname);
+		appendStringLiteralAH(query, slotinfo->plugin, fout);
+		appendPQExpBuffer(query, ", ");
+		appendStringLiteralAH(query, slotinfo->twophase, fout);
+		appendPQExpBuffer(query, ");");
+
+		ArchiveEntry(fout, slotinfo->dobj.catId, slotinfo->dobj.dumpId,
+					 ARCHIVE_OPTS(.tag = slotname,
+								  .description = "REPLICATION SLOT",
+								  .section = SECTION_POST_DATA,
+								  .createStmt = query->data));
+
+		pfree(slotname);
+		destroyPQExpBuffer(query);
+	}
+}
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index ed6ce41ad7..e59cfdd8fa 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -82,7 +82,8 @@ typedef enum
 	DO_PUBLICATION,
 	DO_PUBLICATION_REL,
 	DO_PUBLICATION_TABLE_IN_SCHEMA,
-	DO_SUBSCRIPTION
+	DO_SUBSCRIPTION,
+	DO_REPLICATION_SLOT
 } DumpableObjectType;
 
 /*
@@ -666,6 +667,18 @@ typedef struct _SubscriptionInfo
 	char	   *subpasswordrequired;
 } SubscriptionInfo;
 
+/*
+ * The ReplicationSlotInfo struct is used to represent replication slots.
+ * XXX: add more attrbutes if needed
+ */
+typedef struct _ReplicationSlotInfo
+{
+	DumpableObject dobj;
+	char	   *plugin;
+	char	   *slottype;
+	char	   *twophase;
+}			ReplicationSlotInfo;
+
 /*
  *	common utility functions
  */
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 8266c117a3..4280283f0d 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -1497,6 +1497,10 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
 			snprintf(buf, bufsize,
 					 "SUBSCRIPTION (ID %d OID %u)",
 					 obj->dumpId, obj->catId.oid);
+		case DO_REPLICATION_SLOT:
+			snprintf(buf, bufsize,
+					 "REPLICATION SLOT (ID %d NAME %s)",
+					 obj->dumpId, obj->name);
 			return;
 		case DO_PRE_DATA_BOUNDARY:
 			snprintf(buf, bufsize,
diff --git a/src/bin/pg_upgrade/dump.c b/src/bin/pg_upgrade/dump.c
index 6c8c82dca8..f8d0c6ddde 100644
--- a/src/bin/pg_upgrade/dump.c
+++ b/src/bin/pg_upgrade/dump.c
@@ -59,6 +59,29 @@ generate_old_dump(void)
 						   log_opts.dumpdir,
 						   sql_file_name, escaped_connstr.data);
 
+		/*
+		 * Dump replication slots if needed.
+		 *
+		 * XXX We cannot dump replication slots at the same time as the schema
+		 * dump because we need to separate the timing of restoring
+		 * replication slots and other objects. Replication slots, in
+		 * particular, should not be restored before executing the pg_resetwal
+		 * command because it will remove WALs that are required by the slots.
+		 */
+		if (user_opts.include_slots)
+		{
+			char		slots_file_name[MAXPGPATH];
+
+			snprintf(slots_file_name, sizeof(slots_file_name), DB_DUMP_SLOTS_FILE_MASK, old_db->db_oid);
+			parallel_exec_prog(log_file_name, NULL,
+							   "\"%s/pg_dump\" %s --replication-slots-only "
+							   "--quote-all-identifiers --binary-upgrade %s "
+							   "--file=\"%s/%s\" %s",
+							   new_cluster.bindir, cluster_conn_opts(&old_cluster),
+							   log_opts.verbose ? "--verbose" : "",
+							   log_opts.dumpdir,
+							   slots_file_name, escaped_connstr.data);
+		}
 		termPQExpBuffer(&escaped_connstr);
 	}
 
diff --git a/src/bin/pg_upgrade/meson.build b/src/bin/pg_upgrade/meson.build
index 12a97f84e2..7f5d48b7e1 100644
--- a/src/bin/pg_upgrade/meson.build
+++ b/src/bin/pg_upgrade/meson.build
@@ -42,6 +42,7 @@ tests += {
     'tests': [
       't/001_basic.pl',
       't/002_pg_upgrade.pl',
+      't/003_logical_replication.pl',
     ],
     'test_kwargs': {'priority': 40}, # pg_upgrade tests are slow
   },
diff --git a/src/bin/pg_upgrade/option.c b/src/bin/pg_upgrade/option.c
index 8869b6b60d..d8d9f69b47 100644
--- a/src/bin/pg_upgrade/option.c
+++ b/src/bin/pg_upgrade/option.c
@@ -57,6 +57,7 @@ parseCommandLine(int argc, char *argv[])
 		{"verbose", no_argument, NULL, 'v'},
 		{"clone", no_argument, NULL, 1},
 		{"copy", no_argument, NULL, 2},
+		{"include-replication-slots", no_argument, NULL, 3},
 
 		{NULL, 0, NULL, 0}
 	};
@@ -199,6 +200,10 @@ parseCommandLine(int argc, char *argv[])
 				user_opts.transfer_mode = TRANSFER_MODE_COPY;
 				break;
 
+			case 3:
+				user_opts.include_slots = true;
+				break;
+
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
 						os_info.progname);
@@ -289,6 +294,7 @@ usage(void)
 	printf(_("  -V, --version                 display version information, then exit\n"));
 	printf(_("  --clone                       clone instead of copying files to new cluster\n"));
 	printf(_("  --copy                        copy files to new cluster (default)\n"));
+	printf(_("  --include-replication-slots   upgrade replication slots\n"));
 	printf(_("  -?, --help                    show this help, then exit\n"));
 	printf(_("\n"
 			 "Before running pg_upgrade you must:\n"
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 75bab0a04c..04bf1d867a 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -59,6 +59,7 @@ static void copy_xact_xlog_xid(void);
 static void set_frozenxids(bool minmxid_only);
 static void make_outputdirs(char *pgdata);
 static void setup(char *argv0, bool *live_check);
+static void create_replication_slots(void);
 
 ClusterInfo old_cluster,
 			new_cluster;
@@ -188,6 +189,19 @@ main(int argc, char **argv)
 			  new_cluster.pgdata);
 	check_ok();
 
+	/*
+	 * Create replication slots if requested.
+	 *
+	 * Note: This must be done after doing pg_resetwal command because the
+	 * command will remove required WALs.
+	 */
+	if (user_opts.include_slots)
+	{
+		start_postmaster(&new_cluster, true);
+		create_replication_slots();
+		stop_postmaster(false);
+	}
+
 	if (user_opts.do_sync)
 	{
 		prep_status("Sync data directory to disk");
@@ -860,3 +874,50 @@ set_frozenxids(bool minmxid_only)
 
 	check_ok();
 }
+
+/*
+ * create_replication_slots()
+ *
+ * Similar to create_new_objects() but only restores replication slots.
+ */
+static void
+create_replication_slots(void)
+{
+	int			dbnum;
+
+	prep_status_progress("Restoring replication slots in the new cluster");
+
+	for (dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
+	{
+		char		slots_file_name[MAXPGPATH],
+					log_file_name[MAXPGPATH];
+		DbInfo	   *old_db = &old_cluster.dbarr.dbs[dbnum];
+
+		pg_log(PG_STATUS, "%s", old_db->db_name);
+
+		snprintf(slots_file_name, sizeof(slots_file_name),
+				 DB_DUMP_SLOTS_FILE_MASK, old_db->db_oid);
+		snprintf(log_file_name, sizeof(log_file_name),
+				 DB_DUMP_LOG_FILE_MASK, old_db->db_oid);
+
+		parallel_exec_prog(log_file_name,
+						   NULL,
+						   "\"%s/psql\" %s --echo-queries --set ON_ERROR_STOP=on "
+						   "--no-psqlrc --dbname %s -f \"%s/%s\"",
+						   new_cluster.bindir,
+						   cluster_conn_opts(&new_cluster),
+						   old_db->db_name,
+						   log_opts.dumpdir,
+						   slots_file_name);
+	}
+
+	/* reap all children */
+	while (reap_child(true) == true)
+		;
+
+	end_progress_output();
+	check_ok();
+
+	/* update new_cluster info now that we have objects in the databases */
+	get_db_and_rel_infos(&new_cluster);
+}
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 3eea0139c7..1aa41f68bc 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -29,6 +29,7 @@
 /* contains both global db information and CREATE DATABASE commands */
 #define GLOBALS_DUMP_FILE	"pg_upgrade_dump_globals.sql"
 #define DB_DUMP_FILE_MASK	"pg_upgrade_dump_%u.custom"
+#define DB_DUMP_SLOTS_FILE_MASK	"pg_upgrade_dump_%u_slots.sql"
 
 /*
  * Base directories that include all the files generated internally, from the
@@ -304,6 +305,7 @@ typedef struct
 	transferMode transfer_mode; /* copy files or link them? */
 	int			jobs;			/* number of processes/threads to use */
 	char	   *socketdir;		/* directory to use for Unix sockets */
+	bool		include_slots;	/* true -> dump and restore replication slots */
 } UserOpts;
 
 typedef struct
diff --git a/src/bin/pg_upgrade/t/003_logical_replication.pl b/src/bin/pg_upgrade/t/003_logical_replication.pl
new file mode 100644
index 0000000000..13ddde3d5f
--- /dev/null
+++ b/src/bin/pg_upgrade/t/003_logical_replication.pl
@@ -0,0 +1,89 @@
+# Copyright (c) 2021-2023, PostgreSQL Global Development Group
+
+# Tests for logical replication, especially for upgrading publisher
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Can be changed to test the other modes.
+my $mode = $ENV{PG_TEST_PG_UPGRADE_MODE} || '--copy';
+
+# Initialize publisher node
+my $old_publisher = PostgreSQL::Test::Cluster->new('old_publisher');
+$old_publisher->init(allows_streaming => 'logical');
+$old_publisher->start;
+
+# Initialize subscriber node
+my $subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$subscriber->init(allows_streaming => 'logical');
+$subscriber->start;
+
+$old_publisher->safe_psql('postgres',
+	"CREATE TABLE tbl AS SELECT generate_series(1,10) AS a");
+$subscriber->safe_psql('postgres', "CREATE TABLE tbl (a int)");
+
+# Setup logical replication
+my $old_connstr = $old_publisher->connstr . ' dbname=postgres';
+$old_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION pub FOR ALL TABLES");
+$subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sub CONNECTION '$old_connstr' PUBLICATION pub");
+
+# Wait for initial table sync to finish
+$subscriber->wait_for_subscription_sync($old_publisher, 'sub');
+
+my $result = $subscriber->safe_psql('postgres', "SELECT count(*) FROM tbl");
+is($result, qq(10), 'check initial rows on subscriber');
+
+# Preparations for upgrading publisher
+$old_publisher->stop;
+$subscriber->safe_psql('postgres', "ALTER SUBSCRIPTION sub DISABLE");
+
+my $new_publisher = PostgreSQL::Test::Cluster->new('new_publisher');
+$new_publisher->init(allows_streaming => 'logical');
+
+my $bindir = $new_publisher->config_data('--bindir');
+
+# Run pg_upgrade. pg_upgrade_output.d is removed at the end
+command_ok(
+	[
+		'pg_upgrade', '--no-sync',
+		'-d',         $old_publisher->data_dir,
+		'-D',         $new_publisher->data_dir,
+		'-b',         $bindir,
+		'-B',         $bindir,
+		'-s',         $new_publisher->host,
+		'-p',         $old_publisher->port,
+		'-P',         $new_publisher->port,
+		$mode,        '--include-replication-slot'
+	],
+	'run of pg_upgrade for new publisher');
+ok( !-d $new_publisher->data_dir . "/pg_upgrade_output.d",
+	"pg_upgrade_output.d/ removed after pg_upgrade success");
+
+# Check whether the replication slot is copied
+$new_publisher->start;
+$result =
+  $new_publisher->safe_psql('postgres',
+	"SELECT count(*) FROM pg_replication_slots");
+is($result, qq(1), 'check the replication slot is copied to new publisher');
+
+# Change connection string and enable logical replication
+my $new_connstr = $new_publisher->connstr . ' dbname=postgres';
+
+$subscriber->safe_psql('postgres',
+	"ALTER SUBSCRIPTION sub CONNECTION '$new_connstr'");
+$subscriber->safe_psql('postgres', "ALTER SUBSCRIPTION sub ENABLE");
+
+# Check whether changes on the new publisher get replicated to the subscriber
+$new_publisher->safe_psql('postgres',
+	"INSERT INTO tbl VALUES (generate_series(11, 20))");
+
+$new_publisher->wait_for_catchup('sub');
+
+$result = $subscriber->safe_psql('postgres', "SELECT count(*) FROM tbl");
+is($result, qq(20), 'check changes are shipped to subscriber');
+
+done_testing();
-- 
2.27.0



^ permalink  raw  reply  [nested|flat] 23+ messages in thread

* Re: [PoC] pg_upgrade: allow to upgrade publisher node
  2023-04-07 13:59 RE: [PoC] pg_upgrade: allow to upgrade publisher node Hayato Kuroda (Fujitsu) <[email protected]>
@ 2023-04-11 08:20 ` Peter Smith <[email protected]>
  0 siblings, 0 replies; 23+ messages in thread

From: Peter Smith @ 2023-04-11 08:20 UTC (permalink / raw)
  To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Julien Rouhaud <[email protected]>; Amit Kapila <[email protected]>

On Sat, Apr 8, 2023 at 12:00 AM Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
>
...
> > 17. main
> >
> > + /*
> > + * Create replication slots if requested.
> > + *
> > + * XXX This must be done after doing pg_resetwal command because the
> > + * command will remove required WALs.
> > + */
> > + if (user_opts.include_slots)
> > + {
> > + start_postmaster(&new_cluster, true);
> > + create_replicaiton_slots();
> > + stop_postmaster(false);
> > + }
> > +
> >
> > I don't think that warrants a "XXX" style comment. It is just a "Note:".
>
> Fixed. Could you please tell me the classification of them if you can?

Hopefully, someone will correct me if this explanation is wrong, but
my understanding of the different prefixes is like this --

"XXX" is used as a marker for future developers to consider maybe
revisiting/improving something that the comment refers to
e.g.
/* XXX - it would be better to code this using blah but for now we did
not.... */
/* XXX - option 'foo' is not currently supported but... */
/* XXX - it might be worth considering adding more checks or an assert
here because... */

OTOH, "Note" is just for highlighting why something is the way it is,
but with no implication that it should be revisited/changed in the
future.
e.g.
/* Note: We deliberately do not test the state here because... */
/* Note: This memory must be zeroed because... */
/* Note: This string has no '\0' terminator so... */

------
Kind Regards,
Peter Smith.
Fujitsu Australia






^ permalink  raw  reply  [nested|flat] 23+ messages in thread

end of thread, other threads:[~2023-04-11 08:20 UTC | newest]

Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2021-01-20 11:57 [PATCH v5 1/3] Test for snapshot too old and wal_level=minimal Kyotaro Horiguchi <[email protected]>
2023-04-07 13:59 RE: [PoC] pg_upgrade: allow to upgrade publisher node Hayato Kuroda (Fujitsu) <[email protected]>
2023-04-11 08:20 ` Re: [PoC] pg_upgrade: allow to upgrade publisher node Peter Smith <[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