($INBOX_DIR/description missing)  

help / color / mirror / Atom feed

[PATCH v5 1/3] Test for snapshot too old and wal_level=minimal
22+ messages / 2 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ 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; 22+ 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] 22+ messages in thread

* How about a psql backslash command to show GUCs?
@ 2022-04-06 17:48  Tom Lane <[email protected]>
  0 siblings, 0 replies; 22+ messages in thread

From: Tom Lane @ 2022-04-06 17:48 UTC (permalink / raw)
  To: [email protected]; +Cc: Mark Dilger <[email protected]>

It's not difficult to get psql to show you the current value
of a single GUC --- "SHOW" does that fine, and it has tab
completion support for the GUC name.  However, I very often
find myself resorting to the much more tedious

select * from pg_settings where name like '%foo%';

when I want to see some related parameters, or when I'm a bit
fuzzy on the exact name of the parameter.  Not only is this
a lot of typing, but unless I'm willing to type even more to
avoid using "*", I'll get a wall of mostly unreadable text,
because pg_settings is far too wide and cluttered with
low-grade information.

In the discussion about adding privileges for GUCs [1], there
was a proposal to add a new psql backslash command to show GUCs,
which could reduce this problem to something like

\dcp *foo*

(The version proposed there was not actually useful for this
purpose because it was too narrowly focused on GUCs with
privileges, but that's easily fixed.)

So does anyone else like this idea?

In detail, I'd imagine this command showing the name, setting, unit,
and vartype fields of pg_setting by default, and if you add "+"
then it should add the context field, as well as applicable
privileges when server version >= 15.  However, there's plenty
of room for bikeshedding that list of columns, not to mention
the precise name of the command.  (I'm not that thrilled with
"\dcp" myself, as it looks like it might be a sub-form of "\dc".)
So I thought I'd solicit comments before working on a patch
not after.

I view this as being at least in part mop-up for commit a0ffa885e,
especially since a form of this was discussed in that thread.
So I don't think it'd be unreasonable to push into v15, even
though it's surely a new feature.

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/[email protected]






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

end of thread, other threads:[~2022-04-06 17:48 UTC | newest]

Thread overview: 22+ 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]>
2022-04-06 17:48 How about a psql backslash command to show GUCs? Tom Lane <[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