1--
2-- ADVISORY LOCKS
3--
4
5BEGIN;
6
7SELECT
8	pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
9	pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
10
11SELECT locktype, classid, objid, objsubid, mode, granted
12	FROM pg_locks WHERE locktype = 'advisory'
13	ORDER BY classid, objid, objsubid;
14
15
16-- pg_advisory_unlock_all() shouldn't release xact locks
17SELECT pg_advisory_unlock_all();
18
19SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
20
21
22-- can't unlock xact locks
23SELECT
24	pg_advisory_unlock(1), pg_advisory_unlock_shared(2),
25	pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
26
27
28-- automatically release xact locks at commit
29COMMIT;
30
31SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
32
33
34BEGIN;
35
36-- holding both session and xact locks on the same objects, xact first
37SELECT
38	pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
39	pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
40
41SELECT locktype, classid, objid, objsubid, mode, granted
42	FROM pg_locks WHERE locktype = 'advisory'
43	ORDER BY classid, objid, objsubid;
44
45SELECT
46	pg_advisory_lock(1), pg_advisory_lock_shared(2),
47	pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
48
49ROLLBACK;
50
51SELECT locktype, classid, objid, objsubid, mode, granted
52	FROM pg_locks WHERE locktype = 'advisory'
53	ORDER BY classid, objid, objsubid;
54
55
56-- unlocking session locks
57SELECT
58	pg_advisory_unlock(1), pg_advisory_unlock(1),
59	pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
60	pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
61	pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
62
63SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
64
65
66BEGIN;
67
68-- holding both session and xact locks on the same objects, session first
69SELECT
70	pg_advisory_lock(1), pg_advisory_lock_shared(2),
71	pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
72
73SELECT locktype, classid, objid, objsubid, mode, granted
74	FROM pg_locks WHERE locktype = 'advisory'
75	ORDER BY classid, objid, objsubid;
76
77SELECT
78	pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
79	pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
80
81ROLLBACK;
82
83SELECT locktype, classid, objid, objsubid, mode, granted
84	FROM pg_locks WHERE locktype = 'advisory'
85	ORDER BY classid, objid, objsubid;
86
87
88-- releasing all session locks
89SELECT pg_advisory_unlock_all();
90
91SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
92
93
94BEGIN;
95
96-- grabbing txn locks multiple times
97
98SELECT
99	pg_advisory_xact_lock(1), pg_advisory_xact_lock(1),
100	pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2),
101	pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1),
102	pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);
103
104SELECT locktype, classid, objid, objsubid, mode, granted
105	FROM pg_locks WHERE locktype = 'advisory'
106	ORDER BY classid, objid, objsubid;
107
108COMMIT;
109
110SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
111
112-- grabbing session locks multiple times
113
114SELECT
115	pg_advisory_lock(1), pg_advisory_lock(1),
116	pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
117	pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
118	pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
119
120SELECT locktype, classid, objid, objsubid, mode, granted
121	FROM pg_locks WHERE locktype = 'advisory'
122	ORDER BY classid, objid, objsubid;
123
124SELECT
125	pg_advisory_unlock(1), pg_advisory_unlock(1),
126	pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
127	pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
128	pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
129
130SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
131
132-- .. and releasing them all at once
133
134SELECT
135	pg_advisory_lock(1), pg_advisory_lock(1),
136	pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
137	pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
138	pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
139
140SELECT locktype, classid, objid, objsubid, mode, granted
141	FROM pg_locks WHERE locktype = 'advisory'
142	ORDER BY classid, objid, objsubid;
143
144SELECT pg_advisory_unlock_all();
145
146SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
147