Skip to content

Commit c3315a7

Browse files
anarazelanarazel
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. Reviewed-by: Michael Paquier <michael@paquier.xyz> Discussion: https://postgr.es/m/20221003234111.4ob7yph6r4g4ywhu@awork3.anarazel.de
1 parent 6a20b04 commit c3315a7

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)