src/test/regress/expected/advisory_lock.out

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