LCOV - code coverage report
Current view: top level - src/backend/commands - matview.c (source / functions) Hit Total Coverage
Test: PostgreSQL 13beta1 Lines: 233 256 91.0 %
Date: 2020-06-01 09:07:10 Functions: 14 15 93.3 %
Legend: Lines: hit not hit

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

Generated by: LCOV version 1.13