Skip to content

Commit 3981fd5

Browse files
anarazelmichaelpq
anarazel
authored and
michaelpq
committed
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. This was originally applied as c3315a7 in 16~, but it is possible to see this test fail depending on the concurrent activity for older active branches. Reviewed-by: Michael Paquier <michael@paquier.xyz> Discussion: https://postgr.es/m/20221003234111.4ob7yph6r4g4ywhu@awork3.anarazel.de Backpatch-through: 12
1 parent 74eaa05 commit 3981fd5

File tree

2 files changed

+33
-30
lines changed

2 files changed

+33
-30
lines changed

‎src/test/regress/expected/advisory_lock.out

+16-15
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
--
22
-- ADVISORY LOCKS
33
--
4+
SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset
45
BEGIN;
56
SELECT
67
pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
@@ -11,7 +12,7 @@ SELECT
1112
(1 row)
1213

1314
SELECT locktype, classid, objid, objsubid, mode, granted
14-
FROM pg_locks WHERE locktype = 'advisory'
15+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
1516
ORDER BY classid, objid, objsubid;
1617
locktype | classid | objid | objsubid | mode | granted
1718
----------+---------+-------+----------+---------------+---------
@@ -28,7 +29,7 @@ SELECT pg_advisory_unlock_all();
2829

2930
(1 row)
3031

31-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
32+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
3233
count
3334
-------
3435
4
@@ -49,7 +50,7 @@ WARNING: you don't own a lock of type ShareLock
4950

5051
-- automatically release xact locks at commit
5152
COMMIT;
52-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
53+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
5354
count
5455
-------
5556
0
@@ -66,7 +67,7 @@ SELECT
6667
(1 row)
6768

6869
SELECT locktype, classid, objid, objsubid, mode, granted
69-
FROM pg_locks WHERE locktype = 'advisory'
70+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
7071
ORDER BY classid, objid, objsubid;
7172
locktype | classid | objid | objsubid | mode | granted
7273
----------+---------+-------+----------+---------------+---------
@@ -86,7 +87,7 @@ SELECT
8687

8788
ROLLBACK;
8889
SELECT locktype, classid, objid, objsubid, mode, granted
89-
FROM pg_locks WHERE locktype = 'advisory'
90+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
9091
ORDER BY classid, objid, objsubid;
9192
locktype | classid | objid | objsubid | mode | granted
9293
----------+---------+-------+----------+---------------+---------
@@ -111,7 +112,7 @@ WARNING: you don't own a lock of type ShareLock
111112
t | f | t | f | t | f | t | f
112113
(1 row)
113114

114-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
115+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
115116
count
116117
-------
117118
0
@@ -128,7 +129,7 @@ SELECT
128129
(1 row)
129130

130131
SELECT locktype, classid, objid, objsubid, mode, granted
131-
FROM pg_locks WHERE locktype = 'advisory'
132+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
132133
ORDER BY classid, objid, objsubid;
133134
locktype | classid | objid | objsubid | mode | granted
134135
----------+---------+-------+----------+---------------+---------
@@ -148,7 +149,7 @@ SELECT
148149

149150
ROLLBACK;
150151
SELECT locktype, classid, objid, objsubid, mode, granted
151-
FROM pg_locks WHERE locktype = 'advisory'
152+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
152153
ORDER BY classid, objid, objsubid;
153154
locktype | classid | objid | objsubid | mode | granted
154155
----------+---------+-------+----------+---------------+---------
@@ -165,7 +166,7 @@ SELECT pg_advisory_unlock_all();
165166

166167
(1 row)
167168

168-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
169+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
169170
count
170171
-------
171172
0
@@ -184,7 +185,7 @@ SELECT
184185
(1 row)
185186

186187
SELECT locktype, classid, objid, objsubid, mode, granted
187-
FROM pg_locks WHERE locktype = 'advisory'
188+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
188189
ORDER BY classid, objid, objsubid;
189190
locktype | classid | objid | objsubid | mode | granted
190191
----------+---------+-------+----------+---------------+---------
@@ -195,7 +196,7 @@ SELECT locktype, classid, objid, objsubid, mode, granted
195196
(4 rows)
196197

197198
COMMIT;
198-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
199+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
199200
count
200201
-------
201202
0
@@ -213,7 +214,7 @@ SELECT
213214
(1 row)
214215

215216
SELECT locktype, classid, objid, objsubid, mode, granted
216-
FROM pg_locks WHERE locktype = 'advisory'
217+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
217218
ORDER BY classid, objid, objsubid;
218219
locktype | classid | objid | objsubid | mode | granted
219220
----------+---------+-------+----------+---------------+---------
@@ -233,7 +234,7 @@ SELECT
233234
t | t | t | t | t | t | t | t
234235
(1 row)
235236

