Reduce risks of conflicts in internal queries of REFRESH MATVIEW CONCURRENTLY

michaelpq · michaelpq · commit d9525c46c89d · 2021-06-03T15:29:01.000+09:00
The internal SQL queries used by REFRESH MATERIALIZED VIEW CONCURRENTLY include some aliases for its diff and temporary relations with rather-generic names: diff, newdata, newdata2 and mv. Depending on the queries used for the materialized view, using CONCURRENTLY could lead to some internal failures if the query and those internal aliases conflict. Those names have been chosen in 841c29c. This commit switches instead to a naming pattern which is less likely going to cause conflicts, based on an idea from Thomas Munro, by appending _$ to those aliases. This is not perfect as those new names could still conflict, but at least it has the advantage to keep the code readable and simple while reducing the likelihood of conflicts to be close to zero. Reported-by: Mathis Rudolf Author: Bharath Rupireddy Reviewed-by: Bernd Helmle, Thomas Munro, Michael Paquier Discussion: https://postgr.es/m/109c267a-10d2-3c53-b60e-720fcf44d9e8@credativ.de Backpatch-through: 9.6
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
@@ -601,12 +601,12 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 	 */
 	resetStringInfo(&querybuf);
 	appendStringInfo(&querybuf,
-					 "SELECT newdata FROM %s newdata "
-					 "WHERE newdata IS NOT NULL AND EXISTS "
-					 "(SELECT 1 FROM %s newdata2 WHERE newdata2 IS NOT NULL "
-					 "AND newdata2 OPERATOR(pg_catalog.*=) newdata "
-					 "AND newdata2.ctid OPERATOR(pg_catalog.<>) "
-					 "newdata.ctid)",
+					 "SELECT _$newdata FROM %s _$newdata "
+					 "WHERE _$newdata IS NOT NULL AND EXISTS "
+					 "(SELECT 1 FROM %s _$newdata2 WHERE _$newdata2 IS NOT NULL "
+					 "AND _$newdata2 OPERATOR(pg_catalog.*=) _$newdata "
+					 "AND _$newdata2.ctid OPERATOR(pg_catalog.<>) "
+					 "_$newdata.ctid)",
 					 tempname, tempname);
 	if (SPI_execute(querybuf.data, false, 1) != SPI_OK_SELECT)
 		elog(ERROR, "SPI_exec failed: %s", querybuf.data);
@@ -634,8 +634,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 	resetStringInfo(&querybuf);
 	appendStringInfo(&querybuf,
 					 "CREATE TEMP TABLE %s AS "
-					 "SELECT mv.ctid AS tid, newdata "
-					 "FROM %s mv FULL JOIN %s newdata ON (",
+					 "SELECT _$mv.ctid AS tid, _$newdata "
+					 "FROM %s _$mv FULL JOIN %s _$newdata ON (",
 					 diffname, matviewname, tempname);
 
 	/*
@@ -728,9 +728,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 				if (foundUniqueIndex)
 					appendStringInfoString(&querybuf, " AND ");
 
-				leftop = quote_qualified_identifier("newdata",
+				leftop = quote_qualified_identifier("_$newdata",
 													NameStr(attr->attname));
-				rightop = quote_qualified_identifier("mv",
+				rightop = quote_qualified_identifier("_$mv",
 													 NameStr(attr->attname));
 
 				generate_operator_clause(&querybuf,
@@ -758,8 +758,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 	Assert(foundUniqueIndex);
 
 	appendStringInfoString(&querybuf,
-						   " AND newdata OPERATOR(pg_catalog.*=) mv) "
-						   "WHERE newdata IS NULL OR mv IS NULL "
+						   " AND _$newdata OPERATOR(pg_catalog.*=) _$mv) "
+						   "WHERE _$newdata IS NULL OR _$mv IS NULL "
 						   "ORDER BY tid");
 
 	/* Create the temporary "diff" table. */
@@ -785,19 +785,19 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
 	/* Deletes must come before inserts; do them first. */
 	resetStringInfo(&querybuf);
 	appendStringInfo(&querybuf,
-				   "DELETE FROM %s mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
-					 "(SELECT diff.tid FROM %s diff "
-					 "WHERE diff.tid IS NOT NULL "
-					 "AND diff.newdata IS NULL)",
+				   "DELETE FROM %s _$mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
+					 "(SELECT _$diff.tid FROM %s _$diff "
+					 "WHERE _$diff.tid IS NOT NULL "
+					 "AND _$diff._$newdata IS NULL)",
 					 matviewname, diffname);
 	if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
 		elog(ERROR, "SPI_exec failed: %s", querybuf.data);
 
 	/* Inserts go last. */
 	resetStringInfo(&querybuf);
 	appendStringInfo(&querybuf,
-					 "INSERT INTO %s SELECT (diff.newdata).* "
-					 "FROM %s diff WHERE tid IS NULL",
+					 "INSERT INTO %s SELECT (_$diff._$newdata).* "
+					 "FROM %s _$diff WHERE tid IS NULL",
 					 matviewname, diffname);
 	if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
 		elog(ERROR, "SPI_exec failed: %s", querybuf.data);
