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

Commit34c20de

Browse files
committed
Allow to lock views.
Now all tables used in view definitions can be recursively locked by aLOCK command.Author: Yugo NagataReviewed by Robert Haas, Thomas Munro and me.Discussion:https://postgr.es/m/20171011183629.eb2817b3.nagata%40sraoss.co.jp
1 parentfb60478 commit34c20de

File tree

4 files changed

+285
-18
lines changed

4 files changed

+285
-18
lines changed

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

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,11 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ]
4545
end.)
4646
</para>
4747

48+
<para>
49+
When a view is specified to be locked, all relations appearing in the view
50+
definition query are also locked recursively with the same lock mode.
51+
</para>
52+
4853
<para>
4954
When acquiring locks automatically for commands that reference
5055
tables, <productname>PostgreSQL</productname> always uses the least

‎src/backend/commands/lockcmds.c‎

Lines changed: 126 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -23,11 +23,15 @@
2323
#include"utils/acl.h"
2424
#include"utils/lsyscache.h"
2525
#include"utils/syscache.h"
26+
#include"rewrite/rewriteHandler.h"
27+
#include"access/heapam.h"
28+
#include"nodes/nodeFuncs.h"
2629

27-
staticvoidLockTableRecurse(Oidreloid,LOCKMODElockmode,boolnowait);
28-
staticAclResultLockTableAclCheck(Oidrelid,LOCKMODElockmode);
30+
staticvoidLockTableRecurse(Oidreloid,LOCKMODElockmode,boolnowait,Oiduserid);
31+
staticAclResultLockTableAclCheck(Oidrelid,LOCKMODElockmode,Oiduserid);
2932
staticvoidRangeVarCallbackForLockTable(constRangeVar*rv,Oidrelid,
3033
Oidoldrelid,void*arg);
34+
staticvoidLockViewRecurse(Oidreloid,Oidroot_reloid,LOCKMODElockmode,boolnowait);
3135

3236
/*
3337
* LOCK TABLE
@@ -62,8 +66,10 @@ LockTableCommand(LockStmt *lockstmt)
6266
RangeVarCallbackForLockTable,
6367
(void*)&lockstmt->mode);
6468

65-
if (recurse)
66-
LockTableRecurse(reloid,lockstmt->mode,lockstmt->nowait);
69+
if (get_rel_relkind(reloid)==RELKIND_VIEW)
70+
LockViewRecurse(reloid,reloid,lockstmt->mode,lockstmt->nowait);
71+
elseif (recurse)
72+
LockTableRecurse(reloid,lockstmt->mode,lockstmt->nowait,GetUserId());
6773
}
6874
}
6975

@@ -86,15 +92,17 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
8692
return;/* woops, concurrently dropped; no permissions
8793
* check */
8894

89-
/* Currently, we only allow plain tables to be locked */
90-
if (relkind!=RELKIND_RELATION&&relkind!=RELKIND_PARTITIONED_TABLE)
95+
96+
/* Currently, we only allow plain tables or views to be locked */
97+
if (relkind!=RELKIND_RELATION&&relkind!=RELKIND_PARTITIONED_TABLE&&
98+
relkind!=RELKIND_VIEW)
9199
ereport(ERROR,
92100
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
93-
errmsg("\"%s\" is not a table",
101+
errmsg("\"%s\" is not a table or a view",
94102
rv->relname)));
95103

96104
/* Check permissions. */
97-
aclresult=LockTableAclCheck(relid,lockmode);
105+
aclresult=LockTableAclCheck(relid,lockmode,GetUserId());
98106
if (aclresult!=ACLCHECK_OK)
99107
aclcheck_error(aclresult,get_relkind_objtype(get_rel_relkind(relid)),rv->relname);
100108
}
@@ -107,7 +115,7 @@ RangeVarCallbackForLockTable(const RangeVar *rv, Oid relid, Oid oldrelid,
107115
* multiply-inheriting children more than once, but that's no problem.
108116
*/
109117
staticvoid
110-
LockTableRecurse(Oidreloid,LOCKMODElockmode,boolnowait)
118+
LockTableRecurse(Oidreloid,LOCKMODElockmode,boolnowait,Oiduserid)
111119
{
112120
List*children;
113121
ListCell*lc;
@@ -120,7 +128,7 @@ LockTableRecurse(Oid reloid, LOCKMODE lockmode, bool nowait)
120128
AclResultaclresult;
121129

122130
/* Check permissions before acquiring the lock. */
123-
aclresult=LockTableAclCheck(childreloid,lockmode);
131+
aclresult=LockTableAclCheck(childreloid,lockmode,userid);
124132
if (aclresult!=ACLCHECK_OK)
125133
{
126134
char*relname=get_rel_name(childreloid);
@@ -157,15 +165,120 @@ LockTableRecurse(Oid reloid, LOCKMODE lockmode, bool nowait)
157165
continue;
158166
}
159167

160-
LockTableRecurse(childreloid,lockmode,nowait);
168+
LockTableRecurse(childreloid,lockmode,nowait,userid);
161169
}
162170
}
163171

