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
Appearance settings

Commit 53de141

Browse filesBrowse files
alvherrealvherre
committed
Accept relations of any kind in LOCK TABLE
The restriction that only tables and views can be locked by LOCK TABLE is quite arbitrary, since the underlying mechanism can lock any relation type. Drop the restriction so that programs such as pg_dump can lock all relations they're interested in, preventing schema changes that could cause a dump to fail after expending much effort. Backpatch to 9.5. Author: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reported-by: Wells Oliver <wells.oliver@gmail.com> Discussion: https://postgr.es/m/20201021200659.GA32358@alvherre.pgsql
1 parent 67b3c3b commit 53de141
Copy full SHA for 53de141

File tree

Expand file treeCollapse file tree

4 files changed

+40
-21
lines changed
Filter options
Expand file treeCollapse file tree

4 files changed

+40
-21
lines changed

‎doc/src/sgml/ref/lock.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/lock.sgml
+12-9Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ PostgreSQL documentation
1616

1717
<refnamediv>
1818
<refname>LOCK</refname>
19-
<refpurpose>lock a table</refpurpose>
19+
<refpurpose>lock a named relation (table, etc)</refpurpose>
2020
</refnamediv>
2121

2222
<refsynopsisdiv>
@@ -34,7 +34,9 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
3434
<title>Description</title>
3535

3636
<para>
37-
<command>LOCK TABLE</command> obtains a table-level lock, waiting
37+
<command>LOCK TABLE</command> obtains a table-level lock on a
38+
relation (table, partitioned table, foreign table, view,
39+
materialized view, index, composite type, sequence), waiting
3840
if necessary for any conflicting locks to be released. If
3941
<literal>NOWAIT</literal> is specified, <command>LOCK
4042
TABLE</command> does not wait to acquire the desired lock: if it
@@ -110,17 +112,18 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
110112
<term><replaceable class="PARAMETER">name</replaceable></term>
111113
<listitem>
112114
<para>
113-
The name (optionally schema-qualified) of an existing table to
114-
lock. If <literal>ONLY</> is specified before the table name, only that
115-
table is locked. If <literal>ONLY</> is not specified, the table and all
116-
its descendant tables (if any) are locked. Optionally, <literal>*</>
115+
The name (optionally schema-qualified) of an existing relation to
116+
lock. If <literal>ONLY</literal> is specified before a table name, only that
117+
table is locked. If <literal>ONLY</literal> is not specified, the table and all
118+
its descendant tables (if any) are locked. Optionally, <literal>*</literal>
117119
can be specified after the table name to explicitly indicate that
118-
descendant tables are included.
120+
descendant tables are included. When locking a view, all relations appearing
121+
in the view definition are locked, regardless of <literal>ONLY</literal>.
119122
</para>
120123

121124
<para>
122-
The command <literal>LOCK TABLE a, b;</> is equivalent to
123-
<literal>LOCK TABLE a; LOCK TABLE b;</>. The tables are locked
125+
The command <literal>LOCK TABLE a, b;</literal> is equivalent to
126+
<literal>LOCK TABLE a; LOCK TABLE b;</literal>. The relations are locked
124127
one-by-one in the order specified in the <command>LOCK
125128
TABLE</command> command.
126129
</para>

‎src/backend/commands/lockcmds.c

Copy file name to clipboardExpand all lines: src/backend/commands/lockcmds.c
-7Lines changed: 0 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -87,13 +87,6 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
8787
return; /* woops, concurrently dropped; no permissions
8888
* check */
8989

