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 59ab4ac

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 4066b64 commit 59ab4ac
Copy full SHA for 59ab4ac

File tree

Expand file treeCollapse file tree

4 files changed

+42
-25
lines changed
Filter options
Expand file treeCollapse file tree

4 files changed

+42
-25
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/lock.sgml
+9-6Lines changed: 9 additions & 6 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
@@ -115,17 +117,18 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ]
115117
<term><replaceable class="parameter">name</replaceable></term>
116118
<listitem>
117119
<para>
118-
The name (optionally schema-qualified) of an existing table to
119-
lock. If <literal>ONLY</literal> is specified before the table name, only that
120+
The name (optionally schema-qualified) of an existing relation to
121+
lock. If <literal>ONLY</literal> is specified before a table name, only that
120122
table is locked. If <literal>ONLY</literal> is not specified, the table and all
121123
its descendant tables (if any) are locked. Optionally, <literal>*</literal>
122124
can be specified after the table name to explicitly indicate that
123-
descendant tables are included.
125+
descendant tables are included. When locking a view, all relations appearing
126+
in the view definition are locked, regardless of <literal>ONLY</literal>.
124127
</para>
125128

126129
<para>
127130
The command <literal>LOCK TABLE a, b;</literal> is equivalent to
128-
<literal>LOCK TABLE a; LOCK TABLE b;</literal>. The tables are locked
131+
<literal>LOCK TABLE a; LOCK TABLE b;</literal>. The relations are locked
129132
one-by-one in the order specified in the <command>LOCK
130133
TABLE</command> command.
131134
</para>

‎src/backend/commands/lockcmds.c

Copy file name to clipboardExpand all lines: src/backend/commands/lockcmds.c
+9-19Lines changed: 9 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -83,14 +83,6 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
8383
return; /* woops, concurrently dropped; no permissions
8484
* check */
8585

86-
/* Currently, we only allow plain tables or views to be locked */
87-
if (relkind != RELKIND_RELATION && relkind != RELKIND_PARTITIONED_TABLE &&
88-
relkind != RELKIND_VIEW)
89-
ereport(ERROR,
90-
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
91-
errmsg("\"%s\" is not a table or view",
92-
rv->relname)));
93-
9486
/*
9587
* Make note if a temporary relation has been accessed in this
9688
* transaction.
@@ -186,11 +178,13 @@ LockViewRecurse_walker(Node *node, LockViewRecurse_context *context)
186178
foreach(rtable, query->rtable)
187179
{
188180
RangeTblEntry *rte = lfirst(rtable);
181+
Oid relid;
189182
AclResult aclresult;
190183

191-
Oid relid = rte->relid;
192-
char relkind = rte->relkind;
193-
char *relname = get_rel_name(relid);
184+
/* ignore all non-relation RTEs */
185+
if (rte->rtekind != RTE_RELATION)
186+
continue;
187+
relid = rte->relid;
194188

195189
/*
196190
* The OLD and NEW placeholder entries in the view's rtable are
@@ -201,11 +195,6 @@ LockViewRecurse_walker(Node *node, LockViewRecurse_context *context)
201195
strcmp(rte->eref->aliasname, "new") == 0))
202196
continue;
203197

204-
/* Currently, we only allow plain tables or views to be locked. */
205-
if (relkind != RELKIND_RELATION && relkind != RELKIND_PARTITIONED_TABLE &&
206-
relkind != RELKIND_VIEW)
207-
continue;
208-
209198
/* Check infinite recursion in the view definition. */
210199
if (list_member_oid(context->ancestor_views, relid))
211200
ereport(ERROR,
@@ -216,7 +205,8 @@ LockViewRecurse_walker(Node *node, LockViewRecurse_context *context)
216205
/* Check permissions with the view owner's privilege. */
217206
aclresult = LockTableAclCheck(relid, context->lockmode, context->viewowner);
218207
if (aclresult != ACLCHECK_OK)
219-
aclcheck_error(aclresult, get_relkind_objtype(relkind), relname);
208+
aclcheck_error(aclresult, get_relkind_objtype(rte->relkind),
209+
get_rel_name(relid));
220210

221211
/* We have enough rights to lock the relation; do so. */
222212
if (!context->nowait)
@@ -225,9 +215,9 @@ LockViewRecurse_walker(Node *node, LockViewRecurse_context *context)
225215
ereport(ERROR,
226216
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
227217
errmsg("could not obtain lock on relation \"%s\"",
228-
relname)));
218+
get_rel_name(relid))));
229219

