granicus.if.org Git - postgresql/blob - src/test/regress/expected/advisory_lock.out

   1 --
   2 -- ADVISORY LOCKS
   3 --
   4 BEGIN;
   5 SELECT
   6         pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
   7         pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
   8  pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
   9 -----------------------+------------------------------+-----------------------+------------------------------
  10                        |                              |                       |
  11 (1 row)
  12
  13 SELECT locktype, classid, objid, objsubid, mode, granted
  14         FROM pg_locks WHERE locktype = 'advisory'
  15         ORDER BY classid, objid, objsubid;
  16  locktype | classid | objid | objsubid |     mode      | granted
  17 ----------+---------+-------+----------+---------------+---------
  18  advisory |       0 |     1 |        1 | ExclusiveLock | t
  19  advisory |       0 |     2 |        1 | ShareLock     | t
  20  advisory |       1 |     1 |        2 | ExclusiveLock | t
  21  advisory |       2 |     2 |        2 | ShareLock     | t
  22 (4 rows)
  23
  24 -- pg_advisory_unlock_all() shouldn't release xact locks
  25 SELECT pg_advisory_unlock_all();
  26  pg_advisory_unlock_all
  27 ------------------------
  28
  29 (1 row)
  30
  31 SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
  32  count
  33 -------
  34      4
  35 (1 row)
  36
  37 -- can't unlock xact locks
  38 SELECT
  39         pg_advisory_unlock(1), pg_advisory_unlock_shared(2),
  40         pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
  41 WARNING:  you don't own a lock of type ExclusiveLock
  42 WARNING:  you don't own a lock of type ShareLock
  43 WARNING:  you don't own a lock of type ExclusiveLock
  44 WARNING:  you don't own a lock of type ShareLock
  45  pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock_shared
  46 --------------------+---------------------------+--------------------+---------------------------
  47  f                  | f                         | f                  | f
  48 (1 row)
  49
  50 -- automatically release xact locks at commit
  51 COMMIT;
  52 SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
  53  count
  54 -------
  55      0
  56 (1 row)
  57
  58 BEGIN;
  59 -- holding both session and xact locks on the same objects, xact first
  60 SELECT
  61         pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
  62         pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
  63  pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
  64 -----------------------+------------------------------+-----------------------+------------------------------
  65                        |                              |                       |
  66 (1 row)
  67
  68 SELECT locktype, classid, objid, objsubid, mode, granted
  69         FROM pg_locks WHERE locktype = 'advisory'
  70         ORDER BY classid, objid, objsubid;
  71  locktype | classid | objid | objsubid |     mode      | granted
  72 ----------+---------+-------+----------+---------------+---------
  73  advisory |       0 |     1 |        1 | ExclusiveLock | t
  74  advisory |       0 |     2 |        1 | ShareLock     | t
  75  advisory |       1 |     1 |        2 | ExclusiveLock | t
  76  advisory |       2 |     2 |        2 | ShareLock     | t
  77 (4 rows)
  78
  79 SELECT
  80         pg_advisory_lock(1), pg_advisory_lock_shared(2),
  81         pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
  82  pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared
  83 ------------------+-------------------------+------------------+-------------------------
  84                   |                         |                  |
  85 (1 row)
  86
  87 ROLLBACK;
  88 SELECT locktype, classid, objid, objsubid, mode, granted
  89         FROM pg_locks WHERE locktype = 'advisory'
  90         ORDER BY classid, objid, objsubid;
  91  locktype | classid | objid | objsubid |     mode      | granted
  92 ----------+---------+-------+----------+---------------+---------
  93  advisory |       0 |     1 |        1 | ExclusiveLock | t
  94  advisory |       0 |     2 |        1 | ShareLock     | t
  95  advisory |       1 |     1 |        2 | ExclusiveLock | t
  96  advisory |       2 |     2 |        2 | ShareLock     | t
  97 (4 rows)
  98
  99 -- unlocking session locks
 100 SELECT
 101         pg_advisory_unlock(1), pg_advisory_unlock(1),
 102         pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
 103         pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
 104         pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
 105 WARNING:  you don't own a lock of type ExclusiveLock
 106 WARNING:  you don't own a lock of type ShareLock
 107 WARNING:  you don't own a lock of type ExclusiveLock
 108 WARNING:  you don't own a lock of type ShareLock
 109  pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared
 110 --------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+---------------------------
 111  t                  | f                  | t                         | f                         | t                  | f                  | t                         | f
 112 (1 row)
 113
 114 SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
 115  count
 116 -------
 117      0
 118 (1 row)
 119
 120 BEGIN;
 121 -- holding both session and xact locks on the same objects, session first
 122 SELECT
 123         pg_advisory_lock(1), pg_advisory_lock_shared(2),
 124         pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2);
 125  pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared
 126 ------------------+-------------------------+------------------+-------------------------
 127                   |                         |                  |
 128 (1 row)
 129
 130 SELECT locktype, classid, objid, objsubid, mode, granted
 131         FROM pg_locks WHERE locktype = 'advisory'
 132         ORDER BY classid, objid, objsubid;
 133  locktype | classid | objid | objsubid |     mode      | granted
 134 ----------+---------+-------+----------+---------------+---------
 135  advisory |       0 |     1 |        1 | ExclusiveLock | t
 136  advisory |       0 |     2 |        1 | ShareLock     | t
 137  advisory |       1 |     1 |        2 | ExclusiveLock | t
 138  advisory |       2 |     2 |        2 | ShareLock     | t
 139 (4 rows)
 140
 141 SELECT
 142         pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
 143         pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2);
 144  pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared
 145 -----------------------+------------------------------+-----------------------+------------------------------
 146                        |                              |                       |
 147 (1 row)
 148
 149 ROLLBACK;
 150 SELECT locktype, classid, objid, objsubid, mode, granted
 151         FROM pg_locks WHERE locktype = 'advisory'
 152         ORDER BY classid, objid, objsubid;
 153  locktype | classid | objid | objsubid |     mode      | granted
 154 ----------+---------+-------+----------+---------------+---------
 155  advisory |       0 |     1 |        1 | ExclusiveLock | t
 156  advisory |       0 |     2 |        1 | ShareLock     | t
 157  advisory |       1 |     1 |        2 | ExclusiveLock | t
 158  advisory |       2 |     2 |        2 | ShareLock     | t
 159 (4 rows)
 160
 161 -- releasing all session locks
 162 SELECT pg_advisory_unlock_all();
 163  pg_advisory_unlock_all
 164 ------------------------
 165
 166 (1 row)
 167
 168 SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
 169  count
 170 -------
 171      0
 172 (1 row)
 173
 174 BEGIN;
 175 -- grabbing txn locks multiple times
 176 SELECT
 177         pg_advisory_xact_lock(1), pg_advisory_xact_lock(1),
 178         pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2),
 179         pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1),
 180         pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);
 181  pg_advisory_xact_lock | pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock_shared
 182 -----------------------+-----------------------+------------------------------+------------------------------+-----------------------+-----------------------+------------------------------+------------------------------
 183                        |                       |                              |                              |                       |                       |                              |
 184 (1 row)
 185
 186 SELECT locktype, classid, objid, objsubid, mode, granted
 187         FROM pg_locks WHERE locktype = 'advisory'
 188         ORDER BY classid, objid, objsubid;
 189  locktype | classid | objid | objsubid |     mode      | granted
 190 ----------+---------+-------+----------+---------------+---------
 191  advisory |       0 |     1 |        1 | ExclusiveLock | t
 192  advisory |       0 |     2 |        1 | ShareLock     | t
 193  advisory |       1 |     1 |        2 | ExclusiveLock | t
 194  advisory |       2 |     2 |        2 | ShareLock     | t
 195 (4 rows)
 196
 197 COMMIT;
 198 SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
 199  count
 200 -------
 201      0
 202 (1 row)
 203
 204 -- grabbing session locks multiple times
 205 SELECT
 206         pg_advisory_lock(1), pg_advisory_lock(1),
 207         pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
 208         pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
 209         pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
 210  pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared
 211 ------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+-------------------------
 212                   |                  |                         |                         |                  |                  |                         |
 213 (1 row)
 214
 215 SELECT locktype, classid, objid, objsubid, mode, granted
 216         FROM pg_locks WHERE locktype = 'advisory'
 217         ORDER BY classid, objid, objsubid;
 218  locktype | classid | objid | objsubid |     mode      | granted
 219 ----------+---------+-------+----------+---------------+---------
 220  advisory |       0 |     1 |        1 | ExclusiveLock | t
 221  advisory |       0 |     2 |        1 | ShareLock     | t
 222  advisory |       1 |     1 |        2 | ExclusiveLock | t
 223  advisory |       2 |     2 |        2 | ShareLock     | t
 224 (4 rows)
 225
 226 SELECT
 227         pg_advisory_unlock(1), pg_advisory_unlock(1),
 228         pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2),
 229         pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1),
 230         pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2);
 231  pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared
 232 --------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+---------------------------
 233  t                  | t                  | t                         | t                         | t                  | t                  | t                         | t
 234 (1 row)
 235
 236 SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
 237  count
 238 -------
 239      0
 240 (1 row)
 241
 242 -- .. and releasing them all at once
 243 SELECT
 244         pg_advisory_lock(1), pg_advisory_lock(1),
 245         pg_advisory_lock_shared(2), pg_advisory_lock_shared(2),
 246         pg_advisory_lock(1, 1), pg_advisory_lock(1, 1),
 247         pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2);
 248  pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared
 249 ------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+-------------------------
 250                   |                  |                         |                         |                  |                  |                         |
 251 (1 row)
 252
 253 SELECT locktype, classid, objid, objsubid, mode, granted
 254         FROM pg_locks WHERE locktype = 'advisory'
 255         ORDER BY classid, objid, objsubid;
 256  locktype | classid | objid | objsubid |     mode      | granted
 257 ----------+---------+-------+----------+---------------+---------
 258  advisory |       0 |     1 |        1 | ExclusiveLock | t
 259  advisory |       0 |     2 |        1 | ShareLock     | t
 260  advisory |       1 |     1 |        2 | ExclusiveLock | t
 261  advisory |       2 |     2 |        2 | ShareLock     | t
 262 (4 rows)
 263
 264 SELECT pg_advisory_unlock_all();
 265  pg_advisory_unlock_all
 266 ------------------------
 267
 268 (1 row)
 269
 270 SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';
 271  count
 272 -------
 273      0
 274 (1 row)
 275