Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * matview.c
4 : * materialized view support
5 : *
6 : * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
7 : * Portions Copyright (c) 1994, Regents of the University of California
8 : *
9 : *
10 : * IDENTIFICATION
11 : * src/backend/commands/matview.c
12 : *
13 : *-------------------------------------------------------------------------
14 : */
15 : #include "postgres.h"
16 :
17 : #include "access/genam.h"
18 : #include "access/heapam.h"
19 : #include "access/htup_details.h"
20 : #include "access/multixact.h"
21 : #include "access/tableam.h"
22 : #include "access/xact.h"
23 : #include "catalog/indexing.h"
24 : #include "catalog/namespace.h"
25 : #include "catalog/pg_am.h"
26 : #include "catalog/pg_opclass.h"
27 : #include "commands/cluster.h"
28 : #include "commands/matview.h"
29 : #include "commands/tablecmds.h"
30 : #include "commands/tablespace.h"
31 : #include "executor/executor.h"
32 : #include "executor/spi.h"
33 : #include "miscadmin.h"
34 : #include "pgstat.h"
35 : #include "rewrite/rewriteHandler.h"
36 : #include "storage/lmgr.h"
37 : #include "tcop/tcopprot.h"
38 : #include "utils/builtins.h"
39 : #include "utils/lsyscache.h"
40 : #include "utils/rel.h"
41 : #include "utils/snapmgr.h"
42 : #include "utils/syscache.h"
43 :
44 :
45 : typedef struct
46 : {
47 : DestReceiver pub; /* publicly-known function pointers */
48 : Oid transientoid; /* OID of new heap into which to store */
49 : /* These fields are filled by transientrel_startup: */
50 : Relation transientrel; /* relation to write to */
51 : CommandId output_cid; /* cmin to insert in output tuples */
52 : int ti_options; /* table_tuple_insert performance options */
53 : BulkInsertState bistate; /* bulk insert state */
54 : } DR_transientrel;
55 :
56 : static int matview_maintenance_depth = 0;
57 :
58 : static void transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo);
59 : static bool transientrel_receive(TupleTableSlot *slot, DestReceiver *self);
60 : static void transientrel_shutdown(DestReceiver *self);
61 : static void transientrel_destroy(DestReceiver *self);
62 : static uint64 refresh_matview_datafill(DestReceiver *dest, Query *query,
63 : const char *queryString, bool is_create);
64 : static char *make_temptable_name_n(char *tempname, int n);
65 : static void refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
66 : int save_sec_context);
67 : static void refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence);
68 : static bool is_usable_unique_index(Relation indexRel);
69 : static void OpenMatViewIncrementalMaintenance(void);
70 : static void CloseMatViewIncrementalMaintenance(void);
71 :
72 : /*
73 : * SetMatViewPopulatedState
74 : * Mark a materialized view as populated, or not.
75 : *
76 : * NOTE: caller must be holding an appropriate lock on the relation.
77 : */
78 : void
79 612 : SetMatViewPopulatedState(Relation relation, bool newstate)
80 : {
81 : Relation pgrel;
82 : HeapTuple tuple;
83 :
84 : Assert(relation->rd_rel->relkind == RELKIND_MATVIEW);
85 :
86 : /*
87 : * Update relation's pg_class entry. Crucial side-effect: other backends
88 : * (and this one too!) are sent SI message to make them rebuild relcache
89 : * entries.
90 : */
91 612 : pgrel = table_open(RelationRelationId, RowExclusiveLock);
92 612 : tuple = SearchSysCacheCopy1(RELOID,
93 : ObjectIdGetDatum(RelationGetRelid(relation)));
94 612 : if (!HeapTupleIsValid(tuple))
95 0 : elog(ERROR, "cache lookup failed for relation %u",
96 : RelationGetRelid(relation));
97 :
98 612 : ((Form_pg_class) GETSTRUCT(tuple))->relispopulated = newstate;
99 :
100 612 : CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);
101 :
102 612 : heap_freetuple(tuple);
103 612 : table_close(pgrel, RowExclusiveLock);
104 :
105 : /*
106 : * Advance command counter to make the updated pg_class row locally
107 : * visible.
108 : */
109 612 : CommandCounterIncrement();
110 612 : }
111 :
112 : /*
113 : * ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command
114 : *
115 : * If WITH NO DATA was specified, this is effectively like a TRUNCATE;
116 : * otherwise it is like a TRUNCATE followed by an INSERT using the SELECT
117 : * statement associated with the materialized view. The statement node's
118 : * skipData field shows whether the clause was used.
119 : */
120 : ObjectAddress
121 262 : ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
122 : QueryCompletion *qc)
123 : {
124 : Oid matviewOid;
125 : LOCKMODE lockmode;
126 :
127 : /* Determine strength of lock needed. */
128 262 : lockmode = stmt->concurrent ? ExclusiveLock : AccessExclusiveLock;
129 :
130 : /*
131 : * Get a lock until end of transaction.
132 : */
133 262 : matviewOid = RangeVarGetRelidExtended(stmt->relation,
134 : lockmode, 0,
135 : RangeVarCallbackMaintainsTable,
136 : NULL);
137 :
138 446 : return RefreshMatViewByOid(matviewOid, false, stmt->skipData,
139 256 : stmt->concurrent, queryString, qc);
140 : }
141 :
142 : /*
143 : * RefreshMatViewByOid -- refresh materialized view by OID
144 : *
145 : * This refreshes the materialized view by creating a new table and swapping
146 : * the relfilenumbers of the new table and the old materialized view, so the OID
147 : * of the original materialized view is preserved. Thus we do not lose GRANT
148 : * nor references to this materialized view.
149 : *
150 : * If skipData is true, this is effectively like a TRUNCATE; otherwise it is
151 : * like a TRUNCATE followed by an INSERT using the SELECT statement associated
152 : * with the materialized view.
153 : *
154 : * Indexes are rebuilt too, via REINDEX. Since we are effectively bulk-loading
155 : * the new heap, it's better to create the indexes afterwards than to fill them
156 : * incrementally while we load.
157 : *
158 : * The matview's "populated" state is changed based on whether the contents
159 : * reflect the result set of the materialized view's query.
160 : *
161 : * This is also used to populate the materialized view created by CREATE
162 : * MATERIALIZED VIEW command.
163 : */
164 : ObjectAddress
165 618 : RefreshMatViewByOid(Oid matviewOid, bool is_create, bool skipData,
166 : bool concurrent, const char *queryString,
167 : QueryCompletion *qc)
168 : {
169 : Relation matviewRel;
170 : RewriteRule *rule;
171 : List *actions;
172 : Query *dataQuery;
173 : Oid tableSpace;
174 : Oid relowner;
175 : Oid OIDNewHeap;
176 618 : uint64 processed = 0;
177 : char relpersistence;
178 : Oid save_userid;
179 : int save_sec_context;
180 : int save_nestlevel;
181 : ObjectAddress address;
182 :
183 618 : matviewRel = table_open(matviewOid, NoLock);
184 618 : relowner = matviewRel->rd_rel->relowner;
185 :
186 : /*
187 : * Switch to the owner's userid, so that any functions are run as that
188 : * user. Also lock down security-restricted operations and arrange to
189 : * make GUC variable changes local to this command.
190 : */
191 618 : GetUserIdAndSecContext(&save_userid, &save_sec_context);
192 618 : SetUserIdAndSecContext(relowner,
193 : save_sec_context | SECURITY_RESTRICTED_OPERATION);
194 618 : save_nestlevel = NewGUCNestLevel();
195 618 : RestrictSearchPath();
196 :
197 : /* Make sure it is a materialized view. */
198 618 : if (matviewRel->rd_rel->relkind != RELKIND_MATVIEW)
199 0 : ereport(ERROR,
200 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
201 : errmsg("\"%s\" is not a materialized view",
202 : RelationGetRelationName(matviewRel))));
203 :
204 : /* Check that CONCURRENTLY is not specified if not populated. */
205 618 : if (concurrent && !RelationIsPopulated(matviewRel))
206 0 : ereport(ERROR,
207 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
208 : errmsg("CONCURRENTLY cannot be used when the materialized view is not populated")));
209 :
210 : /* Check that conflicting options have not been specified. */
211 618 : if (concurrent && skipData)
212 6 : ereport(ERROR,
213 : (errcode(ERRCODE_SYNTAX_ERROR),
214 : errmsg("%s and %s options cannot be used together",
215 : "CONCURRENTLY", "WITH NO DATA")));
216 :
217 : /*
218 : * Check that everything is correct for a refresh. Problems at this point
219 : * are internal errors, so elog is sufficient.
220 : */
221 612 : if (matviewRel->rd_rel->relhasrules == false ||
222 612 : matviewRel->rd_rules->numLocks < 1)
223 0 : elog(ERROR,
224 : "materialized view \"%s\" is missing rewrite information",
225 : RelationGetRelationName(matviewRel));
226 :
227 612 : if (matviewRel->rd_rules->numLocks > 1)
228 0 : elog(ERROR,
229 : "materialized view \"%s\" has too many rules",
230 : RelationGetRelationName(matviewRel));
231 :
232 612 : rule = matviewRel->rd_rules->rules[0];
233 612 : if (rule->event != CMD_SELECT || !(rule->isInstead))
234 0 : elog(ERROR,
235 : "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule",
236 : RelationGetRelationName(matviewRel));
237 :
238 612 : actions = rule->actions;
239 612 : if (list_length(actions) != 1)
240 0 : elog(ERROR,
241 : "the rule for materialized view \"%s\" is not a single action",
242 : RelationGetRelationName(matviewRel));
243 :
244 : /*
245 : * Check that there is a unique index with no WHERE clause on one or more
246 : * columns of the materialized view if CONCURRENTLY is specified.
247 : */
248 612 : if (concurrent)
249 : {
250 78 : List *indexoidlist = RelationGetIndexList(matviewRel);
251 : ListCell *indexoidscan;
252 78 : bool hasUniqueIndex = false;
253 :
254 : Assert(!is_create);
255 :
256 90 : foreach(indexoidscan, indexoidlist)
257 : {
258 84 : Oid indexoid = lfirst_oid(indexoidscan);
259 : Relation indexRel;
260 :
261 84 : indexRel = index_open(indexoid, AccessShareLock);
262 84 : hasUniqueIndex = is_usable_unique_index(indexRel);
263 84 : index_close(indexRel, AccessShareLock);
264 84 : if (hasUniqueIndex)
265 72 : break;
266 : }
267 :
268 78 : list_free(indexoidlist);
269 :
270 78 : if (!hasUniqueIndex)
271 6 : ereport(ERROR,
272 : (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
273 : errmsg("cannot refresh materialized view \"%s\" concurrently",
274 : quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
275 : RelationGetRelationName(matviewRel))),
276 : errhint("Create a unique index with no WHERE clause on one or more columns of the materialized view.")));
277 : }
278 :
279 : /*
280 : * The stored query was rewritten at the time of the MV definition, but
281 : * has not been scribbled on by the planner.
282 : */
283 606 : dataQuery = linitial_node(Query, actions);
284 :
285 : /*
286 : * Check for active uses of the relation in the current transaction, such
287 : * as open scans.
288 : *
289 : * NB: We count on this to protect us against problems with refreshing the
290 : * data using TABLE_INSERT_FROZEN.
291 : */
292 606 : CheckTableNotInUse(matviewRel,
293 : is_create ? "CREATE MATERIALIZED VIEW" :
294 : "REFRESH MATERIALIZED VIEW");
295 :
296 : /*
297 : * Tentatively mark the matview as populated or not (this will roll back
298 : * if we fail later).
299 : */
300 606 : SetMatViewPopulatedState(matviewRel, !skipData);
301 :
302 : /* Concurrent refresh builds new data in temp tablespace, and does diff. */
303 606 : if (concurrent)
304 : {
305 72 : tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP, false);
306 72 : relpersistence = RELPERSISTENCE_TEMP;
307 : }
308 : else
309 : {
310 534 : tableSpace = matviewRel->rd_rel->reltablespace;
311 534 : relpersistence = matviewRel->rd_rel->relpersistence;
312 : }
313 :
314 : /*
315 : * Create the transient table that will receive the regenerated data. Lock
316 : * it against access by any other process until commit (by which time it
317 : * will be gone).
318 : */
319 1212 : OIDNewHeap = make_new_heap(matviewOid, tableSpace,
320 606 : matviewRel->rd_rel->relam,
321 : relpersistence, ExclusiveLock);
322 : Assert(CheckRelationOidLockedByMe(OIDNewHeap, AccessExclusiveLock, false));
323 :
324 : /* Generate the data, if wanted. */
325 606 : if (!skipData)
326 : {
327 : DestReceiver *dest;
328 :
329 606 : dest = CreateTransientRelDestReceiver(OIDNewHeap);
330 606 : processed = refresh_matview_datafill(dest, dataQuery, queryString,
331 : is_create);
332 : }
333 :
334 : /* Make the matview match the newly generated data. */
335 564 : if (concurrent)
336 : {
337 72 : int old_depth = matview_maintenance_depth;
338 :
339 72 : PG_TRY();
340 : {
341 72 : refresh_by_match_merge(matviewOid, OIDNewHeap, relowner,
342 : save_sec_context);
343 : }
344 12 : PG_CATCH();
345 : {
346 12 : matview_maintenance_depth = old_depth;
347 12 : PG_RE_THROW();
348 : }
349 60 : PG_END_TRY();
350 : Assert(matview_maintenance_depth == old_depth);
351 : }
352 : else
353 : {
354 492 : refresh_by_heap_swap(matviewOid, OIDNewHeap, relpersistence);
355 :
356 : /*
357 : * Inform cumulative stats system about our activity: basically, we
358 : * truncated the matview and inserted some new data. (The concurrent
359 : * code path above doesn't need to worry about this because the
360 : * inserts and deletes it issues get counted by lower-level code.)
361 : */
362 486 : pgstat_count_truncate(matviewRel);
363 486 : if (!skipData)
364 486 : pgstat_count_heap_insert(matviewRel, processed);
365 : }
366 :
367 546 : table_close(matviewRel, NoLock);
368 :
369 : /* Roll back any GUC changes */
370 546 : AtEOXact_GUC(false, save_nestlevel);
371 :
372 : /* Restore userid and security context */
373 546 : SetUserIdAndSecContext(save_userid, save_sec_context);
374 :
375 546 : ObjectAddressSet(address, RelationRelationId, matviewOid);
376 :
377 : /*
378 : * Save the rowcount so that pg_stat_statements can track the total number
379 : * of rows processed by REFRESH MATERIALIZED VIEW command. Note that we
380 : * still don't display the rowcount in the command completion tag output,
381 : * i.e., the display_rowcount flag of CMDTAG_REFRESH_MATERIALIZED_VIEW
382 : * command tag is left false in cmdtaglist.h. Otherwise, the change of
383 : * completion tag output might break applications using it.
384 : *
385 : * When called from CREATE MATERIALIZED VIEW command, the rowcount is
386 : * displayed with the command tag CMDTAG_SELECT.
387 : */
388 546 : if (qc)
389 534 : SetQueryCompletion(qc,
390 : is_create ? CMDTAG_SELECT : CMDTAG_REFRESH_MATERIALIZED_VIEW,
391 : processed);
392 :
393 546 : return address;
394 : }
395 :
396 : /*
397 : * refresh_matview_datafill
398 : *
399 : * Execute the given query, sending result rows to "dest" (which will
400 : * insert them into the target matview).
401 : *
402 : * Returns number of rows inserted.
403 : */
404 : static uint64
405 606 : refresh_matview_datafill(DestReceiver *dest, Query *query,
406 : const char *queryString, bool is_create)
407 : {
408 : List *rewritten;
409 : PlannedStmt *plan;
410 : QueryDesc *queryDesc;
411 : Query *copied_query;
412 : uint64 processed;
413 :
414 : /* Lock and rewrite, using a copy to preserve the original query. */
415 606 : copied_query = copyObject(query);
416 606 : AcquireRewriteLocks(copied_query, true, false);
417 606 : rewritten = QueryRewrite(copied_query);
418 :
419 : /* SELECT should never rewrite to more or less than one SELECT query */
420 606 : if (list_length(rewritten) != 1)
421 0 : elog(ERROR, "unexpected rewrite result for %s",
422 : is_create ? "CREATE MATERIALIZED VIEW " : "REFRESH MATERIALIZED VIEW");
423 606 : query = (Query *) linitial(rewritten);
424 :
425 : /* Check for user-requested abort. */
426 606 : CHECK_FOR_INTERRUPTS();
427 :
428 : /* Plan the query which will generate data for the refresh. */
429 606 : plan = pg_plan_query(query, queryString, CURSOR_OPT_PARALLEL_OK, NULL);
430 :
431 : /*
432 : * Use a snapshot with an updated command ID to ensure this query sees
433 : * results of any previously executed queries. (This could only matter if
434 : * the planner executed an allegedly-stable function that changed the
435 : * database contents, but let's do it anyway to be safe.)
436 : */
437 594 : PushCopiedSnapshot(GetActiveSnapshot());
438 594 : UpdateActiveSnapshotCommandId();
439 :
440 : /* Create a QueryDesc, redirecting output to our tuple receiver */
441 594 : queryDesc = CreateQueryDesc(plan, NULL, queryString,
442 : GetActiveSnapshot(), InvalidSnapshot,
443 : dest, NULL, NULL, 0);
444 :
445 : /* call ExecutorStart to prepare the plan for execution */
446 594 : if (!ExecutorStart(queryDesc, 0))
447 0 : elog(ERROR, "ExecutorStart() failed unexpectedly");
448 :
449 : /* run the plan */
450 594 : ExecutorRun(queryDesc, ForwardScanDirection, 0);
451 :
452 564 : processed = queryDesc->estate->es_processed;
453 :
454 : /* and clean up */
455 564 : ExecutorFinish(queryDesc);
456 564 : ExecutorEnd(queryDesc);
457 :
458 564 : FreeQueryDesc(queryDesc);
459 :
460 564 : PopActiveSnapshot();
461 :
462 564 : return processed;
463 : }
464 :
465 : DestReceiver *
466 606 : CreateTransientRelDestReceiver(Oid transientoid)
467 : {
468 606 : DR_transientrel *self = (DR_transientrel *) palloc0(sizeof(DR_transientrel));
469 :
470 606 : self->pub.receiveSlot = transientrel_receive;
471 606 : self->pub.rStartup = transientrel_startup;
472 606 : self->pub.rShutdown = transientrel_shutdown;
473 606 : self->pub.rDestroy = transientrel_destroy;
474 606 : self->pub.mydest = DestTransientRel;
475 606 : self->transientoid = transientoid;
476 :
477 606 : return (DestReceiver *) self;
478 : }
479 :
480 : /*
481 : * transientrel_startup --- executor startup
482 : */
483 : static void
484 594 : transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
485 : {
486 594 : DR_transientrel *myState = (DR_transientrel *) self;
487 : Relation transientrel;
488 :
489 594 : transientrel = table_open(myState->transientoid, NoLock);
490 :
491 : /*
492 : * Fill private fields of myState for use by later routines
493 : */
494 594 : myState->transientrel = transientrel;
495 594 : myState->output_cid = GetCurrentCommandId(true);
496 594 : myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN;
497 594 : myState->bistate = GetBulkInsertState();
498 :
499 : /*
500 : * Valid smgr_targblock implies something already wrote to the relation.
501 : * This may be harmless, but this function hasn't planned for it.
502 : */
503 : Assert(RelationGetTargetBlock(transientrel) == InvalidBlockNumber);
504 594 : }
505 :
506 : /*
507 : * transientrel_receive --- receive one tuple
508 : */
509 : static bool
510 4054 : transientrel_receive(TupleTableSlot *slot, DestReceiver *self)
511 : {
512 4054 : DR_transientrel *myState = (DR_transientrel *) self;
513 :
514 : /*
515 : * Note that the input slot might not be of the type of the target
516 : * relation. That's supported by table_tuple_insert(), but slightly less
517 : * efficient than inserting with the right slot - but the alternative
518 : * would be to copy into a slot of the right type, which would not be
519 : * cheap either. This also doesn't allow accessing per-AM data (say a
520 : * tuple's xmin), but since we don't do that here...
521 : */
522 :
523 4054 : table_tuple_insert(myState->transientrel,
524 : slot,
525 : myState->output_cid,
526 : myState->ti_options,
527 : myState->bistate);
528 :
529 : /* We know this is a newly created relation, so there are no indexes */
530 :
531 4054 : return true;
532 : }
533 :
534 : /*
535 : * transientrel_shutdown --- executor end
536 : */
537 : static void
538 564 : transientrel_shutdown(DestReceiver *self)
539 : {
540 564 : DR_transientrel *myState = (DR_transientrel *) self;
541 :
542 564 : FreeBulkInsertState(myState->bistate);
543 :
544 564 : table_finish_bulk_insert(myState->transientrel, myState->ti_options);
545 :
546 : /* close transientrel, but keep lock until commit */
547 564 : table_close(myState->transientrel, NoLock);
548 564 : myState->transientrel = NULL;
549 564 : }
550 :
551 : /*
552 : * transientrel_destroy --- release DestReceiver object
553 : */
554 : static void
555 0 : transientrel_destroy(DestReceiver *self)
556 : {
557 0 : pfree(self);
558 0 : }
559 :
560 :
561 : /*
562 : * Given a qualified temporary table name, append an underscore followed by
563 : * the given integer, to make a new table name based on the old one.
564 : * The result is a palloc'd string.
565 : *
566 : * As coded, this would fail to make a valid SQL name if the given name were,
567 : * say, "FOO"."BAR". Currently, the table name portion of the input will
568 : * never be double-quoted because it's of the form "pg_temp_NNN", cf
569 : * make_new_heap(). But we might have to work harder someday.
570 : */
571 : static char *
572 72 : make_temptable_name_n(char *tempname, int n)
573 : {
574 : StringInfoData namebuf;
575 :
576 72 : initStringInfo(&namebuf);
577 72 : appendStringInfoString(&namebuf, tempname);
578 72 : appendStringInfo(&namebuf, "_%d", n);
579 72 : return namebuf.data;
580 : }
581 :
582 : /*
583 : * refresh_by_match_merge
584 : *
585 : * Refresh a materialized view with transactional semantics, while allowing
586 : * concurrent reads.
587 : *
588 : * This is called after a new version of the data has been created in a
589 : * temporary table. It performs a full outer join against the old version of
590 : * the data, producing "diff" results. This join cannot work if there are any
591 : * duplicated rows in either the old or new versions, in the sense that every
592 : * column would compare as equal between the two rows. It does work correctly
593 : * in the face of rows which have at least one NULL value, with all non-NULL
594 : * columns equal. The behavior of NULLs on equality tests and on UNIQUE
595 : * indexes turns out to be quite convenient here; the tests we need to make
596 : * are consistent with default behavior. If there is at least one UNIQUE
597 : * index on the materialized view, we have exactly the guarantee we need.
598 : *
599 : * The temporary table used to hold the diff results contains just the TID of
600 : * the old record (if matched) and the ROW from the new table as a single
601 : * column of complex record type (if matched).
602 : *
603 : * Once we have the diff table, we perform set-based DELETE and INSERT
604 : * operations against the materialized view, and discard both temporary
605 : * tables.
606 : *
607 : * Everything from the generation of the new data to applying the differences
608 : * takes place under cover of an ExclusiveLock, since it seems as though we
609 : * would want to prohibit not only concurrent REFRESH operations, but also
610 : * incremental maintenance. It also doesn't seem reasonable or safe to allow
611 : * SELECT FOR UPDATE or SELECT FOR SHARE on rows being updated or deleted by
612 : * this command.
613 : */
614 : static void
615 72 : refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
616 : int save_sec_context)
617 : {
618 : StringInfoData querybuf;
619 : Relation matviewRel;
620 : Relation tempRel;
621 : char *matviewname;
622 : char *tempname;
623 : char *diffname;
624 : TupleDesc tupdesc;
625 : bool foundUniqueIndex;
626 : List *indexoidlist;
627 : ListCell *indexoidscan;
628 : int16 relnatts;
629 : Oid *opUsedForQual;
630 :
631 72 : initStringInfo(&querybuf);
632 72 : matviewRel = table_open(matviewOid, NoLock);
633 72 : matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
634 72 : RelationGetRelationName(matviewRel));
635 72 : tempRel = table_open(tempOid, NoLock);
636 72 : tempname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(tempRel)),
637 72 : RelationGetRelationName(tempRel));
638 72 : diffname = make_temptable_name_n(tempname, 2);
639 :
640 72 : relnatts = RelationGetNumberOfAttributes(matviewRel);
641 :
642 : /* Open SPI context. */
643 72 : SPI_connect();
644 :
645 : /* Analyze the temp table with the new contents. */
646 72 : appendStringInfo(&querybuf, "ANALYZE %s", tempname);
647 72 : if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
648 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
649 :
650 : /*
651 : * We need to ensure that there are not duplicate rows without NULLs in
652 : * the new data set before we can count on the "diff" results. Check for
653 : * that in a way that allows showing the first duplicated row found. Even
654 : * after we pass this test, a unique index on the materialized view may
655 : * find a duplicate key problem.
656 : *
657 : * Note: here and below, we use "tablename.*::tablerowtype" as a hack to
658 : * keep ".*" from being expanded into multiple columns in a SELECT list.
659 : * Compare ruleutils.c's get_variable().
660 : */
661 72 : resetStringInfo(&querybuf);
662 72 : appendStringInfo(&querybuf,
663 : "SELECT newdata.*::%s FROM %s newdata "
664 : "WHERE newdata.* IS NOT NULL AND EXISTS "
665 : "(SELECT 1 FROM %s newdata2 WHERE newdata2.* IS NOT NULL "
666 : "AND newdata2.* OPERATOR(pg_catalog.*=) newdata.* "
667 : "AND newdata2.ctid OPERATOR(pg_catalog.<>) "
668 : "newdata.ctid)",
669 : tempname, tempname, tempname);
670 72 : if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT)
671 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
672 72 : if (SPI_processed > 0)
673 : {
674 : /*
675 : * Note that this ereport() is returning data to the user. Generally,
676 : * we would want to make sure that the user has been granted access to
677 : * this data. However, REFRESH MAT VIEW is only able to be run by the
678 : * owner of the mat view (or a superuser) and therefore there is no
679 : * need to check for access to data in the mat view.
680 : */
681 6 : ereport(ERROR,
682 : (errcode(ERRCODE_CARDINALITY_VIOLATION),
683 : errmsg("new data for materialized view \"%s\" contains duplicate rows without any null columns",
684 : RelationGetRelationName(matviewRel)),
685 : errdetail("Row: %s",
686 : SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1))));
687 : }
688 :
689 : /*
690 : * Create the temporary "diff" table.
691 : *
692 : * Temporarily switch out of the SECURITY_RESTRICTED_OPERATION context,
693 : * because you cannot create temp tables in SRO context. For extra
694 : * paranoia, add the composite type column only after switching back to
695 : * SRO context.
696 : */
697 66 : SetUserIdAndSecContext(relowner,
698 : save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
699 66 : resetStringInfo(&querybuf);
700 66 : appendStringInfo(&querybuf,
701 : "CREATE TEMP TABLE %s (tid pg_catalog.tid)",
702 : diffname);
703 66 : if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
704 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
705 66 : SetUserIdAndSecContext(relowner,
706 : save_sec_context | SECURITY_RESTRICTED_OPERATION);
707 66 : resetStringInfo(&querybuf);
708 66 : appendStringInfo(&querybuf,
709 : "ALTER TABLE %s ADD COLUMN newdata %s",
710 : diffname, tempname);
711 66 : if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
712 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
713 :
714 : /* Start building the query for populating the diff table. */
715 66 : resetStringInfo(&querybuf);
716 66 : appendStringInfo(&querybuf,
717 : "INSERT INTO %s "
718 : "SELECT mv.ctid AS tid, newdata.*::%s AS newdata "
719 : "FROM %s mv FULL JOIN %s newdata ON (",
720 : diffname, tempname, matviewname, tempname);
721 :
722 : /*
723 : * Get the list of index OIDs for the table from the relcache, and look up
724 : * each one in the pg_index syscache. We will test for equality on all
725 : * columns present in all unique indexes which only reference columns and
726 : * include all rows.
727 : */
728 66 : tupdesc = matviewRel->rd_att;
729 66 : opUsedForQual = (Oid *) palloc0(sizeof(Oid) * relnatts);
730 66 : foundUniqueIndex = false;
731 :
732 66 : indexoidlist = RelationGetIndexList(matviewRel);
733 :
734 138 : foreach(indexoidscan, indexoidlist)
735 : {
736 72 : Oid indexoid = lfirst_oid(indexoidscan);
737 : Relation indexRel;
738 :
739 72 : indexRel = index_open(indexoid, RowExclusiveLock);
740 72 : if (is_usable_unique_index(indexRel))
741 : {
742 72 : Form_pg_index indexStruct = indexRel->rd_index;
743 72 : int indnkeyatts = indexStruct->indnkeyatts;
744 : oidvector *indclass;
745 : Datum indclassDatum;
746 : int i;
747 :
748 : /* Must get indclass the hard way. */
749 72 : indclassDatum = SysCacheGetAttrNotNull(INDEXRELID,
750 72 : indexRel->rd_indextuple,
751 : Anum_pg_index_indclass);
752 72 : indclass = (oidvector *) DatumGetPointer(indclassDatum);
753 :
754 : /* Add quals for all columns from this index. */
755 160 : for (i = 0; i < indnkeyatts; i++)
756 : {
757 88 : int attnum = indexStruct->indkey.values[i];
758 88 : Oid opclass = indclass->values[i];
759 88 : Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
760 88 : Oid attrtype = attr->atttypid;
761 : HeapTuple cla_ht;
762 : Form_pg_opclass cla_tup;
763 : Oid opfamily;
764 : Oid opcintype;
765 : Oid op;
766 : const char *leftop;
767 : const char *rightop;
768 :
769 : /*
770 : * Identify the equality operator associated with this index
771 : * column. First we need to look up the column's opclass.
772 : */
773 88 : cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass));
774 88 : if (!HeapTupleIsValid(cla_ht))
775 0 : elog(ERROR, "cache lookup failed for opclass %u", opclass);
776 88 : cla_tup = (Form_pg_opclass) GETSTRUCT(cla_ht);
777 : Assert(cla_tup->opcmethod == BTREE_AM_OID);
778 88 : opfamily = cla_tup->opcfamily;
779 88 : opcintype = cla_tup->opcintype;
780 88 : ReleaseSysCache(cla_ht);
781 :
782 88 : op = get_opfamily_member(opfamily, opcintype, opcintype,
783 : BTEqualStrategyNumber);
784 88 : if (!OidIsValid(op))
785 0 : elog(ERROR, "missing operator %d(%u,%u) in opfamily %u",
786 : BTEqualStrategyNumber, opcintype, opcintype, opfamily);
787 :
788 : /*
789 : * If we find the same column with the same equality semantics
790 : * in more than one index, we only need to emit the equality
791 : * clause once.
792 : *
793 : * Since we only remember the last equality operator, this
794 : * code could be fooled into emitting duplicate clauses given
795 : * multiple indexes with several different opclasses ... but
796 : * that's so unlikely it doesn't seem worth spending extra
797 : * code to avoid.
798 : */
799 88 : if (opUsedForQual[attnum - 1] == op)
800 0 : continue;
801 88 : opUsedForQual[attnum - 1] = op;
802 :
803 : /*
804 : * Actually add the qual, ANDed with any others.
805 : */
806 88 : if (foundUniqueIndex)
807 28 : appendStringInfoString(&querybuf, " AND ");
808 :
809 88 : leftop = quote_qualified_identifier("newdata",
810 88 : NameStr(attr->attname));
811 88 : rightop = quote_qualified_identifier("mv",
812 88 : NameStr(attr->attname));
813 :
814 88 : generate_operator_clause(&querybuf,
815 : leftop, attrtype,
816 : op,
817 : rightop, attrtype);
818 :
819 88 : foundUniqueIndex = true;
820 : }
821 : }
822 :
823 : /* Keep the locks, since we're about to run DML which needs them. */
824 72 : index_close(indexRel, NoLock);
825 : }
826 :
827 66 : list_free(indexoidlist);
828 :
829 : /*
830 : * There must be at least one usable unique index on the matview.
831 : *
832 : * ExecRefreshMatView() checks that after taking the exclusive lock on the
833 : * matview. So at least one unique index is guaranteed to exist here
834 : * because the lock is still being held. (One known exception is if a
835 : * function called as part of refreshing the matview drops the index.
836 : * That's a pretty silly thing to do.)
837 : */
838 66 : if (!foundUniqueIndex)
839 6 : ereport(ERROR,
840 : errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
841 : errmsg("could not find suitable unique index on materialized view"));
842 :
843 60 : appendStringInfoString(&querybuf,
844 : " AND newdata.* OPERATOR(pg_catalog.*=) mv.*) "
845 : "WHERE newdata.* IS NULL OR mv.* IS NULL "
846 : "ORDER BY tid");
847 :
848 : /* Populate the temporary "diff" table. */
849 60 : if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
850 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
851 :
852 : /*
853 : * We have no further use for data from the "full-data" temp table, but we
854 : * must keep it around because its type is referenced from the diff table.
855 : */
856 :
857 : /* Analyze the diff table. */
858 60 : resetStringInfo(&querybuf);
859 60 : appendStringInfo(&querybuf, "ANALYZE %s", diffname);
860 60 : if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
861 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
862 :
863 60 : OpenMatViewIncrementalMaintenance();
864 :
865 : /* Deletes must come before inserts; do them first. */
866 60 : resetStringInfo(&querybuf);
867 60 : appendStringInfo(&querybuf,
868 : "DELETE FROM %s mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
869 : "(SELECT diff.tid FROM %s diff "
870 : "WHERE diff.tid IS NOT NULL "
871 : "AND diff.newdata IS NULL)",
872 : matviewname, diffname);
873 60 : if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
874 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
875 :
876 : /* Inserts go last. */
877 60 : resetStringInfo(&querybuf);
878 60 : appendStringInfo(&querybuf,
879 : "INSERT INTO %s SELECT (diff.newdata).* "
880 : "FROM %s diff WHERE tid IS NULL",
881 : matviewname, diffname);
882 60 : if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
883 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
884 :
885 : /* We're done maintaining the materialized view. */
886 60 : CloseMatViewIncrementalMaintenance();
887 60 : table_close(tempRel, NoLock);
888 60 : table_close(matviewRel, NoLock);
889 :
890 : /* Clean up temp tables. */
891 60 : resetStringInfo(&querybuf);
892 60 : appendStringInfo(&querybuf, "DROP TABLE %s, %s", diffname, tempname);
893 60 : if (SPI_exec(querybuf.data, 0) != SPI_OK_UTILITY)
894 0 : elog(ERROR, "SPI_exec failed: %s", querybuf.data);
895 :
896 : /* Close SPI context. */
897 60 : if (SPI_finish() != SPI_OK_FINISH)
898 0 : elog(ERROR, "SPI_finish failed");
899 60 : }
900 :
901 : /*
902 : * Swap the physical files of the target and transient tables, then rebuild
903 : * the target's indexes and throw away the transient table. Security context
904 : * swapping is handled by the called function, so it is not needed here.
905 : */
906 : static void
907 492 : refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersistence)
908 : {
909 492 : finish_heap_swap(matviewOid, OIDNewHeap, false, false, true, true,
910 : RecentXmin, ReadNextMultiXactId(), relpersistence);
911 486 : }
912 :
913 : /*
914 : * Check whether specified index is usable for match merge.
915 : */
916 : static bool
917 156 : is_usable_unique_index(Relation indexRel)
918 : {
919 156 : Form_pg_index indexStruct = indexRel->rd_index;
920 :
921 : /*
922 : * Must be unique, valid, immediate, non-partial, and be defined over
923 : * plain user columns (not expressions). We also require it to be a
924 : * btree. Even if we had any other unique index kinds, we'd not know how
925 : * to identify the corresponding equality operator, nor could we be sure
926 : * that the planner could implement the required FULL JOIN with non-btree
927 : * operators.
928 : */
929 156 : if (indexStruct->indisunique &&
930 156 : indexStruct->indimmediate &&
931 156 : indexRel->rd_rel->relam == BTREE_AM_OID &&
932 312 : indexStruct->indisvalid &&
933 156 : RelationGetIndexPredicate(indexRel) == NIL &&
934 150 : indexStruct->indnatts > 0)
935 : {
936 : /*
937 : * The point of groveling through the index columns individually is to
938 : * reject both index expressions and system columns. Currently,
939 : * matviews couldn't have OID columns so there's no way to create an
940 : * index on a system column; but maybe someday that wouldn't be true,
941 : * so let's be safe.
942 : */
943 150 : int numatts = indexStruct->indnatts;
944 : int i;
945 :
946 326 : for (i = 0; i < numatts; i++)
947 : {
948 182 : int attnum = indexStruct->indkey.values[i];
949 :
950 182 : if (attnum <= 0)
951 6 : return false;
952 : }
953 144 : return true;
954 : }
955 6 : return false;
956 : }
957 :
958 :
959 : /*
960 : * This should be used to test whether the backend is in a context where it is
961 : * OK to allow DML statements to modify materialized views. We only want to
962 : * allow that for internal code driven by the materialized view definition,
963 : * not for arbitrary user-supplied code.
964 : *
965 : * While the function names reflect the fact that their main intended use is
966 : * incremental maintenance of materialized views (in response to changes to
967 : * the data in referenced relations), they are initially used to allow REFRESH
968 : * without blocking concurrent reads.
969 : */
970 : bool
971 120 : MatViewIncrementalMaintenanceIsEnabled(void)
972 : {
973 120 : return matview_maintenance_depth > 0;
974 : }
975 :
976 : static void
977 60 : OpenMatViewIncrementalMaintenance(void)
978 : {
979 60 : matview_maintenance_depth++;
980 60 : }
981 :
982 : static void
983 60 : CloseMatViewIncrementalMaintenance(void)
984 : {
985 60 : matview_maintenance_depth--;
986 : Assert(matview_maintenance_depth >= 0);
987 60 : }
|
