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

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;