172+
/*
173+
* Apply LOCK TABLE recursively over a view
174+
*
175+
* All tables and views appearing in the view definition query are locked
176+
* recursively with the same lock mode.
177+
*/
178+
179+
typedefstruct
180+
{
181+
Oidroot_reloid;
182+
LOCKMODElockmode;
183+
boolnowait;
184+
Oidviewowner;
185+
Oidviewoid;
186+
}LockViewRecurse_context;
187+
188+
staticbool
189+
LockViewRecurse_walker(Node*node,LockViewRecurse_context*context)
190+
{
191+
if (node==NULL)
192+
return false;
193+
194+
if (IsA(node,Query))
195+
{
196+
Query*query= (Query*)node;
197+
ListCell*rtable;
198+
199+
foreach(rtable,query->rtable)
200+
{
201+
RangeTblEntry*rte=lfirst(rtable);
202+
AclResultaclresult;
203+
204+
Oidrelid=rte->relid;
205+
charrelkind=rte->relkind;
206+
char*relname=get_rel_name(relid);
207+
208+
/* The OLD and NEW placeholder entries in the view's rtable are skipped. */
209+
if (relid==context->viewoid&&
210+
(!strcmp(rte->eref->aliasname,"old")|| !strcmp(rte->eref->aliasname,"new")))
211+
continue;
212+
213+
/* Currently, we only allow plain tables or views to be locked. */
214+
if (relkind!=RELKIND_RELATION&&relkind!=RELKIND_PARTITIONED_TABLE&&
215+
relkind!=RELKIND_VIEW)
216+
continue;
217+
218+
/* Check infinite recursion in the view definition. */
219+
if (relid==context->root_reloid)
220+
ereport(ERROR,
221+
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
222+
errmsg("infinite recursion detected in rules for relation \"%s\"",
223+
get_rel_name(context->root_reloid))));
224+
225+
/* Check permissions with the view owner's privilege. */
226+
aclresult=LockTableAclCheck(relid,context->lockmode,context->viewowner);
227+
if (aclresult!=ACLCHECK_OK)
228+
aclcheck_error(aclresult,get_relkind_objtype(relkind),relname);
229+
230+
/* We have enough rights to lock the relation; do so. */
231+
if (!context->nowait)
232+
LockRelationOid(relid,context->lockmode);
233+
elseif (!ConditionalLockRelationOid(relid,context->lockmode))
234+
ereport(ERROR,
235+
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
236+
errmsg("could not obtain lock on relation \"%s\"",
237+
relname)));
238+
239+
if (relkind==RELKIND_VIEW)
240+
LockViewRecurse(relid,context->root_reloid,context->lockmode,context->nowait);
241+
elseif (rte->inh)
242+
LockTableRecurse(relid,context->lockmode,context->nowait,context->viewowner);
243+
}
244+
245+
returnquery_tree_walker(query,
246+
LockViewRecurse_walker,
247+
context,
248+
QTW_IGNORE_JOINALIASES);
249+
}
250+
251+
returnexpression_tree_walker(node,
252+
LockViewRecurse_walker,
253+
context);
254+
}
255+
256+
staticvoid
257+
LockViewRecurse(Oidreloid,Oidroot_reloid,LOCKMODElockmode,boolnowait)
258+
{
259+
LockViewRecurse_contextcontext;
260+
261+
Relationview;
262+
Query*viewquery;
263+
264+
view=heap_open(reloid,NoLock);
265+
viewquery=get_view_query(view);
266+
heap_close(view,NoLock);
267+
268+
context.root_reloid=root_reloid;
269+
context.lockmode=lockmode;
270+
context.nowait=nowait;
271+
context.viewowner=view->rd_rel->relowner;
272+
context.viewoid=reloid;
273+
274+
LockViewRecurse_walker((Node*)viewquery,&context);
275+
}
276+
164277
/*
165278
* Check whether the current user is permitted to lock this relation.
166279
*/
167280
staticAclResult
168-
LockTableAclCheck(Oidreloid,LOCKMODElockmode)
281+
LockTableAclCheck(Oidreloid,LOCKMODElockmode,Oiduserid)
169282
{
170283
AclResultaclresult;
171284
AclModeaclmask;
@@ -178,7 +291,7 @@ LockTableAclCheck(Oid reloid, LOCKMODE lockmode)
178291
else
179292
aclmask=ACL_UPDATE |ACL_DELETE |ACL_TRUNCATE;
180293

181-
aclresult=pg_class_aclcheck(reloid,GetUserId(),aclmask);
294+
aclresult=pg_class_aclcheck(reloid,userid,aclmask);
182295

183296
returnaclresult;
184297
}

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

