/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/regress/expected/advisory_lock.out
275 строк
13 KB
Andres Freund
tests: Restrict pg_locks queries in advisory_locks.sql to current database
05 окт 2022, 20:44
05 окт 2022, 20:44
c3315a7
Код
Авторство
О чём код?
-- -- ADVISORY LOCKS -- SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset BEGIN; SELECT pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared -----------------------+------------------------------+-----------------------+------------------------------ | | | (1 row) SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid ORDER BY classid, objid, objsubid; locktype | classid | objid | objsubid | mode | granted ----------+---------+-------+----------+---------------+--------- advisory | 0 | 1 | 1 | ExclusiveLock | t advisory | 0 | 2 | 1 | ShareLock | t advisory | 1 | 1 | 2 | ExclusiveLock | t advisory | 2 | 2 | 2 | ShareLock | t (4 rows) -- pg_advisory_unlock_all() shouldn't release xact locks SELECT pg_advisory_unlock_all(); pg_advisory_unlock_all ------------------------ (1 row) SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; count ------- 4 (1 row) -- can't unlock xact locks SELECT pg_advisory_unlock(1), pg_advisory_unlock_shared(2), pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2); WARNING: you don't own a lock of type ExclusiveLock WARNING: you don't own a lock of type ShareLock WARNING: you don't own a lock of type ExclusiveLock WARNING: you don't own a lock of type ShareLock pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock_shared --------------------+---------------------------+--------------------+--------------------------- f | f | f | f (1 row) -- automatically release xact locks at commit COMMIT; SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; count ------- 0 (1 row) BEGIN; -- holding both session and xact locks on the same objects, xact first SELECT pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared -----------------------+------------------------------+-----------------------+------------------------------ | | | (1 row) SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid ORDER BY classid, objid, objsubid; locktype | classid | objid | objsubid | mode | granted ----------+---------+-------+----------+---------------+--------- advisory | 0 | 1 | 1 | ExclusiveLock | t advisory | 0 | 2 | 1 | ShareLock | t advisory | 1 | 1 | 2 | ExclusiveLock | t advisory | 2 | 2 | 2 | ShareLock | t (4 rows) SELECT pg_advisory_lock(1), pg_advisory_lock_shared(2), pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2); pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared ------------------+-------------------------+------------------+------------------------- | | | (1 row) ROLLBACK; SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid ORDER BY classid, objid, objsubid; locktype | classid | objid | objsubid | mode | granted ----------+---------+-------+----------+---------------+--------- advisory | 0 | 1 | 1 | ExclusiveLock | t advisory | 0 | 2 | 1 | ShareLock | t advisory | 1 | 1 | 2 | ExclusiveLock | t advisory | 2 | 2 | 2 | ShareLock | t (4 rows) -- unlocking session locks SELECT pg_advisory_unlock(1), pg_advisory_unlock(1), pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2), pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2); WARNING: you don't own a lock of type ExclusiveLock WARNING: you don't own a lock of type ShareLock WARNING: you don't own a lock of type ExclusiveLock WARNING: you don't own a lock of type ShareLock 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 --------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+--------------------------- t | f | t | f | t | f | t | f (1 row) SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; count ------- 0 (1 row) BEGIN; -- holding both session and xact locks on the same objects, session first SELECT pg_advisory_lock(1), pg_advisory_lock_shared(2), pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2); pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared ------------------+-------------------------+------------------+------------------------- | | | (1 row) SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid ORDER BY classid, objid, objsubid; locktype | classid | objid | objsubid | mode | granted ----------+---------+-------+----------+---------------+--------- advisory | 0 | 1 | 1 | ExclusiveLock | t advisory | 0 | 2 | 1 | ShareLock | t advisory | 1 | 1 | 2 | ExclusiveLock | t advisory | 2 | 2 | 2 | ShareLock | t (4 rows) SELECT pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared -----------------------+------------------------------+-----------------------+------------------------------ | | | (1 row) ROLLBACK; SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid ORDER BY classid, objid, objsubid; locktype | classid | objid | objsubid | mode | granted ----------+---------+-------+----------+---------------+--------- advisory | 0 | 1 | 1 | ExclusiveLock | t advisory | 0 | 2 | 1 | ShareLock | t advisory | 1 | 1 | 2 | ExclusiveLock | t advisory | 2 | 2 | 2 | ShareLock | t (4 rows) -- releasing all session locks SELECT pg_advisory_unlock_all(); pg_advisory_unlock_all ------------------------ (1 row) SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; count ------- 0 (1 row) BEGIN; -- grabbing txn locks multiple times SELECT pg_advisory_xact_lock(1), pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2); 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 -----------------------+-----------------------+------------------------------+------------------------------+-----------------------+-----------------------+------------------------------+------------------------------ | | | | | | | (1 row) SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid ORDER BY classid, objid, objsubid; locktype | classid | objid | objsubid | mode | granted ----------+---------+-------+----------+---------------+--------- advisory | 0 | 1 | 1 | ExclusiveLock | t advisory | 0 | 2 | 1 | ShareLock | t advisory | 1 | 1 | 2 | ExclusiveLock | t advisory | 2 | 2 | 2 | ShareLock | t (4 rows) COMMIT; SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; count ------- 0 (1 row) -- grabbing session locks multiple times SELECT pg_advisory_lock(1), pg_advisory_lock(1), pg_advisory_lock_shared(2), pg_advisory_lock_shared(2), pg_advisory_lock(1, 1), pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2); 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 ------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+------------------------- | | | | | | | (1 row) SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid ORDER BY classid, objid, objsubid; locktype | classid | objid | objsubid | mode | granted ----------+---------+-------+----------+---------------+--------- advisory | 0 | 1 | 1 | ExclusiveLock | t advisory | 0 | 2 | 1 | ShareLock | t advisory | 1 | 1 | 2 | ExclusiveLock | t advisory | 2 | 2 | 2 | ShareLock | t (4 rows) SELECT pg_advisory_unlock(1), pg_advisory_unlock(1), pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2), pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2); 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 --------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+--------------------------- t | t | t | t | t | t | t | t (1 row) SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; count ------- 0 (1 row) -- .. and releasing them all at once SELECT pg_advisory_lock(1), pg_advisory_lock(1), pg_advisory_lock_shared(2), pg_advisory_lock_shared(2), pg_advisory_lock(1, 1), pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2); 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 ------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+------------------------- | | | | | | | (1 row) SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid ORDER BY classid, objid, objsubid; locktype | classid | objid | objsubid | mode | granted ----------+---------+-------+----------+---------------+--------- advisory | 0 | 1 | 1 | ExclusiveLock | t advisory | 0 | 2 | 1 | ShareLock | t advisory | 1 | 1 | 2 | ExclusiveLock | t advisory | 2 | 2 | 2 | ShareLock | t (4 rows) SELECT pg_advisory_unlock_all(); pg_advisory_unlock_all ------------------------ (1 row) SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid; count ------- 0 (1 row)