230-
if (relkind == RELKIND_VIEW)
220+
if (rte->relkind == RELKIND_VIEW)
231221
LockViewRecurse(relid, context->lockmode, context->nowait, context->ancestor_views);
232222
else if (rte->inh)
233223
LockTableRecurse(relid, context->lockmode, context->nowait);

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/lock.out
+11Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,9 @@ CREATE VIEW lock_view3 AS SELECT * from lock_view2;
1212
CREATE VIEW lock_view4 AS SELECT (select a from lock_tbl1a limit 1) from lock_tbl1;
1313
CREATE VIEW lock_view5 AS SELECT * from lock_tbl1 where a in (select * from lock_tbl1a);
1414
CREATE VIEW lock_view6 AS SELECT * from (select * from lock_tbl1) sub;
15+
CREATE MATERIALIZED VIEW lock_mv1 AS SELECT * FROM lock_view6;
16+
CREATE INDEX lock_mvi1 ON lock_mv1 (a);
17+
CREATE SEQUENCE lock_seq;
1518
CREATE ROLE regress_rol_lock1;
1619
ALTER ROLE regress_rol_lock1 SET search_path = lock_schema1;
1720
GRANT USAGE ON SCHEMA lock_schema1 TO regress_rol_lock1;
@@ -154,9 +157,16 @@ BEGIN;
154157
LOCK TABLE ONLY lock_tbl1;
155158
ROLLBACK;
156159
RESET ROLE;
160+
-- Lock other relations
161+
BEGIN TRANSACTION;
162+
LOCK TABLE lock_mv1;
163+
LOCK TABLE lock_mvi1;
164+
LOCK TABLE lock_seq;
165+
ROLLBACK;
157166
--
158167
-- Clean up
159168
--
169+
DROP MATERIALIZED VIEW lock_mv1;
160170
DROP VIEW lock_view7;
161171
DROP VIEW lock_view6;
162172
DROP VIEW lock_view5;
@@ -168,6 +178,7 @@ DROP TABLE lock_tbl3;
168178
DROP TABLE lock_tbl2;
169179
DROP TABLE lock_tbl1;
170180
DROP TABLE lock_tbl1a;
181+
DROP SEQUENCE lock_seq;
171182
DROP SCHEMA lock_schema1 CASCADE;
172183
DROP ROLE regress_rol_lock1;
173184
-- atomic ops tests

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

Copy file name to clipboardExpand all lines: src/test/regress/sql/lock.sql
+13Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,9 @@ CREATE VIEW lock_view3 AS SELECT * from lock_view2;
1313
CREATE VIEW lock_view4 AS SELECT (select a from lock_tbl1a limit 1) from lock_tbl1;
1414
CREATE VIEW lock_view5 AS SELECT * from lock_tbl1 where a in (select * from lock_tbl1a);
1515
CREATE VIEW lock_view6 AS SELECT * from (select * from lock_tbl1) sub;
16+
CREATE MATERIALIZED VIEW lock_mv1 AS SELECT * FROM lock_view6;
17+
CREATE INDEX lock_mvi1 ON lock_mv1 (a);
18+
CREATE SEQUENCE lock_seq;
1619
CREATE ROLE regress_rol_lock1;
1720
ALTER ROLE regress_rol_lock1 SET search_path = lock_schema1;
1821
GRANT USAGE ON SCHEMA lock_schema1 TO regress_rol_lock1;
@@ -117,9 +120,18 @@ LOCK TABLE ONLY lock_tbl1;
117120
ROLLBACK;
118121
RESET ROLE;
119122

123+
-- Lock other relations
124+
BEGIN TRANSACTION;
125+
LOCK TABLE lock_mv1;
126+
LOCK TABLE lock_mvi1;
127+
LOCK TABLE lock_seq;
128+
ROLLBACK;
129+
130+
120131
--
121132
-- Clean up
122133
--
134+
DROP MATERIALIZED VIEW lock_mv1;
123135
DROP VIEW lock_view7;
124136
DROP VIEW lock_view6;
125137
DROP VIEW lock_view5;
@@ -130,6 +142,7 @@ DROP TABLE lock_tbl3;
130142
DROP TABLE lock_tbl2;
131143
DROP TABLE lock_tbl1;
132144
DROP TABLE lock_tbl1a;
145+
DROP SEQUENCE lock_seq;
133146
DROP SCHEMA lock_schema1 CASCADE;
134147
DROP ROLE regress_rol_lock1;
135148

0 commit comments

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