add test: pg_rowlocks extension
Started by Dong Wook Leeover 3 years ago6 messages
Hi Hackers,
I just wrote test about pg_rowlocks extension.
I added sql and spec test for locking state.
---
Regards
DongWook Lee
Attachments:
001_add_test_pgrowlocks.patchtext/x-diff; charset=us-asciiDownload
diff --git a/contrib/pgrowlocks/Makefile b/contrib/pgrowlocks/Makefile
index 294c05dd0f..128f345b7e 100644
--- a/contrib/pgrowlocks/Makefile
+++ b/contrib/pgrowlocks/Makefile
@@ -9,6 +9,9 @@ EXTENSION = pgrowlocks
DATA = pgrowlocks--1.2.sql pgrowlocks--1.1--1.2.sql pgrowlocks--1.0--1.1.sql
PGFILEDESC = "pgrowlocks - display row locking information"
+REGRESS = pgrowlocks
+ISOLATION = show_rowlocks_info
+
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/pgrowlocks/expected/pgrowlocks.out b/contrib/pgrowlocks/expected/pgrowlocks.out
new file mode 100644
index 0000000000..9daf600e50
--- /dev/null
+++ b/contrib/pgrowlocks/expected/pgrowlocks.out
@@ -0,0 +1,28 @@
+--
+-- Test pgrowlocks extension
+--
+CREATE EXTENSION pgrowlocks;
+-- set up
+CREATE TABLE test(c int);
+-- check permission to use pgrowlocks extension;
+CREATE ROLE regress_priv_user1 superuser login;
+CREATE ROLE regress_priv_user2;
+CREATE ROLE regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user1;
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- ok
+ locked_row | locker | multi | xids | modes
+------------+--------+-------+------+-------
+(0 rows)
+
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- fail
+ERROR: permission denied for table test
+-- switch to superuser
+\c -
+GRANT SELECT ON test TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user3;
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- ok
+ locked_row | locker | multi | xids | modes
+------------+--------+-------+------+-------
+(0 rows)
+
diff --git a/contrib/pgrowlocks/expected/show_rowlocks_info.out b/contrib/pgrowlocks/expected/show_rowlocks_info.out
new file mode 100644
index 0000000000..7f06f55785
--- /dev/null
+++ b/contrib/pgrowlocks/expected/show_rowlocks_info.out
@@ -0,0 +1,17 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1b s1u1 s2_rowlocks s1c s2_rowlocks
+step s1b: BEGIN;
+step s1u1: UPDATE t1 SET c1 = 2 WHERE c1 = 1;
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('t1');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"No Key Update"}
+(1 row)
+
+step s1c: COMMIT;
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('t1');
+locked_row|multi|modes
+----------+-----+-----
+(0 rows)
+
diff --git a/contrib/pgrowlocks/output_iso/results/show_rowlocks_info.out b/contrib/pgrowlocks/output_iso/results/show_rowlocks_info.out
new file mode 100644
index 0000000000..7f06f55785
--- /dev/null
+++ b/contrib/pgrowlocks/output_iso/results/show_rowlocks_info.out
@@ -0,0 +1,17 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1b s1u1 s2_rowlocks s1c s2_rowlocks
+step s1b: BEGIN;
+step s1u1: UPDATE t1 SET c1 = 2 WHERE c1 = 1;
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('t1');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"No Key Update"}
+(1 row)
+
+step s1c: COMMIT;
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('t1');
+locked_row|multi|modes
+----------+-----+-----
+(0 rows)
+
diff --git a/contrib/pgrowlocks/results/pgrowlocks.out b/contrib/pgrowlocks/results/pgrowlocks.out
new file mode 100644
index 0000000000..9daf600e50
--- /dev/null
+++ b/contrib/pgrowlocks/results/pgrowlocks.out
@@ -0,0 +1,28 @@
+--
+-- Test pgrowlocks extension
+--
+CREATE EXTENSION pgrowlocks;
+-- set up
+CREATE TABLE test(c int);
+-- check permission to use pgrowlocks extension;
+CREATE ROLE regress_priv_user1 superuser login;
+CREATE ROLE regress_priv_user2;
+CREATE ROLE regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user1;
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- ok
+ locked_row | locker | multi | xids | modes
+------------+--------+-------+------+-------
+(0 rows)
+
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- fail
+ERROR: permission denied for table test
+-- switch to superuser
+\c -
+GRANT SELECT ON test TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user3;
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- ok
+ locked_row | locker | multi | xids | modes
+------------+--------+-------+------+-------
+(0 rows)
+
diff --git a/contrib/pgrowlocks/specs/show_rowlocks_info.spec b/contrib/pgrowlocks/specs/show_rowlocks_info.spec
new file mode 100644
index 0000000000..0d5d629702
--- /dev/null
+++ b/contrib/pgrowlocks/specs/show_rowlocks_info.spec
@@ -0,0 +1,27 @@
+setup
+{ CREATE EXTENSION pgrowlocks;
+
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(c1 integer);
+
+ INSERT INTO t1 (c1) VALUES (1);
+}
+
+teardown
+{
+ DROP TABLE t1;
+}
+
+session "s1"
+
+step "s1b" { BEGIN; }
+step "s1u1" { UPDATE t1 SET c1 = 2 WHERE c1 = 1; }
+step "s1c" { COMMIT; }
+
+session "s2"
+
+step "s2_rowlocks" { select locked_row, multi, modes from pgrowlocks('t1'); }
+
+
+permutation "s1b" "s1u1" "s2_rowlocks" "s1c" "s2_rowlocks"
+
diff --git a/contrib/pgrowlocks/sql/pgrowlocks.sql b/contrib/pgrowlocks/sql/pgrowlocks.sql
new file mode 100644
index 0000000000..c9ef30a058
--- /dev/null
+++ b/contrib/pgrowlocks/sql/pgrowlocks.sql
@@ -0,0 +1,30 @@
+--
+-- Test pgrowlocks extension
+--
+
+CREATE EXTENSION pgrowlocks;
+
+-- set up
+CREATE TABLE test(c int);
+
+-- check permission to use pgrowlocks extension;
+
+CREATE ROLE regress_priv_user1 superuser login;
+CREATE ROLE regress_priv_user2;
+CREATE ROLE regress_priv_user3;
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- ok
+
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- fail
+
+-- switch to superuser
+\c -
+
+GRANT SELECT ON test TO regress_priv_user3;
+
+SET SESSION AUTHORIZATION regress_priv_user3;
+SELECT locked_row, locker, multi, xids, modes FROM pgrowlocks('test'); -- ok
+
Re: add test: pg_rowlocks extension
Dong Wook Lee <sh95119@gmail.com> writes:
I just wrote test about pg_rowlocks extension.
I added sql and spec test for locking state.
I think this could be cut down quite a bit. Do we really need
both a SQL test and an isolation test? Seems like you could
easily do everything in the isolation test.
Also, it is not a good idea to go creating superusers in a contrib
test: we support "make installcheck" for these tests, but people don't
especially like new superusers cropping up in their installations.
I doubt that we need *any* of the permissions-ish tests that you
propose adding here; those are not part of the module's own
functionality, and we don't generally have similar tests in other
contrib modules.
If you do keep any of it, remember to drop the roles you create ---
leaving global objects behind is not OK. (For one thing, it
breaks doing repeat "make installcheck"s.)
Another thing that's bad style is the "drop table if exists".
This should be running in an empty database, and if somehow it's
not, destroying pre-existing objects would be pretty unfriendly.
Better to fail at the CREATE.
See also my comments about your pg_buffercache patch, which
largely apply here too.
regards, tom lane
Re: add test: pg_rowlocks extension
2022년 7월 31일 (일) 오전 6:32, Tom Lane <tgl@sss.pgh.pa.us>님이 작성:
Dong Wook Lee <sh95119@gmail.com> writes:
I just wrote test about pg_rowlocks extension.
I added sql and spec test for locking state.I think this could be cut down quite a bit. Do we really need
both a SQL test and an isolation test? Seems like you could
easily do everything in the isolation test.
I agree with your optionion.
Also, it is not a good idea to go creating superusers in a contrib
test: we support "make installcheck" for these tests, but people don't
especially like new superusers cropping up in their installations.
I doubt that we need *any* of the permissions-ish tests that you
propose adding here; those are not part of the module's own
functionality, and we don't generally have similar tests in other
contrib modules.
I agree it's right to remove that part.
If you do keep any of it, remember to drop the roles you create ---
leaving global objects behind is not OK. (For one thing, it
breaks doing repeat "make installcheck"s.)Another thing that's bad style is the "drop table if exists".
This should be running in an empty database, and if somehow it's
not, destroying pre-existing objects would be pretty unfriendly.
Better to fail at the CREATE.
Thank you for the good explanation. It will be very helpful to write a
test in the future.
See also my comments about your pg_buffercache patch, which
largely apply here too.
OK. I will add the `.gitignore` file.
I will revise my patch and submit it again as soon as possible.
Re: add test: pg_rowlocks extension
I modified my previous patch by reflecting the feedback.
and I wrote most of the queries for the test after looking at the file below.
- ref: (https://github.com/postgres/postgres/blob/master/src/test/isolation/specs/tuplelock-conflict.spec)
The coverage of the test is approximately 81.5%.
If there is any problem, I would appreciate it if you let me know anytime.
Thank you always for your kind reply.
Attachments:
v2_add_test_pgrowlocks.patchapplication/octet-stream; name=v2_add_test_pgrowlocks.patchDownload
From 3459561dfa1ea3ed14273eda1a03372f0e7a065b Mon Sep 17 00:00:00 2001
From: Lee Dong Wook <sh95119@gmail.com>
Date: Tue, 2 Aug 2022 20:49:52 +0900
Subject: [PATCH] pgrowlocks: add test for coverage
---
contrib/pgrowlocks/.gitignore | 6 +
contrib/pgrowlocks/Makefile | 2 +
contrib/pgrowlocks/expected/pgrowlocks.out | 613 +++++++++++++++++++++
contrib/pgrowlocks/specs/pgrowlocks.spec | 62 +++
4 files changed, 683 insertions(+)
create mode 100644 contrib/pgrowlocks/.gitignore
create mode 100644 contrib/pgrowlocks/expected/pgrowlocks.out
create mode 100644 contrib/pgrowlocks/specs/pgrowlocks.spec
diff --git a/contrib/pgrowlocks/.gitignore b/contrib/pgrowlocks/.gitignore
new file mode 100644
index 000000000000..b4903eba657f
--- /dev/null
+++ b/contrib/pgrowlocks/.gitignore
@@ -0,0 +1,6 @@
+# Generated subdirectories
+/log/
+/results/
+/output_iso/
+/tmp_check/
+/tmp_check_iso/
diff --git a/contrib/pgrowlocks/Makefile b/contrib/pgrowlocks/Makefile
index 294c05dd0f91..2094a25c70d9 100644
--- a/contrib/pgrowlocks/Makefile
+++ b/contrib/pgrowlocks/Makefile
@@ -9,6 +9,8 @@ EXTENSION = pgrowlocks
DATA = pgrowlocks--1.2.sql pgrowlocks--1.1--1.2.sql pgrowlocks--1.0--1.1.sql
PGFILEDESC = "pgrowlocks - display row locking information"
+ISOLATION = pgrowlocks
+
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/pgrowlocks/expected/pgrowlocks.out b/contrib/pgrowlocks/expected/pgrowlocks.out
new file mode 100644
index 000000000000..4ae42c5b014d
--- /dev/null
+++ b/contrib/pgrowlocks/expected/pgrowlocks.out
@@ -0,0 +1,613 @@
+unused step name: s2_tuplock1
+unused step name: s2_tuplock2
+unused step name: s2_tuplock3
+unused step name: s2_tuplock4
+Parsed test spec with 2 sessions
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock1 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock1: SELECT * FROM multixact_conflict FOR KEY SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"For Key Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock1 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock1: SELECT * FROM multixact_conflict FOR KEY SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"For Key Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock1 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock1: SELECT * FROM multixact_conflict FOR KEY SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"For Key Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock1 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock1: SELECT * FROM multixact_conflict FOR KEY SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"For Key Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock2 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock2: SELECT * FROM multixact_conflict FOR SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-------------------
+(0,1) |t |{"Key Share",Share}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock2 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock2: SELECT * FROM multixact_conflict FOR SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-------------------
+(0,1) |t |{"Key Share",Share}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock2 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock2: SELECT * FROM multixact_conflict FOR SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-------------------
+(0,1) |t |{"Key Share",Share}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock2 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock2: SELECT * FROM multixact_conflict FOR SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-------------------
+(0,1) |t |{"Key Share",Share}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock3 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock3: SELECT * FROM multixact_conflict FOR NO KEY UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+---------------------------------
+(0,1) |t |{"Key Share","For No Key Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock3 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock3: SELECT * FROM multixact_conflict FOR NO KEY UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+---------------------------------
+(0,1) |t |{"Key Share","For No Key Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock3 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock3: SELECT * FROM multixact_conflict FOR NO KEY UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+---------------------------------
+(0,1) |t |{"Key Share","For No Key Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock3 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock3: SELECT * FROM multixact_conflict FOR NO KEY UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+---------------------------------
+(0,1) |t |{"Key Share","For No Key Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock4 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock4: SELECT * FROM multixact_conflict FOR UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+--------------------------
+(0,1) |t |{"Key Share","For Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock4 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock4: SELECT * FROM multixact_conflict FOR UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+--------------------------
+(0,1) |t |{"Key Share","For Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock4 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock4: SELECT * FROM multixact_conflict FOR UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+--------------------------
+(0,1) |t |{"Key Share","For Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_lcksvpt s1_tuplock4 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_lcksvpt: SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo;
+a
+-
+1
+(1 row)
+
+step s1_tuplock4: SELECT * FROM multixact_conflict FOR UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+--------------------------
+(0,1) |t |{"Key Share","For Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock1 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock1: SELECT * FROM multixact_conflict FOR KEY SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"For Key Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock1 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock1: SELECT * FROM multixact_conflict FOR KEY SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"For Key Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock1 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock1: SELECT * FROM multixact_conflict FOR KEY SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"For Key Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock1 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock1: SELECT * FROM multixact_conflict FOR KEY SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-----------------
+(0,1) |f |{"For Key Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock2 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock2: SELECT * FROM multixact_conflict FOR SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-------------
+(0,1) |f |{"For Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock2 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock2: SELECT * FROM multixact_conflict FOR SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-------------
+(0,1) |f |{"For Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock2 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock2: SELECT * FROM multixact_conflict FOR SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-------------
+(0,1) |f |{"For Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock2 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock2: SELECT * FROM multixact_conflict FOR SHARE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+-------------
+(0,1) |f |{"For Share"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock3 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock3: SELECT * FROM multixact_conflict FOR NO KEY UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+---------------------
+(0,1) |f |{"For No Key Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock3 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock3: SELECT * FROM multixact_conflict FOR NO KEY UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+---------------------
+(0,1) |f |{"For No Key Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock3 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock3: SELECT * FROM multixact_conflict FOR NO KEY UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+---------------------
+(0,1) |f |{"For No Key Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock3 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock3: SELECT * FROM multixact_conflict FOR NO KEY UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+---------------------
+(0,1) |f |{"For No Key Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock4 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock4: SELECT * FROM multixact_conflict FOR UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+--------------
+(0,1) |f |{"For Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock4 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock4: SELECT * FROM multixact_conflict FOR UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+--------------
+(0,1) |f |{"For Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock4 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock4: SELECT * FROM multixact_conflict FOR UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+--------------
+(0,1) |f |{"For Update"}
+(1 row)
+
+step s1_commit: COMMIT;
+
+starting permutation: s1_begin s1_tuplock4 s2_rowlocks s1_commit
+step s1_begin: BEGIN;
+step s1_tuplock4: SELECT * FROM multixact_conflict FOR UPDATE;
+a
+-
+1
+(1 row)
+
+step s2_rowlocks: select locked_row, multi, modes from pgrowlocks('multixact_conflict');
+locked_row|multi|modes
+----------+-----+--------------
+(0,1) |f |{"For Update"}
+(1 row)
+
+step s1_commit: COMMIT;
diff --git a/contrib/pgrowlocks/specs/pgrowlocks.spec b/contrib/pgrowlocks/specs/pgrowlocks.spec
new file mode 100644
index 000000000000..fd068a546e48
--- /dev/null
+++ b/contrib/pgrowlocks/specs/pgrowlocks.spec
@@ -0,0 +1,62 @@
+setup {
+ CREATE EXTENSION pgrowlocks;
+ CREATE TABLE multixact_conflict (a int primary key);
+ INSERT INTO multixact_conflict VALUES (1);
+}
+
+teardown {
+ DROP EXTENSION pgrowlocks;
+ DROP TABLE multixact_conflict;
+}
+
+session s1
+step s1_begin { BEGIN; }
+step s1_lcksvpt { SELECT * FROM multixact_conflict FOR KEY SHARE; SAVEPOINT foo; }
+step s1_tuplock1 { SELECT * FROM multixact_conflict FOR KEY SHARE; }
+step s1_tuplock2 { SELECT * FROM multixact_conflict FOR SHARE; }
+step s1_tuplock3 { SELECT * FROM multixact_conflict FOR NO KEY UPDATE; }
+step s1_tuplock4 { SELECT * FROM multixact_conflict FOR UPDATE; }
+step s1_commit { COMMIT; }
+
+session s2
+step s2_tuplock1 { SELECT * FROM multixact_conflict FOR KEY SHARE; }
+step s2_tuplock2 { SELECT * FROM multixact_conflict FOR SHARE; }
+step s2_tuplock3 { SELECT * FROM multixact_conflict FOR NO KEY UPDATE; }
+step s2_tuplock4 { SELECT * FROM multixact_conflict FOR UPDATE; }
+step s2_rowlocks { select locked_row, multi, modes from pgrowlocks('multixact_conflict'); }
+
+# with savepoints test the multixact cases
+permutation s1_begin s1_lcksvpt s1_tuplock1 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock1 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock1 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock1 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock2 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock2 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock2 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock2 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock3 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock3 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock3 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock3 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock4 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock4 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock4 s2_rowlocks s1_commit
+permutation s1_begin s1_lcksvpt s1_tuplock4 s2_rowlocks s1_commit
+
+# no multixacts here
+permutation s1_begin s1_tuplock1 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock1 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock1 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock1 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock2 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock2 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock2 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock2 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock3 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock3 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock3 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock3 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock4 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock4 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock4 s2_rowlocks s1_commit
+permutation s1_begin s1_tuplock4 s2_rowlocks s1_commit
Re: add test: pg_rowlocks extension
Dong Wook Lee <sh95119@gmail.com> writes:
I modified my previous patch by reflecting the feedback.
and I wrote most of the queries for the test after looking at the file below.
Pushed with some revisions. Notably, I didn't see any point in
repeating each test case four times, so I trimmed it down to once
per case.
regards, tom lane