On Tue, Mar 1, 2016 at 12:58 AM, Michael Paquier
<michael.paqu...@gmail.com> wrote:
> On Tue, Mar 1, 2016 at 9:35 AM, Andres Freund <and...@anarazel.de> wrote:
>> On 2016-02-29 18:30:27 -0600, Kevin Grittner wrote:
>>> Basically, a connection needs to remain open and interleave
>>> commands with other connections, which the isolation tester does
>>> just fine; but it needs to do that using a custom postgresql.conf
>>> file, which TAP does just fine. I haven't been able to see the
>>> right way to get a TAP test to set up a customized installation to
>>> run isolation tests against. If I can get that working, I have
>>> additional tests I can drop into that.
>> Check contrib/test_decoding's makefile. It does just that with
>> isolationtester.
>
> pg_isolation_regress --temp-config is the key item here, you can
> enforce a test to run on a server with a wanted configuration set.
Thanks for the tips. Attached is a minimal set of isolation tests.
I can expand on it if needed, but wanted:
(1) to confirm that this is the right way to do this, and
(2) how long people were willing to tolerate these tests running.
Since we're making this time-based (by popular demand), there must
be delays to see the new behavior. This very minimal pair of tests
runs in just under one minute on my i7. Decent coverage of all the
index AMs would probably require tests which run for at least 10
minutes, and probably double that. I don't recall any satisfactory
resolution to prior discussions about long-running tests.
This is a follow-on patch, just to add isolation testing; the prior
patch must be applied, too.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 6167ec1..9b93552 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -8,6 +8,7 @@ SUBDIRS = \
brin \
commit_ts \
dummy_seclabel \
+ snapshot_too_old \
test_ddl_deparse \
test_extensions \
test_parser \
diff --git a/src/test/modules/snapshot_too_old/Makefile b/src/test/modules/snapshot_too_old/Makefile
new file mode 100644
index 0000000..7b9feca
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/Makefile
@@ -0,0 +1,47 @@
+# src/test/modules/snapshot_too_old/Makefile
+
+EXTRA_CLEAN = ./isolation_output
+
+ISOLATIONCHECKS=sto_using_cursor sto_using_select
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/snapshot_too_old
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+# Disabled because these tests require "old_snapshot_threshold" >= 0, which
+# typical installcheck users do not have (e.g. buildfarm clients).
+installcheck:;
+
+# But it can nonetheless be very helpful to run tests on preexisting
+# installation, allow to do so, but only if requested explicitly.
+installcheck-force: isolationcheck-install-force
+
+check: isolationcheck
+
+submake-isolation:
+ $(MAKE) -C $(top_builddir)/src/test/isolation all
+
+submake-test_decoding:
+ $(MAKE) -C $(top_builddir)/src/test/modules/snapshot_too_old
+
+isolationcheck: | submake-isolation temp-install
+ $(MKDIR_P) isolation_output
+ $(pg_isolation_regress_check) \
+ --temp-config $(top_srcdir)/src/test/modules/snapshot_too_old/sto.conf \
+ --outputdir=./isolation_output \
+ $(ISOLATIONCHECKS)
+
+isolationcheck-install-force: all | submake-isolation temp-install
+ $(pg_isolation_regress_installcheck) \
+ $(ISOLATIONCHECKS)
+
+PHONY: check isolationcheck isolationcheck-install-force
+
+temp-install: EXTRA_INSTALL=src/test/modules/snapshot_too_old
diff --git a/src/test/modules/snapshot_too_old/expected/sto_using_cursor.out b/src/test/modules/snapshot_too_old/expected/sto_using_cursor.out
new file mode 100644
index 0000000..8cc29ec
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_using_cursor.out
@@ -0,0 +1,73 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1decl s1f1 s1sleep s1f2 s2u
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: FETCH FIRST FROM cursor1;
+c
+
+1
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+
+starting permutation: s1decl s1f1 s1sleep s2u s1f2
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1f2: FETCH FIRST FROM cursor1;
+ERROR: snapshot too old
+
+starting permutation: s1decl s1f1 s2u s1sleep s1f2
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+1
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: FETCH FIRST FROM cursor1;
+ERROR: snapshot too old
+
+starting permutation: s1decl s2u s1f1 s1sleep s1f2
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: FETCH FIRST FROM cursor1;
+ERROR: snapshot too old
+
+starting permutation: s2u s1decl s1f1 s1sleep s1f2
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+2
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: FETCH FIRST FROM cursor1;
+ERROR: snapshot too old
diff --git a/src/test/modules/snapshot_too_old/expected/sto_using_select.out b/src/test/modules/snapshot_too_old/expected/sto_using_select.out
new file mode 100644
index 0000000..eb15bc2
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/expected/sto_using_select.out
@@ -0,0 +1,55 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1f1 s1sleep s1f2 s2u
+step s1f1: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+1
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+
+starting permutation: s1f1 s1sleep s2u s1f2
+step s1f1: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1f2: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+ERROR: snapshot too old
+
+starting permutation: s1f1 s2u s1sleep s1f2
+step s1f1: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+1
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+ERROR: snapshot too old
+
+starting permutation: s2u s1f1 s1sleep s1f2
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1f1: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+2
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+ERROR: snapshot too old
diff --git a/src/test/modules/snapshot_too_old/results/sto_using_cursor.out b/src/test/modules/snapshot_too_old/results/sto_using_cursor.out
new file mode 100644
index 0000000..8cc29ec
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/results/sto_using_cursor.out
@@ -0,0 +1,73 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1decl s1f1 s1sleep s1f2 s2u
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: FETCH FIRST FROM cursor1;
+c
+
+1
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+
+starting permutation: s1decl s1f1 s1sleep s2u s1f2
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1f2: FETCH FIRST FROM cursor1;
+ERROR: snapshot too old
+
+starting permutation: s1decl s1f1 s2u s1sleep s1f2
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+1
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: FETCH FIRST FROM cursor1;
+ERROR: snapshot too old
+
+starting permutation: s1decl s2u s1f1 s1sleep s1f2
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: FETCH FIRST FROM cursor1;
+ERROR: snapshot too old
+
+starting permutation: s2u s1decl s1f1 s1sleep s1f2
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1decl: DECLARE cursor1 CURSOR FOR SELECT c FROM sto1;
+step s1f1: FETCH FIRST FROM cursor1;
+c
+
+2
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: FETCH FIRST FROM cursor1;
+ERROR: snapshot too old
diff --git a/src/test/modules/snapshot_too_old/results/sto_using_select.out b/src/test/modules/snapshot_too_old/results/sto_using_select.out
new file mode 100644
index 0000000..eb15bc2
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/results/sto_using_select.out
@@ -0,0 +1,55 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1f1 s1sleep s1f2 s2u
+step s1f1: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+1
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+
+starting permutation: s1f1 s1sleep s2u s1f2
+step s1f1: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+1
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1f2: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+ERROR: snapshot too old
+
+starting permutation: s1f1 s2u s1sleep s1f2
+step s1f1: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+1
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+ERROR: snapshot too old
+
+starting permutation: s2u s1f1 s1sleep s1f2
+step s2u: UPDATE sto1 SET c = 1001 WHERE c = 1;
+step s1f1: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+c
+
+2
+step s1sleep: SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold';
+setting pg_sleep
+
+0
+step s1f2: SELECT c FROM sto1 ORDER BY c LIMIT 1;
+ERROR: snapshot too old
diff --git a/src/test/modules/snapshot_too_old/specs/sto_using_cursor.spec b/src/test/modules/snapshot_too_old/specs/sto_using_cursor.spec
new file mode 100644
index 0000000..eac18ca
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/specs/sto_using_cursor.spec
@@ -0,0 +1,37 @@
+# This test provokes a "snapshot too old" error using a cursor.
+#
+# 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 TABLE sto1 (c int NOT NULL);
+ INSERT INTO sto1 SELECT generate_series(1, 1000);
+ CREATE TABLE sto2 (c int NOT NULL);
+}
+setup
+{
+ VACUUM ANALYZE sto1;
+}
+
+teardown
+{
+ DROP TABLE sto1, sto2;
+}
+
+session "s1"
+setup { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s1decl" { DECLARE cursor1 CURSOR FOR SELECT c FROM sto1; }
+step "s1f1" { FETCH FIRST FROM cursor1; }
+step "s1sleep" { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "s1f2" { FETCH FIRST FROM cursor1; }
+teardown { COMMIT; }
+
+session "s2"
+step "s2u" { UPDATE sto1 SET c = 1001 WHERE c = 1; }
diff --git a/src/test/modules/snapshot_too_old/specs/sto_using_select.spec b/src/test/modules/snapshot_too_old/specs/sto_using_select.spec
new file mode 100644
index 0000000..d7c34f3
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/specs/sto_using_select.spec
@@ -0,0 +1,36 @@
+# This test provokes a "snapshot too old" error using SELECT statements.
+#
+# 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 TABLE sto1 (c int NOT NULL);
+ INSERT INTO sto1 SELECT generate_series(1, 1000);
+ CREATE TABLE sto2 (c int NOT NULL);
+}
+setup
+{
+ VACUUM ANALYZE sto1;
+}
+
+teardown
+{
+ DROP TABLE sto1, sto2;
+}
+
+session "s1"
+setup { BEGIN ISOLATION LEVEL REPEATABLE READ; }
+step "s1f1" { SELECT c FROM sto1 ORDER BY c LIMIT 1; }
+step "s1sleep" { SELECT setting, pg_sleep(6) FROM pg_settings WHERE name = 'old_snapshot_threshold'; }
+step "s1f2" { SELECT c FROM sto1 ORDER BY c LIMIT 1; }
+teardown { COMMIT; }
+
+session "s2"
+step "s2u" { UPDATE sto1 SET c = 1001 WHERE c = 1; }
diff --git a/src/test/modules/snapshot_too_old/sto.conf b/src/test/modules/snapshot_too_old/sto.conf
new file mode 100644
index 0000000..ce8048f
--- /dev/null
+++ b/src/test/modules/snapshot_too_old/sto.conf
@@ -0,0 +1,3 @@
+autovacuum = off
+old_snapshot_threshold = 0
+
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
