aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAndres Freund <andres@anarazel.de>2022-10-05 10:44:38 -0700
committerAndres Freund <andres@anarazel.de>2022-10-05 10:44:38 -0700
commitc3315a7da57be720222b119385ed0f7ad7c15268 (patch)
tree11bcbc5ae9709db80108f17ba5c6a7689032bf30
parent6a20b04f0408fb61da610543d4608a08f86d1aeb (diff)
downloadpostgresql-c3315a7da57be720222b119385ed0f7ad7c15268.tar.gz
postgresql-c3315a7da57be720222b119385ed0f7ad7c15268.zip
tests: Restrict pg_locks queries in advisory_locks.sql to current database
Otherwise testing an existing installation can fail, if there are other locks, e.g. from one of the isolation tests. Reviewed-by: Michael Paquier <michael@paquier.xyz> Discussion: https://postgr.es/m/20221003234111.4ob7yph6r4g4ywhu@awork3.anarazel.de
-rw-r--r--src/test/regress/expected/advisory_lock.out31
-rw-r--r--src/test/regress/sql/advisory_lock.sql32
2 files changed, 33 insertions, 30 deletions
diff --git a/src/test/regress/expected/advisory_lock.out b/src/test/regress/expected/advisory_lock.out
index 2a2df6f7e4b..02e07765ac2 100644
--- a/src/test/regress/expected/advisory_lock.out
+++ b/src/test/regress/expected/advisory_lock.out
@@ -1,6 +1,7 @@
--
-- ADVISORY LOCKS
--
+SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset
BEGIN;
SELECT
pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
@@ -11,7 +12,7 @@ SELECT
(1 row)
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
locktype | classid | objid | objsubid | mode | granted
----------+---------+-------+----------+---------------+---------
@@ -28,7 +29,7 @@ SELECT pg_advisory_unlock_all();
(1 row)
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
count
-------
4
@@ -49,7 +50,7 @@ WARNING: you don't own a lock of type ShareLock
-- automatically release xact locks at commit
COMMIT;
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
count
-------
0
@@ -66,7 +67,7 @@ SELECT
(1 row)
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
locktype | classid | objid | objsubid | mode | granted
----------+---------+-------+----------+---------------+---------
@@ -86,7 +87,7 @@ SELECT
ROLLBACK;
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
locktype | classid | objid | objsubid | mode | granted
----------+---------+-------+----------+---------------+---------
@@ -111,7 +112,7 @@ WARNING: you don't own a lock of type ShareLock
t | f | t | f | t | f | t | f
(1 row)
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
count
-------
0
@@ -128,7 +129,7 @@ SELECT
(1 row)
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
locktype | classid | objid | objsubid | mode | granted
----------+---------+-------+----------+---------------+---------
@@ -148,7 +149,7 @@ SELECT
ROLLBACK;
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
locktype | classid | objid | objsubid | mode | granted
----------+---------+-------+----------+---------------+---------
@@ -165,7 +166,7 @@ SELECT pg_advisory_unlock_all();
(1 row)
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
count
-------
0
@@ -184,7 +185,7 @@ SELECT
(1 row)
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
locktype | classid | objid | objsubid | mode | granted
----------+---------+-------+----------+---------------+---------
@@ -195,7 +196,7 @@ SELECT locktype, classid, objid, objsubid, mode, granted
(4 rows)
COMMIT;
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
count
-------
0
@@ -213,7 +214,7 @@ SELECT
(1 row)
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
locktype | classid | objid | objsubid | mode | granted
----------+---------+-------+----------+---------------+---------
@@ -233,7 +234,7 @@ SELECT
t | t | t | t | t | t | t | t
(1 row)
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
count
-------
0
@@ -251,7 +252,7 @@ SELECT
(1 row)
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
locktype | classid | objid | objsubid | mode | granted
----------+---------+-------+----------+---------------+---------
@@ -267,7 +268,7 @@ SELECT pg_advisory_unlock_all();
(1 row)
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
count
-------
0
diff --git a/src/test/regress/sql/advisory_lock.sql b/src/test/regress/sql/advisory_lock.sql
index 57c47c0faca..8513ab8e98f 100644
--- a/src/test/regress/sql/advisory_lock.sql
+++ b/src/test/regress/sql/advisory_lock.sql
@@ -2,6 +2,8 @@
-- ADVISORY LOCKS
--
+SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset
+
BEGIN;
SELECT
@@ -9,14 +11,14 @@ SELECT
pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
-- pg_advisory_unlock_all() shouldn't release xact locks
SELECT pg_advisory_unlock_all();
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
-- can't unlock xact locks
@@ -28,7 +30,7 @@ SELECT
-- automatically release xact locks at commit
COMMIT;
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
BEGIN;
@@ -39,7 +41,7 @@ SELECT
pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
SELECT
@@ -49,7 +51,7 @@ SELECT
ROLLBACK;
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
@@ -60,7 +62,7 @@ SELECT
pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
BEGIN;
@@ -71,7 +73,7 @@ SELECT
pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
SELECT
@@ -81,14 +83,14 @@ SELECT
ROLLBACK;
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
-- releasing all session locks
SELECT pg_advisory_unlock_all();
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
BEGIN;
@@ -102,12 +104,12 @@ SELECT
pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
COMMIT;
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
-- grabbing session locks multiple times
@@ -118,7 +120,7 @@ SELECT
pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
SELECT
@@ -127,7 +129,7 @@ SELECT
pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
-- .. and releasing them all at once
@@ -138,9 +140,9 @@ SELECT
pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
SELECT locktype, classid, objid, objsubid, mode, granted
- FROM pg_locks WHERE locktype = 'advisory'
+ FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
ORDER BY classid, objid, objsubid;
SELECT pg_advisory_unlock_all();
-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;