236-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
237+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
237238
count
238239
-------
239240
0
@@ -251,7 +252,7 @@ SELECT
251252
(1 row)
252253

253254
SELECT locktype, classid, objid, objsubid, mode, granted
254-
FROM pg_locks WHERE locktype = 'advisory'
255+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
255256
ORDER BY classid, objid, objsubid;
256257
locktype | classid | objid | objsubid | mode | granted
257258
----------+---------+-------+----------+---------------+---------
@@ -267,7 +268,7 @@ SELECT pg_advisory_unlock_all();
267268

268269
(1 row)
269270

270-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
271+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
271272
count
272273
-------
273274
0

‎src/test/regress/sql/advisory_lock.sql

+17-15
Original file line numberDiff line numberDiff line change
@@ -2,21 +2,23 @@
22
-- ADVISORY LOCKS
33
--
44

5+
SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset
6+
57
BEGIN;
68

79
SELECT
810
pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
911
pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
1012

1113
SELECT locktype, classid, objid, objsubid, mode, granted
12-
FROM pg_locks WHERE locktype = 'advisory'
14+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
1315
ORDER BY classid, objid, objsubid;
1416

1517

1618
-- pg_advisory_unlock_all() shouldn't release xact locks
1719
SELECT pg_advisory_unlock_all();
1820

19-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
21+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
2022

2123

2224
-- can't unlock xact locks
@@ -28,7 +30,7 @@ SELECT
2830
-- automatically release xact locks at commit
2931
COMMIT;
3032

31-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
33+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
3234

3335

3436
BEGIN;
@@ -39,7 +41,7 @@ SELECT
3941
pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
4042

4143
SELECT locktype, classid, objid, objsubid, mode, granted
42-
FROM pg_locks WHERE locktype = 'advisory'
44+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
4345
ORDER BY classid, objid, objsubid;
4446

4547
SELECT
@@ -49,7 +51,7 @@ SELECT
4951
ROLLBACK;
5052

5153
SELECT locktype, classid, objid, objsubid, mode, granted
52-
FROM pg_locks WHERE locktype = 'advisory'
54+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
5355
ORDER BY classid, objid, objsubid;
5456

5557

@@ -60,7 +62,7 @@ SELECT
6062
pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
6163
pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
6264

63-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
65+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
6466

6567

6668
BEGIN;
@@ -71,7 +73,7 @@ SELECT
7173
pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
7274

7375
SELECT locktype, classid, objid, objsubid, mode, granted
74-
FROM pg_locks WHERE locktype = 'advisory'
76+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
7577
ORDER BY classid, objid, objsubid;
7678

7779
SELECT
@@ -81,14 +83,14 @@ SELECT
8183
ROLLBACK;
8284

8385
SELECT locktype, classid, objid, objsubid, mode, granted
84-
FROM pg_locks WHERE locktype = 'advisory'
86+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
8587
ORDER BY classid, objid, objsubid;
8688

8789

8890
-- releasing all session locks
8991
SELECT pg_advisory_unlock_all();
9092

91-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
93+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
9294

9395

9496
BEGIN;
@@ -102,12 +104,12 @@ SELECT
102104
pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);
103105

104106
SELECT locktype, classid, objid, objsubid, mode, granted
105-
FROM pg_locks WHERE locktype = 'advisory'
107+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
106108
ORDER BY classid, objid, objsubid;
107109

108110
COMMIT;
109111

110-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
112+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
111113

112114
-- grabbing session locks multiple times
113115

@@ -118,7 +120,7 @@ SELECT
118120
pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
119121

120122
SELECT locktype, classid, objid, objsubid, mode, granted
121-
FROM pg_locks WHERE locktype = 'advisory'
123+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
122124
ORDER BY classid, objid, objsubid;
123125

124126
SELECT
@@ -127,7 +129,7 @@ SELECT
127129
pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
128130
pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
129131

130-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
132+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
131133

132134
-- .. and releasing them all at once
133135

@@ -138,9 +140,9 @@ SELECT
138140
pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
139141

140142
SELECT locktype, classid, objid, objsubid, mode, granted
141-
FROM pg_locks WHERE locktype = 'advisory'
143+
FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
142144
ORDER BY classid, objid, objsubid;
143145

144146
SELECT pg_advisory_unlock_all();
145147

146-
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
148+
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;

0 commit comments

Comments
 (0)