Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitac33c7e

Browse files
committed
Regression tests for LOCK TABLE.
Robins Tharakan, reviewed by Szymon Guz, substantially revised by me.
1 parent67ccbb0 commitac33c7e

File tree

4 files changed

+130
-1
lines changed

4 files changed

+130
-1
lines changed

‎src/test/regress/expected/lock.out

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
--
2+
-- Test the LOCK statement
3+
--
4+
-- Setup
5+
CREATE SCHEMA lock_schema1;
6+
SET search_path = lock_schema1;
7+
CREATE TABLE lock_tbl1 (a BIGINT);
8+
CREATE VIEW lock_view1 AS SELECT 1;
9+
CREATE ROLE regress_rol_lock1;
10+
ALTER ROLE regress_rol_lock1 SET search_path = lock_schema1;
11+
GRANT USAGE ON SCHEMA lock_schema1 TO regress_rol_lock1;
12+
-- Try all valid lock options; also try omitting the optional TABLE keyword.
13+
BEGIN TRANSACTION;
14+
LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE;
15+
LOCK lock_tbl1 IN ROW SHARE MODE;
16+
LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE;
17+
LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE;
18+
LOCK TABLE lock_tbl1 IN SHARE MODE;
19+
LOCK lock_tbl1 IN SHARE ROW EXCLUSIVE MODE;
20+
LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE;
21+
LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE;
22+
ROLLBACK;
23+
-- Try using NOWAIT along with valid options.
24+
BEGIN TRANSACTION;
25+
LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE NOWAIT;
26+
LOCK TABLE lock_tbl1 IN ROW SHARE MODE NOWAIT;
27+
LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE NOWAIT;
28+
LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE NOWAIT;
29+
LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT;
30+
LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;
31+
LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT;
32+
LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE NOWAIT;
33+
LOCK TABLE lock_view1 IN EXCLUSIVE MODE; -- Will fail; can't lock a non-table
34+
ERROR: "lock_view1" is not a table
35+
ROLLBACK;
36+
-- Verify that we can lock a table with inheritance children.
37+
CREATE TABLE lock_tbl2 (b BIGINT) INHERITS (lock_tbl1);
38+
CREATE TABLE lock_tbl3 () INHERITS (lock_tbl2);
39+
BEGIN TRANSACTION;
40+
LOCK TABLE lock_tbl1 * IN ACCESS EXCLUSIVE MODE;
41+
ROLLBACK;
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.
44+
GRANT UPDATE ON TABLE lock_tbl1 TO regress_rol_lock1;
45+
SET ROLE regress_rol_lock1;
46+
BEGIN;
47+
LOCK TABLE lock_tbl1 * IN ACCESS EXCLUSIVE MODE;
48+
ERROR: permission denied for relation lock_tbl2
49+
ROLLBACK;
50+
BEGIN;
51+
LOCK TABLE ONLY lock_tbl1;
52+
ROLLBACK;
53+
RESET ROLE;
54+
--
55+
-- Clean up
56+
--
57+
DROP VIEW lock_view1;
58+
DROP TABLE lock_tbl3;
59+
DROP TABLE lock_tbl2;
60+
DROP TABLE lock_tbl1;
61+
DROP SCHEMA lock_schema1 CASCADE;
62+
DROP ROLE regress_rol_lock1;

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
8383
# ----------
8484
# Another group of parallel tests
8585
# ----------
86-
test: privileges security_label collate matview
86+
test: privileges security_label collate matview lock
8787

8888
# ----------
8989
# Another group of parallel tests

‎src/test/regress/serial_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,7 @@ test: privileges
9696
test: security_label
9797
test: collate
9898
test: matview
99+
test: lock
99100
test: alter_generic
100101
test: misc
101102
test: psql

‎src/test/regress/sql/lock.sql

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
--
2+
-- Test the LOCK statement
3+
--
4+
5+
-- Setup
6+
CREATESCHEMAlock_schema1;
7+
SET search_path= lock_schema1;
8+
CREATETABLElock_tbl1 (aBIGINT);
9+
CREATEVIEWlock_view1ASSELECT1;
10+
CREATE ROLE regress_rol_lock1;
11+
ALTER ROLE regress_rol_lock1SET search_path= lock_schema1;
12+
GRANT USAGEON SCHEMA lock_schema1 TO regress_rol_lock1;
13+
14+
-- Try all valid lock options; also try omitting the optional TABLE keyword.
15+
BEGIN TRANSACTION;
16+
LOCK TABLE lock_tbl1IN ACCESS SHARE MODE;
17+
LOCK lock_tbl1IN ROW SHARE MODE;
18+
LOCK TABLE lock_tbl1IN ROW EXCLUSIVE MODE;
19+
LOCK TABLE lock_tbl1IN SHAREUPDATE EXCLUSIVE MODE;
20+
LOCK TABLE lock_tbl1IN SHARE MODE;
21+
LOCK lock_tbl1IN SHARE ROW EXCLUSIVE MODE;
22+
LOCK TABLE lock_tbl1IN EXCLUSIVE MODE;
23+
LOCK TABLE lock_tbl1IN ACCESS EXCLUSIVE MODE;
24+
ROLLBACK;
25+
26+
-- Try using NOWAIT along with valid options.
27+
BEGIN TRANSACTION;
28+
LOCK TABLE lock_tbl1IN ACCESS SHARE MODE NOWAIT;
29+
LOCK TABLE lock_tbl1IN ROW SHARE MODE NOWAIT;
30+
LOCK TABLE lock_tbl1IN ROW EXCLUSIVE MODE NOWAIT;
31+
LOCK TABLE lock_tbl1IN SHAREUPDATE EXCLUSIVE MODE NOWAIT;
32+
LOCK TABLE lock_tbl1IN SHARE MODE NOWAIT;
33+
LOCK TABLE lock_tbl1IN SHARE ROW EXCLUSIVE MODE NOWAIT;
34+
LOCK TABLE lock_tbl1IN EXCLUSIVE MODE NOWAIT;
35+
LOCK TABLE lock_tbl1IN ACCESS EXCLUSIVE MODE NOWAIT;
36+
LOCK TABLE lock_view1IN EXCLUSIVE MODE;-- Will fail; can't lock a non-table
37+
ROLLBACK;
38+
39+
-- Verify that we can lock a table with inheritance children.
40+
CREATETABLElock_tbl2 (bBIGINT) INHERITS (lock_tbl1);
41+
CREATETABLElock_tbl3 () INHERITS (lock_tbl2);
42+
BEGIN TRANSACTION;
43+
LOCK TABLE lock_tbl1*IN ACCESS EXCLUSIVE MODE;
44+
ROLLBACK;
45+
46+
-- Verify that we can't lock a child table just because we have permission
47+
-- on the parent, but that we can lock the parent only.
48+
GRANTUPDATEON TABLE lock_tbl1 TO regress_rol_lock1;
49+
SET ROLE regress_rol_lock1;
50+
BEGIN;
51+
LOCK TABLE lock_tbl1*IN ACCESS EXCLUSIVE MODE;
52+
ROLLBACK;
53+
BEGIN;
54+
LOCK TABLE ONLY lock_tbl1;
55+
ROLLBACK;
56+
RESET ROLE;
57+
58+
--
59+
-- Clean up
60+
--
61+
DROPVIEW lock_view1;
62+
DROPTABLE lock_tbl3;
63+
DROPTABLE lock_tbl2;
64+
DROPTABLE lock_tbl1;
65+
DROPSCHEMA lock_schema1 CASCADE;
66+
DROP ROLE regress_rol_lock1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp