1-- 2-- Test the LOCK statement 3-- 4-- Setup 5CREATE SCHEMA lock_schema1; 6SET search_path = lock_schema1; 7CREATE TABLE lock_tbl1 (a BIGINT); 8CREATE VIEW lock_view1 AS SELECT 1; 9CREATE ROLE regress_rol_lock1; 10ALTER ROLE regress_rol_lock1 SET search_path = lock_schema1; 11GRANT USAGE ON SCHEMA lock_schema1 TO regress_rol_lock1; 12-- Try all valid lock options; also try omitting the optional TABLE keyword. 13BEGIN TRANSACTION; 14LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE; 15LOCK lock_tbl1 IN ROW SHARE MODE; 16LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE; 17LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE; 18LOCK TABLE lock_tbl1 IN SHARE MODE; 19LOCK lock_tbl1 IN SHARE ROW EXCLUSIVE MODE; 20LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE; 21LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE; 22ROLLBACK; 23-- Try using NOWAIT along with valid options. 24BEGIN TRANSACTION; 25LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE NOWAIT; 26LOCK TABLE lock_tbl1 IN ROW SHARE MODE NOWAIT; 27LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE NOWAIT; 28LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE NOWAIT; 29LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT; 30LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT; 31LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT; 32LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE NOWAIT; 33LOCK TABLE lock_view1 IN EXCLUSIVE MODE; -- Will fail; can't lock a non-table 34ERROR: "lock_view1" is not a table 35ROLLBACK; 36-- Verify that we can lock a table with inheritance children. 37CREATE TABLE lock_tbl2 (b BIGINT) INHERITS (lock_tbl1); 38CREATE TABLE lock_tbl3 () INHERITS (lock_tbl2); 39BEGIN TRANSACTION; 40LOCK TABLE lock_tbl1 * IN ACCESS EXCLUSIVE MODE; 41ROLLBACK; 42-- Verify that we can't lock a child table just because we have permission 43-- on the parent, but that we can lock the parent only. 44GRANT UPDATE ON TABLE lock_tbl1 TO regress_rol_lock1; 45SET ROLE regress_rol_lock1; 46BEGIN; 47LOCK TABLE lock_tbl1 * IN ACCESS EXCLUSIVE MODE; 48ERROR: permission denied for relation lock_tbl2 49ROLLBACK; 50BEGIN; 51LOCK TABLE ONLY lock_tbl1; 52ROLLBACK; 53RESET ROLE; 54-- 55-- Clean up 56-- 57DROP VIEW lock_view1; 58DROP TABLE lock_tbl3; 59DROP TABLE lock_tbl2; 60DROP TABLE lock_tbl1; 61DROP SCHEMA lock_schema1 CASCADE; 62DROP ROLE regress_rol_lock1; 63-- atomic ops tests 64RESET search_path; 65SELECT test_atomic_ops(); 66 test_atomic_ops 67----------------- 68 t 69(1 row) 70 71