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

Commit58b1362

Browse files
committed
Fix order of operations in CREATE OR REPLACE VIEW.
When CREATE OR REPLACE VIEW acts on an existing view, don't update theview options until after the view query has been updated.This is necessary in the case where CREATE OR REPLACE VIEW is used onan existing view that is not updatable, and the new view is updatableand specifies the WITH CHECK OPTION. In this case, attempting to applythe new options to the view before updating its query fails, becausethe options are applied using the ALTER TABLE infrastructure whichchecks that WITH CHECK OPTION is only applied to an updatable view.If new columns are being added to the view, that is also done usingthe ALTER TABLE infrastructure, but it is important that that still bedone before updating the view query, because the rules system checksthat the query columns match those on the view relation. Added acomment to explain that, in case someone is tempted to move that towhere the view options are now being set.Back-patch to 9.4 where WITH CHECK OPTION was added.Report:https://postgr.es/m/CAEZATCUp%3Dz%3Ds4SzZjr14bfct_bdJNwMPi-gFi3Xc5k1ntbsAgQ%40mail.gmail.com
1 parentcd510f0 commit58b1362

File tree

3 files changed

+76
-29
lines changed

3 files changed

+76
-29
lines changed

‎src/backend/commands/view.c

Lines changed: 49 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -59,15 +59,13 @@ validateWithCheckOption(char *value)
5959
/*---------------------------------------------------------------------
6060
* DefineVirtualRelation
6161
*
62-
* Create the "view" relation. `DefineRelation' does all the work,
63-
* we just provide the correct arguments ... at least when we're
64-
* creating a view. If we're updating an existing view, we have to
65-
* work harder.
62+
* Create a view relation and use the rules system to store the query
63+
* for the view.
6664
*---------------------------------------------------------------------
6765
*/
6866
staticObjectAddress
6967
DefineVirtualRelation(RangeVar*relation,List*tlist,boolreplace,
70-
List*options)
68+
List*options,Query*viewParse)
7169
{
7270
OidviewOid;
7371
LOCKMODElockmode;
@@ -161,19 +159,14 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
161159
descriptor=BuildDescForRelation(attrList);
162160
checkViewTupleDesc(descriptor,rel->rd_att);
163161

164-
/*
165-
* The new options list replaces the existing options list, even if
166-
* it's empty.
167-
*/
168-
atcmd=makeNode(AlterTableCmd);
169-
atcmd->subtype=AT_ReplaceRelOptions;
170-
atcmd->def= (Node*)options;
171-
atcmds=lappend(atcmds,atcmd);
172-
173162
/*
174163
* If new attributes have been added, we must add pg_attribute entries
175164
* for them. It is convenient (although overkill) to use the ALTER
176165
* TABLE ADD COLUMN infrastructure for this.
166+
*
167+
* Note that we must do this before updating the query for the view,
168+
* since the rules system requires that the correct view columns be in
169+
* place when defining the new rules.
177170
*/
178171
if (list_length(attrList)>rel->rd_att->natts)
179172
{
@@ -192,9 +185,38 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
192185
atcmd->def= (Node*)lfirst(c);
193186
atcmds=lappend(atcmds,atcmd);
194187
}
188+
189+
AlterTableInternal(viewOid,atcmds, true);
190+
191+
/* Make the new view columns visible */
192+
CommandCounterIncrement();
195193
}
196194

197-
/* OK, let's do it. */
195+
/*
196+
* Update the query for the view.
197+
*
198+
* Note that we must do this before updating the view options, because
199+
* the new options may not be compatible with the old view query (for
200+
* example if we attempt to add the WITH CHECK OPTION, we require that
201+
* the new view be automatically updatable, but the old view may not
202+
* have been).
203+
*/
204+
StoreViewQuery(viewOid,viewParse,replace);
205+
206+
/* Make the new view query visible */
207+
CommandCounterIncrement();
208+
209+
/*
210+
* Finally update the view options.
211+
*
212+
* The new options list replaces the existing options list, even if
213+
* it's empty.
214+
*/
215+
atcmd=makeNode(AlterTableCmd);
216+
atcmd->subtype=AT_ReplaceRelOptions;
217+
atcmd->def= (Node*)options;
218+
atcmds=list_make1(atcmd);
219+
198220
AlterTableInternal(viewOid,atcmds, true);
199221

