Age Owner TLA Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * view.c
4 : * use rewrite rules to construct views
5 : *
6 : * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
7 : * Portions Copyright (c) 1994, Regents of the University of California
8 : *
9 : *
10 : * IDENTIFICATION
11 : * src/backend/commands/view.c
12 : *
13 : *-------------------------------------------------------------------------
14 : */
15 : #include "postgres.h"
16 :
17 : #include "access/relation.h"
18 : #include "access/xact.h"
19 : #include "catalog/namespace.h"
20 : #include "commands/defrem.h"
21 : #include "commands/tablecmds.h"
22 : #include "commands/view.h"
23 : #include "miscadmin.h"
24 : #include "nodes/makefuncs.h"
25 : #include "nodes/nodeFuncs.h"
26 : #include "parser/analyze.h"
27 : #include "parser/parse_relation.h"
28 : #include "rewrite/rewriteDefine.h"
29 : #include "rewrite/rewriteHandler.h"
30 : #include "rewrite/rewriteManip.h"
31 : #include "rewrite/rewriteSupport.h"
32 : #include "utils/acl.h"
33 : #include "utils/builtins.h"
34 : #include "utils/lsyscache.h"
35 : #include "utils/rel.h"
36 : #include "utils/syscache.h"
37 :
38 : static void checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc);
39 :
40 : /*---------------------------------------------------------------------
41 : * DefineVirtualRelation
42 : *
43 : * Create a view relation and use the rules system to store the query
44 : * for the view.
45 : *
46 : * EventTriggerAlterTableStart must have been called already.
47 : *---------------------------------------------------------------------
48 : */
49 : static ObjectAddress
4101 rhaas 50 CBC 44058 : DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
51 : List *options, Query *viewParse)
52 : {
53 : Oid viewOid;
54 : LOCKMODE lockmode;
8227 tgl 55 44058 : CreateStmt *createStmt = makeNode(CreateStmt);
56 : List *attrList;
57 : ListCell *t;
58 :
59 : /*
60 : * create a list of ColumnDef nodes based on the names and types of the
61 : * (non-junk) targetlist items from the view's SELECT list.
62 : */
9345 bruce 63 44058 : attrList = NIL;
7522 64 484858 : foreach(t, tlist)
65 : {
2477 tgl 66 440800 : TargetEntry *tle = (TargetEntry *) lfirst(t);
67 :
6577 68 440800 : if (!tle->resjunk)
69 : {
2477 70 438948 : ColumnDef *def = makeColumnDef(tle->resname,
71 438948 : exprType((Node *) tle->expr),
72 438948 : exprTypmod((Node *) tle->expr),
2118 73 438948 : exprCollation((Node *) tle->expr));
74 :
75 : /*
76 : * It's possible that the column is of a collatable type but the
77 : * collation could not be resolved, so double-check.
78 : */
4414 79 438948 : if (type_is_collatable(exprType((Node *) tle->expr)))
80 : {
4404 81 252776 : if (!OidIsValid(def->collOid))
4404 tgl 82 UBC 0 : ereport(ERROR,
83 : (errcode(ERRCODE_INDETERMINATE_COLLATION),
84 : errmsg("could not determine which collation to use for view column \"%s\"",
85 : def->colname),
86 : errhint("Use the COLLATE clause to set the collation explicitly.")));
87 : }
88 : else
4404 tgl 89 CBC 186172 : Assert(!OidIsValid(def->collOid));
90 :
9345 bruce 91 438948 : attrList = lappend(attrList, def);
92 : }
93 : }
94 :
95 : /*
96 : * Look up, check permissions on, and lock the creation namespace; also
97 : * check for a preexisting view with the same name. This will also set
98 : * relation->relpersistence to RELPERSISTENCE_TEMP if the selected
99 : * namespace is temporary.
100 : */
4101 rhaas 101 44058 : lockmode = replace ? AccessExclusiveLock : NoLock;
4099 magnus 102 44058 : (void) RangeVarGetAndCheckCreationNamespace(relation, lockmode, &viewOid);
103 :
7524 tgl 104 44052 : if (OidIsValid(viewOid) && replace)
105 : {
106 : Relation rel;
107 : TupleDesc descriptor;
4126 rhaas 108 112 : List *atcmds = NIL;
109 : AlterTableCmd *atcmd;
110 : ObjectAddress address;
111 :
112 : /* Relation is already locked, but we must build a relcache entry. */
4101 113 112 : rel = relation_open(viewOid, NoLock);
114 :
115 : /* Make sure it *is* a view. */
7524 tgl 116 112 : if (rel->rd_rel->relkind != RELKIND_VIEW)
7203 tgl 117 UBC 0 : ereport(ERROR,
118 : (errcode(ERRCODE_WRONG_OBJECT_TYPE),
119 : errmsg("\"%s\" is not a view",
120 : RelationGetRelationName(rel))));
121 :
122 : /* Also check it's not in use already */
5228 tgl 123 CBC 112 : CheckTableNotInUse(rel, "CREATE OR REPLACE VIEW");
124 :
125 : /*
126 : * Due to the namespace visibility rules for temporary objects, we
127 : * should only end up replacing a temporary view with another
128 : * temporary view, and similarly for permanent views.
129 : */
4500 rhaas 130 112 : Assert(relation->relpersistence == rel->rd_rel->relpersistence);
131 :
132 : /*
133 : * Create a tuple descriptor to compare against the existing view, and
134 : * verify that the old column list is an initial prefix of the new
135 : * column list.
136 : */
5228 tgl 137 112 : descriptor = BuildDescForRelation(attrList);
138 112 : checkViewTupleDesc(descriptor, rel->rd_att);
139 :
140 : /*
141 : * If new attributes have been added, we must add pg_attribute entries
142 : * for them. It is convenient (although overkill) to use the ALTER
143 : * TABLE ADD COLUMN infrastructure for this.
144 : *
145 : * Note that we must do this before updating the query for the view,
146 : * since the rules system requires that the correct view columns be in
147 : * place when defining the new rules.
148 : *
149 : * Also note that ALTER TABLE doesn't run parse transformation on
150 : * AT_AddColumnToView commands. The ColumnDef we supply must be ready
151 : * to execute as-is.
152 : */
153 97 : if (list_length(attrList) > rel->rd_att->natts)
154 : {
155 : ListCell *c;
5237 bruce 156 12 : int skip = rel->rd_att->natts;
157 :
5228 tgl 158 48 : foreach(c, attrList)
159 : {
160 36 : if (skip > 0)
161 : {
162 24 : skip--;
5237 bruce 163 24 : continue;
164 : }
165 12 : atcmd = makeNode(AlterTableCmd);
166 12 : atcmd->subtype = AT_AddColumnToView;
5228 tgl 167 12 : atcmd->def = (Node *) lfirst(c);
5237 bruce 168 12 : atcmds = lappend(atcmds, atcmd);
169 : }
170 :
171 : /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
2300 dean.a.rasheed 172 12 : AlterTableInternal(viewOid, atcmds, true);
173 :
174 : /* Make the new view columns visible */
175 12 : CommandCounterIncrement();
176 : }
177 :
178 : /*
179 : * Update the query for the view.
180 : *
181 : * Note that we must do this before updating the view options, because
182 : * the new options may not be compatible with the old view query (for
183 : * example if we attempt to add the WITH CHECK OPTION, we require that
184 : * the new view be automatically updatable, but the old view may not
185 : * have been).
186 : */
187 97 : StoreViewQuery(viewOid, viewParse, replace);
188 :
189 : /* Make the new view query visible */
190 97 : CommandCounterIncrement();
191 :
192 : /*
193 : * Update the view's options.
194 : *
195 : * The new options list replaces the existing options list, even if
196 : * it's empty.
197 : */
198 97 : atcmd = makeNode(AlterTableCmd);
199 97 : atcmd->subtype = AT_ReplaceRelOptions;
200 97 : atcmd->def = (Node *) options;
201 97 : atcmds = list_make1(atcmd);
202 :
203 : /* EventTriggerAlterTableStart called by ProcessUtilitySlow */
4126 rhaas 204 97 : AlterTableInternal(viewOid, atcmds, true);
205 :
206 : /*
207 : * There is very little to do here to update the view's dependencies.
208 : * Most view-level dependency relationships, such as those on the
209 : * owner, schema, and associated composite type, aren't changing.
210 : * Because we don't allow changing type or collation of an existing
211 : * view column, those dependencies of the existing columns don't
212 : * change either, while the AT_AddColumnToView machinery took care of
213 : * adding such dependencies for new view columns. The dependencies of
214 : * the view's query could have changed arbitrarily, but that was dealt
215 : * with inside StoreViewQuery. What remains is only to check that
216 : * view replacement is allowed when we're creating an extension.
217 : */
2959 alvherre 218 97 : ObjectAddressSet(address, RelationRelationId, viewOid);
219 :
244 tgl 220 97 : recordDependencyOnCurrentExtension(&address, true);
221 :
222 : /*
223 : * Seems okay, so return the OID of the pre-existing view.
224 : */
7522 bruce 225 96 : relation_close(rel, NoLock); /* keep the lock! */
226 :
2959 alvherre 227 96 : return address;
228 : }
229 : else
230 : {
231 : ObjectAddress address;
232 :
233 : /*
234 : * Set the parameters for keys/inheritance etc. All of these are
235 : * uninteresting for views...
236 : */
4060 peter_e 237 43940 : createStmt->relation = relation;
7524 tgl 238 43940 : createStmt->tableElts = attrList;
239 43940 : createStmt->inhRelations = NIL;
240 43940 : createStmt->constraints = NIL;
4126 rhaas 241 43940 : createStmt->options = options;
7454 tgl 242 43940 : createStmt->oncommit = ONCOMMIT_NOOP;
6869 243 43940 : createStmt->tablespacename = NULL;
4641 rhaas 244 43940 : createStmt->if_not_exists = false;
245 :
246 : /*
247 : * Create the relation (this will error out if there's an existing
248 : * view, so we don't need more code to complain if "replace" is
249 : * false).
250 : */
2314 251 43940 : address = DefineRelation(createStmt, RELKIND_VIEW, InvalidOid, NULL,
252 : NULL);
2959 alvherre 253 43931 : Assert(address.objectId != InvalidOid);
254 :
255 : /* Make the new view relation visible */
2300 dean.a.rasheed 256 43931 : CommandCounterIncrement();
257 :
258 : /* Store the query for the view */
259 43931 : StoreViewQuery(address.objectId, viewParse, replace);
260 :
2959 alvherre 261 43931 : return address;
262 : }
263 : }
264 :
265 : /*
266 : * Verify that tupledesc associated with proposed new view definition
267 : * matches tupledesc of old view. This is basically a cut-down version
268 : * of equalTupleDescs(), with code added to generate specific complaints.
269 : * Also, we allow the new tupledesc to have more columns than the old.
270 : */
271 : static void
7524 tgl 272 112 : checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc)
273 : {
274 : int i;
275 :
5237 bruce 276 112 : if (newdesc->natts < olddesc->natts)
7203 tgl 277 3 : ereport(ERROR,
278 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
279 : errmsg("cannot drop columns from view")));
280 :
5237 bruce 281 299 : for (i = 0; i < olddesc->natts; i++)
282 : {
2058 andres 283 202 : Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
284 202 : Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
285 :
286 : /* XXX msg not right, but we don't support DROP COL on view anyway */
7524 tgl 287 202 : if (newattr->attisdropped != oldattr->attisdropped)
7203 tgl 288 UBC 0 : ereport(ERROR,
289 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
290 : errmsg("cannot drop columns from view")));
291 :
7524 tgl 292 CBC 202 : if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0)
7203 293 3 : ereport(ERROR,
294 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
295 : errmsg("cannot change name of view column \"%s\" to \"%s\"",
296 : NameStr(oldattr->attname),
297 : NameStr(newattr->attname)),
298 : errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.")));
299 :
300 : /*
301 : * We cannot allow type, typmod, or collation to change, since these
302 : * properties may be embedded in Vars of other views/rules referencing
303 : * this one. Other column attributes can be ignored.
304 : */
7524 305 199 : if (newattr->atttypid != oldattr->atttypid ||
306 196 : newattr->atttypmod != oldattr->atttypmod)
7203 307 6 : ereport(ERROR,
308 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
309 : errmsg("cannot change data type of view column \"%s\" from %s to %s",
310 : NameStr(oldattr->attname),
311 : format_type_with_typemod(oldattr->atttypid,
312 : oldattr->atttypmod),
313 : format_type_with_typemod(newattr->atttypid,
314 : newattr->atttypmod))));
315 :
316 : /*
317 : * At this point, attcollations should be both valid or both invalid,
318 : * so applying get_collation_name unconditionally should be fine.
319 : */
418 320 193 : if (newattr->attcollation != oldattr->attcollation)
321 3 : ereport(ERROR,
322 : (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
323 : errmsg("cannot change collation of view column \"%s\" from \"%s\" to \"%s\"",
324 : NameStr(oldattr->attname),
325 : get_collation_name(oldattr->attcollation),
326 : get_collation_name(newattr->attcollation))));
327 : }
328 :
329 : /*
330 : * We ignore the constraint fields. The new view desc can't have any
331 : * constraints, and the only ones that could be on the old view are
332 : * defaults, which we are happy to leave in place.
333 : */
7524 334 97 : }
335 :
336 : static void
5704 337 44240 : DefineViewRules(Oid viewOid, Query *viewParse, bool replace)
338 : {
339 : /*
340 : * Set up the ON SELECT rule. Since the query has already been through
341 : * parse analysis, we use DefineQueryRewrite() directly.
342 : */
5871 343 44240 : DefineQueryRewrite(pstrdup(ViewSelectRuleName),
344 : viewOid,
345 : NULL,
346 : CMD_SELECT,
347 : true,
348 : replace,
349 44240 : list_make1(viewParse));
350 :
351 : /*
352 : * Someday: automatic ON INSERT, etc
353 : */
9345 bruce 354 44240 : }
355 :
356 : /*
357 : * DefineView
358 : * Execute a CREATE VIEW command.
359 : */
360 : ObjectAddress
2276 tgl 361 44067 : DefineView(ViewStmt *stmt, const char *queryString,
362 : int stmt_location, int stmt_len)
363 : {
2276 tgl 364 ECB : RawStmt *rawstmt;
365 : Query *viewParse;
5871 366 : RangeVar *view;
3552 sfrost 367 EUB : ListCell *cell;
368 : bool check_option;
369 : ObjectAddress address;
370 :
371 : /*
372 : * Run parse analysis to convert the raw parse tree to a Query. Note this
373 : * also acquires sufficient locks on the source table(s).
374 : */
2276 tgl 375 GIC 44067 : rawstmt = makeNode(RawStmt);
660 376 44067 : rawstmt->stmt = stmt->query;
2276 tgl 377 CBC 44067 : rawstmt->stmt_location = stmt_location;
2276 tgl 378 GIC 44067 : rawstmt->stmt_len = stmt_len;
2276 tgl 379 ECB :
401 peter 380 GIC 44067 : viewParse = parse_analyze_fixedparams(rawstmt, queryString, NULL, 0, NULL);
381 :
5871 tgl 382 ECB : /*
383 : * The grammar should ensure that the result is a single SELECT Query.
4038 384 : * However, it doesn't forbid SELECT INTO, so we have to check for that.
385 : */
4038 tgl 386 GIC 44061 : if (!IsA(viewParse, Query))
4038 tgl 387 LBC 0 : elog(ERROR, "unexpected parse analysis result");
4038 tgl 388 GBC 44061 : if (viewParse->utilityStmt != NULL &&
4038 tgl 389 CBC 3 : IsA(viewParse->utilityStmt, CreateTableAsStmt))
390 3 : ereport(ERROR,
4038 tgl 391 ECB : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
392 : errmsg("views must not contain SELECT INTO")));
2276 tgl 393 GIC 44058 : if (viewParse->commandType != CMD_SELECT)
5871 tgl 394 UIC 0 : elog(ERROR, "unexpected parse analysis result");
5871 tgl 395 ECB :
4426 tgl 396 EUB : /*
397 : * Check for unsupported cases. These tests are redundant with ones in
398 : * DefineQueryRewrite(), but that function will complain about a bogus ON
399 : * SELECT rule, and we'd rather the message complain about a view.
400 : */
4426 tgl 401 GIC 44058 : if (viewParse->hasModifyingCTE)
4426 tgl 402 UIC 0 : ereport(ERROR,
4426 tgl 403 ECB : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2118 tgl 404 EUB : errmsg("views must not contain data-modifying statements in WITH")));
405 :
406 : /*
407 : * If the user specified the WITH CHECK OPTION, add it to the list of
408 : * reloptions.
409 : */
3552 sfrost 410 GIC 44058 : if (stmt->withCheckOption == LOCAL_CHECK_OPTION)
411 12 : stmt->options = lappend(stmt->options,
412 12 : makeDefElem("check_option",
2118 tgl 413 12 : (Node *) makeString("local"), -1));
3552 sfrost 414 CBC 44046 : else if (stmt->withCheckOption == CASCADED_CHECK_OPTION)
415 48 : stmt->options = lappend(stmt->options,
416 48 : makeDefElem("check_option",
2118 tgl 417 GIC 48 : (Node *) makeString("cascaded"), -1));
3552 sfrost 418 ECB :
419 : /*
420 : * Check that the view is auto-updatable if WITH CHECK OPTION was
421 : * specified.
422 : */
3552 sfrost 423 GIC 44058 : check_option = false;
424 :
425 45141 : foreach(cell, stmt->options)
426 : {
427 1083 : DefElem *defel = (DefElem *) lfirst(cell);
428 :
1899 tgl 429 1083 : if (strcmp(defel->defname, "check_option") == 0)
3552 sfrost 430 CBC 61 : check_option = true;
3552 sfrost 431 ECB : }
432 :
433 : /*
434 : * If the check option is specified, look to see if the view is actually
435 : * auto-updatable or not.
436 : */
3552 sfrost 437 GIC 44058 : if (check_option)
438 : {
439 : const char *view_updatable_error =
3260 bruce 440 CBC 61 : view_query_is_auto_updatable(viewParse, true);
441 :
3552 sfrost 442 GIC 61 : if (view_updatable_error)
3552 sfrost 443 UIC 0 : ereport(ERROR,
444 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3101 peter_e 445 ECB : errmsg("WITH CHECK OPTION is supported only on automatically updatable views"),
1692 michael 446 : errhint("%s", _(view_updatable_error))));
447 : }
448 :
449 : /*
450 : * If a list of column names was given, run through and insert these into
451 : * the actual query tree. - thomas 2000-03-08
452 : */
5871 tgl 453 GIC 44058 : if (stmt->aliases != NIL)
454 : {
455 28 : ListCell *alist_item = list_head(stmt->aliases);
456 : ListCell *targetList;
457 :
458 40 : foreach(targetList, viewParse->targetList)
459 : {
2190 460 40 : TargetEntry *te = lfirst_node(TargetEntry, targetList);
461 :
462 : /* junk columns don't get aliases */
5871 463 40 : if (te->resjunk)
5871 tgl 464 UIC 0 : continue;
5871 tgl 465 GIC 40 : te->resname = pstrdup(strVal(lfirst(alist_item)));
1364 466 40 : alist_item = lnext(stmt->aliases, alist_item);
5871 467 40 : if (alist_item == NULL)
468 28 : break; /* done assigning aliases */
469 : }
470 :
471 28 : if (alist_item != NULL)
5871 tgl 472 UIC 0 : ereport(ERROR,
473 : (errcode(ERRCODE_SYNTAX_ERROR),
474 : errmsg("CREATE VIEW specifies more column "
475 : "names than columns")));
476 : }
477 :
478 : /* Unlogged views are not sensible. */
4298 rhaas 479 GIC 44058 : if (stmt->view->relpersistence == RELPERSISTENCE_UNLOGGED)
4298 rhaas 480 UIC 0 : ereport(ERROR,
481 : (errcode(ERRCODE_SYNTAX_ERROR),
482 : errmsg("views cannot be unlogged because they do not have storage")));
483 :
484 : /*
485 : * If the user didn't explicitly ask for a temporary view, check whether
486 : * we need one implicitly. We allow TEMP to be inserted automatically as
487 : * long as the CREATE command is consistent with that --- no explicit
488 : * schema name.
489 : */
2118 tgl 490 GIC 44058 : view = copyObject(stmt->view); /* don't corrupt original command */
4500 rhaas 491 44058 : if (view->relpersistence == RELPERSISTENCE_PERMANENT
3689 kgrittn 492 43932 : && isQueryUsingTempRelation(viewParse))
493 : {
4500 rhaas 494 54 : view->relpersistence = RELPERSISTENCE_TEMP;
5871 tgl 495 54 : ereport(NOTICE,
496 : (errmsg("view \"%s\" will be a temporary view",
497 : view->relname)));
498 : }
499 :
500 : /*
501 : * Create the view relation
502 : *
503 : * NOTE: if it already exists and replace is false, the xact will be
504 : * aborted.
505 : */
2959 alvherre 506 44058 : address = DefineVirtualRelation(view, viewParse->targetList,
2300 dean.a.rasheed 507 44058 : stmt->replace, stmt->options, viewParse);
508 :
2959 alvherre 509 44027 : return address;
510 : }
511 :
512 : /*
513 : * Use the rules system to store the query for the view.
514 : */
515 : void
3689 kgrittn 516 44240 : StoreViewQuery(Oid viewOid, Query *viewParse, bool replace)
517 : {
518 : /*
519 : * Now create the rules associated with the view.
520 : */
521 44240 : DefineViewRules(viewOid, viewParse, replace);
9770 scrappy 522 44240 : }
|