90-
/* Currently, we only allow plain tables to be locked */
91-
if (relkind != RELKIND_RELATION)
92-
ereport(ERROR,
93-
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
94-
errmsg("\"%s\" is not a table",
95-
rv->relname)));
96-
9790
/* Check permissions. */
9891
aclresult = LockTableAclCheck(relid, lockmode);
9992
if (aclresult != ACLCHECK_OK)

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/lock.out
+13-3Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,10 @@
55
CREATE SCHEMA lock_schema1;
66
SET search_path = lock_schema1;
77
CREATE TABLE lock_tbl1 (a BIGINT);
8-
CREATE VIEW lock_view1 AS SELECT 1;
8+
CREATE VIEW lock_view1 AS SELECT 1 AS a;
9+
CREATE MATERIALIZED VIEW lock_mv1 AS SELECT * FROM lock_view1;
10+
CREATE INDEX lock_mvi1 ON lock_mv1 (a);
11+
CREATE SEQUENCE lock_seq;
912
CREATE ROLE regress_rol_lock1;
1013
ALTER ROLE regress_rol_lock1 SET search_path = lock_schema1;
1114
GRANT USAGE ON SCHEMA lock_schema1 TO regress_rol_lock1;
@@ -30,8 +33,7 @@ LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT;
3033
LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;
3134
LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT;
3235
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
36+
LOCK TABLE lock_view1 IN EXCLUSIVE MODE;
3537
ROLLBACK;
3638
-- Verify that we can lock a table with inheritance children.
3739
CREATE TABLE lock_tbl2 (b BIGINT) INHERITS (lock_tbl1);
@@ -51,13 +53,21 @@ BEGIN;
5153
LOCK TABLE ONLY lock_tbl1;
5254
ROLLBACK;
5355
RESET ROLE;
56+
-- Lock other relations
57+
BEGIN TRANSACTION;
58+
LOCK TABLE lock_mv1;
59+
LOCK TABLE lock_mvi1;
60+
LOCK TABLE lock_seq;
61+
ROLLBACK;
5462
--
5563
-- Clean up
5664
--
65+
DROP MATERIALIZED VIEW lock_mv1;
5766
DROP VIEW lock_view1;
5867
DROP TABLE lock_tbl3;
5968
DROP TABLE lock_tbl2;
6069
DROP TABLE lock_tbl1;
70+
DROP SEQUENCE lock_seq;
6171
DROP SCHEMA lock_schema1 CASCADE;
6272
DROP ROLE regress_rol_lock1;
6373
-- atomic ops tests

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

Copy file name to clipboardExpand all lines: src/test/regress/sql/lock.sql
+15-2Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,10 @@
66
CREATE SCHEMA lock_schema1;
77
SET search_path = lock_schema1;
88
CREATE TABLE lock_tbl1 (a BIGINT);
9-
CREATE VIEW lock_view1 AS SELECT 1;
9+
CREATE VIEW lock_view1 AS SELECT 1 AS a;
10+
CREATE MATERIALIZED VIEW lock_mv1 AS SELECT * FROM lock_view1;
11+
CREATE INDEX lock_mvi1 ON lock_mv1 (a);
12+
CREATE SEQUENCE lock_seq;
1013
CREATE ROLE regress_rol_lock1;
1114
ALTER ROLE regress_rol_lock1 SET search_path = lock_schema1;
1215
GRANT USAGE ON SCHEMA lock_schema1 TO regress_rol_lock1;
@@ -33,7 +36,7 @@ LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT;
3336
LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;
3437
LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT;
3538
LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE NOWAIT;
36-
LOCK TABLE lock_view1 IN EXCLUSIVE MODE; -- Will fail; can't lock a non-table
39+
LOCK TABLE lock_view1 IN EXCLUSIVE MODE;
3740
ROLLBACK;
3841

3942
-- Verify that we can lock a table with inheritance children.
@@ -55,13 +58,23 @@ LOCK TABLE ONLY lock_tbl1;
5558
ROLLBACK;
5659
RESET ROLE;
5760

61+
-- Lock other relations
62+
BEGIN TRANSACTION;
63+
LOCK TABLE lock_mv1;
64+
LOCK TABLE lock_mvi1;
65+
LOCK TABLE lock_seq;
66+
ROLLBACK;
67+
68+
5869
--
5970
-- Clean up
6071
--
72+
DROP MATERIALIZED VIEW lock_mv1;
6173
DROP VIEW lock_view1;
6274
DROP TABLE lock_tbl3;
6375
DROP TABLE lock_tbl2;
6476
DROP TABLE lock_tbl1;
77+
DROP SEQUENCE lock_seq;
6578
DROP SCHEMA lock_schema1 CASCADE;
6679
DROP ROLE regress_rol_lock1;
6780

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.