200222
ObjectAddressSet(address,RelationRelationId,viewOid);
@@ -211,7 +233,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
211233
ObjectAddressaddress;
212234

213235
/*
214-
*now set the parameters for keys/inheritance etc. All of these are
236+
*Set the parameters for keys/inheritance etc. All of these are
215237
* uninteresting for views...
216238
*/
217239
createStmt->relation=relation;
@@ -224,13 +246,20 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
224246
createStmt->if_not_exists= false;
225247

226248
/*
227-
*finally createthe relation (this will error out if there's an
228-
*existingview, so we don't need more code to complain if "replace"
229-
*isfalse).
249+
*Createthe relation (this will error out if there's an existing
250+
* view, so we don't need more code to complain if "replace" is
251+
* false).
230252
*/
231253
address=DefineRelation(createStmt,RELKIND_VIEW,InvalidOid,NULL,
232254
NULL);
233255
Assert(address.objectId!=InvalidOid);
256+
257+
/* Make the new view relation visible */
258+
CommandCounterIncrement();
259+
260+
/* Store the query for the view */
261+
StoreViewQuery(address.objectId,viewParse,replace);
262+
234263
returnaddress;
235264
}
236265
}
@@ -530,16 +559,7 @@ DefineView(ViewStmt *stmt, const char *queryString)
530559
* aborted.
531560
*/
532561
address=DefineVirtualRelation(view,viewParse->targetList,
533-
stmt->replace,stmt->options);
534-
535-
/*
536-
* The relation we have just created is not visible to any other commands
537-
* running with the same transaction & command id. So, increment the
538-
* command id counter (but do NOT pfree any memory!!!!)
539-
*/
540-
CommandCounterIncrement();
541-
542-
StoreViewQuery(address.objectId,viewParse,stmt->replace);
562+
stmt->replace,stmt->options,viewParse);
543563

544564
returnaddress;
545565
}

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2452,3 +2452,16 @@ DROP VIEW v2;
24522452
DROP VIEW v1;
24532453
DROP TABLE t2;
24542454
DROP TABLE t1;
2455+
--
2456+
-- Test CREATE OR REPLACE VIEW turning a non-updatable view into an
2457+
-- auto-updatable view and adding check options in a single step
2458+
--
2459+
CREATE TABLE t1 (a int, b text);
2460+
CREATE VIEW v1 AS SELECT null::int AS a;
2461+
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE a > 0 WITH CHECK OPTION;
2462+
INSERT INTO v1 VALUES (1, 'ok'); -- ok
2463+
INSERT INTO v1 VALUES (-1, 'invalid'); -- should fail
2464+
ERROR: new row violates check option for view "v1"
2465+
DETAIL: Failing row contains (-1, invalid).
2466+
DROP VIEW v1;
2467+
DROP TABLE t1;

‎src/test/regress/sql/updatable_views.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1098,3 +1098,17 @@ DROP VIEW v2;
10981098
DROPVIEW v1;
10991099
DROPTABLE t2;
11001100
DROPTABLE t1;
1101+
1102+
--
1103+
-- Test CREATE OR REPLACE VIEW turning a non-updatable view into an
1104+
-- auto-updatable view and adding check options in a single step
1105+
--
1106+
CREATETABLEt1 (aint, btext);
1107+
CREATEVIEWv1ASSELECTnull::intAS a;
1108+
CREATE OR REPLACEVIEWv1ASSELECT*FROM t1WHERE a>0 WITHCHECK OPTION;
1109+
1110+
INSERT INTO v1VALUES (1,'ok');-- ok
1111+
INSERT INTO v1VALUES (-1,'invalid');-- should fail
1112+
1113+
DROPVIEW v1;
1114+
DROPTABLE t1;

0 commit comments

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