Lines changed: 97 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,13 @@
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 TABLE lock_tbl1a (a BIGINT);
9+
CREATE VIEW lock_view1 AS SELECT * FROM lock_tbl1;
10+
CREATE VIEW lock_view2(a,b) AS SELECT * FROM lock_tbl1, lock_tbl1a;
11+
CREATE VIEW lock_view3 AS SELECT * from lock_view2;
12+
CREATE VIEW lock_view4 AS SELECT (select a from lock_tbl1a limit 1) from lock_tbl1;
13+
CREATE VIEW lock_view5 AS SELECT * from lock_tbl1 where a in (select * from lock_tbl1a);
14+
CREATE VIEW lock_view6 AS SELECT * from (select * from lock_tbl1) sub;
915
CREATE ROLE regress_rol_lock1;
1016
ALTER ROLE regress_rol_lock1 SET search_path = lock_schema1;
1117
GRANT USAGE ON SCHEMA lock_schema1 TO regress_rol_lock1;
@@ -30,8 +36,90 @@ LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT;
3036
LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;
3137
LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT;
3238
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
39+
ROLLBACK;
40+
-- Verify that we can lock views.
41+
BEGIN TRANSACTION;
42+
LOCK TABLE lock_view1 IN EXCLUSIVE MODE;
43+
-- lock_view1 and lock_tbl1 are locked.
44+
select relname from pg_locks l, pg_class c
45+
where l.relation = c.oid and relname like '%lock_%' and mode = 'ExclusiveLock'
46+
order by relname;
47+
relname
48+
------------
49+
lock_tbl1
50+
lock_view1
51+
(2 rows)
52+
53+
ROLLBACK;
54+
BEGIN TRANSACTION;
55+
LOCK TABLE lock_view2 IN EXCLUSIVE MODE;
56+
-- lock_view1, lock_tbl1, and lock_tbl1a are locked.
57+
select relname from pg_locks l, pg_class c
58+
where l.relation = c.oid and relname like '%lock_%' and mode = 'ExclusiveLock'
59+
order by relname;
60+
relname
61+
------------
62+
lock_tbl1
63+
lock_tbl1a
64+
lock_view2
65+
(3 rows)
66+
67+
ROLLBACK;
68+
BEGIN TRANSACTION;
69+
LOCK TABLE lock_view3 IN EXCLUSIVE MODE;
70+
-- lock_view3, lock_view2, lock_tbl1, and lock_tbl1a are locked recursively.
71+
select relname from pg_locks l, pg_class c
72+
where l.relation = c.oid and relname like '%lock_%' and mode = 'ExclusiveLock'
73+
order by relname;
74+
relname
75+
------------
76+
lock_tbl1
77+
lock_tbl1a
78+
lock_view2
79+
lock_view3
80+
(4 rows)
81+
82+
ROLLBACK;
83+
BEGIN TRANSACTION;
84+
LOCK TABLE lock_view4 IN EXCLUSIVE MODE;
85+
-- lock_view4, lock_tbl1, and lock_tbl1a are locked.
86+
select relname from pg_locks l, pg_class c
87+
where l.relation = c.oid and relname like '%lock_%' and mode = 'ExclusiveLock'
88+
order by relname;
89+
relname
90+
------------
91+
lock_tbl1
92+
lock_tbl1a
93+
lock_view4
94+
(3 rows)
95+
96+
ROLLBACK;
97+
BEGIN TRANSACTION;
98+
LOCK TABLE lock_view5 IN EXCLUSIVE MODE;
99+
-- lock_view5, lock_tbl1, and lock_tbl1a are locked.
100+
select relname from pg_locks l, pg_class c
101+
where l.relation = c.oid and relname like '%lock_%' and mode = 'ExclusiveLock'
102+
order by relname;
103+
relname
104+
------------
105+
lock_tbl1
106+
lock_tbl1a
107+
lock_view5
108+
(3 rows)
109+
110+
ROLLBACK;
111+
BEGIN TRANSACTION;
112+
LOCK TABLE lock_view6 IN EXCLUSIVE MODE;
113+
-- lock_view6 an lock_tbl1 are locked.
114+
select relname from pg_locks l, pg_class c
115+
where l.relation = c.oid and relname like '%lock_%' and mode = 'ExclusiveLock'
116+
order by relname;
117+
relname
118+
------------
119+
lock_tbl1
120+
lock_view6
121+
(2 rows)
122+
35123
ROLLBACK;
36124
-- Verify that we can lock a table with inheritance children.
37125
CREATE TABLE lock_tbl2 (b BIGINT) INHERITS (lock_tbl1);
@@ -54,10 +142,16 @@ RESET ROLE;
54142
--
55143
-- Clean up
56144
--
145+
DROP VIEW lock_view6;
146+
DROP VIEW lock_view5;
147+
DROP VIEW lock_view4;
148+
DROP VIEW lock_view3;
149+
DROP VIEW lock_view2;
57150
DROP VIEW lock_view1;
58151
DROP TABLE lock_tbl3;
59152
DROP TABLE lock_tbl2;
60153
DROP TABLE lock_tbl1;
154+
DROP TABLE lock_tbl1a;
61155
DROP SCHEMA lock_schema1 CASCADE;
62156
DROP ROLE regress_rol_lock1;
63157
-- atomic ops tests

0 commit comments

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