When creating materialized views, use REFRESH to load data.

Previously, CREATE MATERIALIZED VIEW ... WITH DATA populated the MV
the same way as CREATE TABLE ... AS.

Instead, reuse the REFRESH logic, which locks down security-restricted
operations and restricts the search_path. This reduces the chance that
a subsequent refresh will fail.

Reported-by: Noah Misch
Backpatch-through: 17
Discussion: https://postgr.es/m/20240630222344.db.nmisch@google.com

1 files changed, 40 insertions, 24 deletions

diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index ea05d4b224f..84245b65f7d 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -112,15 +112,44 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
 /*
  * ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command
  *
+ * If WITH NO DATA was specified, this is effectively like a TRUNCATE;
+ * otherwise it is like a TRUNCATE followed by an INSERT using the SELECT
+ * statement associated with the materialized view.  The statement node's
+ * skipData field shows whether the clause was used.
+ */
+ObjectAddress
+ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
+				   ParamListInfo params, QueryCompletion *qc)
+{
+	Oid			matviewOid;
+	LOCKMODE	lockmode;
+
+	/* Determine strength of lock needed. */
+	lockmode = stmt->concurrent ? ExclusiveLock : AccessExclusiveLock;
+
+	/*
+	 * Get a lock until end of transaction.
+	 */
+	matviewOid = RangeVarGetRelidExtended(stmt->relation,
+										  lockmode, 0,
+										  RangeVarCallbackMaintainsTable,
+										  NULL);
+
+	return RefreshMatViewByOid(matviewOid, stmt->skipData, stmt->concurrent,
+							   queryString, params, qc);
+}
+
+/*
+ * RefreshMatViewByOid -- refresh materialized view by OID
+ *
  * This refreshes the materialized view by creating a new table and swapping
  * the relfilenumbers of the new table and the old materialized view, so the OID
  * of the original materialized view is preserved. Thus we do not lose GRANT
  * nor references to this materialized view.
  *
- * If WITH NO DATA was specified, this is effectively like a TRUNCATE;
- * otherwise it is like a TRUNCATE followed by an INSERT using the SELECT
- * statement associated with the materialized view.  The statement node's
- * skipData field shows whether the clause was used.
+ * If skipData is true, this is effectively like a TRUNCATE; otherwise it is
+ * like a TRUNCATE followed by an INSERT using the SELECT statement associated
+ * with the materialized view.
  *
  * Indexes are rebuilt too, via REINDEX. Since we are effectively bulk-loading
  * the new heap, it's better to create the indexes afterwards than to fill them
@@ -130,10 +159,10 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
  * reflect the result set of the materialized view's query.
  */
 ObjectAddress
-ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
-				   ParamListInfo params, QueryCompletion *qc)
+RefreshMatViewByOid(Oid matviewOid, bool skipData, bool concurrent,
+					const char *queryString, ParamListInfo params,
+					QueryCompletion *qc)
 {
-	Oid			matviewOid;
 	Relation	matviewRel;
 	RewriteRule *rule;
 	List	   *actions;
@@ -143,25 +172,12 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 	Oid			OIDNewHeap;
 	DestReceiver *dest;
 	uint64		processed = 0;
-	bool		concurrent;
-	LOCKMODE	lockmode;
 	char		relpersistence;
 	Oid			save_userid;
 	int			save_sec_context;
 	int			save_nestlevel;
 	ObjectAddress address;
 
-	/* Determine strength of lock needed. */
-	concurrent = stmt->concurrent;
-	lockmode = concurrent ? ExclusiveLock : AccessExclusiveLock;
-
-	/*
-	 * Get a lock until end of transaction.
-	 */
-	matviewOid = RangeVarGetRelidExtended(stmt->relation,
-										  lockmode, 0,
-										  RangeVarCallbackMaintainsTable,
-										  NULL);
 	matviewRel = table_open(matviewOid, NoLock);
 	relowner = matviewRel->rd_rel->relowner;
 
@@ -190,7 +206,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 				 errmsg("CONCURRENTLY cannot be used when the materialized view is not populated")));
 
 	/* Check that conflicting options have not been specified. */
-	if (concurrent && stmt->skipData)
+	if (concurrent && skipData)
 		ereport(ERROR,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("%s and %s options cannot be used together",
@@ -275,7 +291,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 	 * Tentatively mark the matview as populated or not (this will roll back
 	 * if we fail later).
 	 */
-	SetMatViewPopulatedState(matviewRel, !stmt->skipData);
+	SetMatViewPopulatedState(matviewRel, !skipData);
 
 	/* Concurrent refresh builds new data in temp tablespace, and does diff. */
 	if (concurrent)
@@ -301,7 +317,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 	dest = CreateTransientRelDestReceiver(OIDNewHeap);
 
 	/* Generate the data, if wanted. */
-	if (!stmt->skipData)
+	if (!skipData)
 		processed = refresh_matview_datafill(dest, dataQuery, queryString);
 
 	/* Make the matview match the newly generated data. */
@@ -333,7 +349,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
 		 * inserts and deletes it issues get counted by lower-level code.)
 		 */
 		pgstat_count_truncate(matviewRel);
-		if (!stmt->skipData)
+		if (!skipData)
 			pgstat_count_heap_insert(matviewRel, processed);
 	}