On Wed, Jun 2, 2021 at 1:27 PM Michael Paquier <mich...@paquier.xyz> wrote:
>
> On Wed, Jun 02, 2021 at 10:53:22AM +0530, Bharath Rupireddy wrote:
> > Thanks. PSA v4.
>
> Thanks for the new version.
>
> + MyProcPid, tempname, MyProcPid, MyProcPid,
> + tempname, MyProcPid, MyProcPid, MyProcPid,
> + MyProcPid, MyProcPid, MyProcPid);
> This style is still a bit heavy-ish. Perhaps we should just come back
> to Thomas's suggestion and just use a prefix with _$ for all that.
Thanks.The changes with that approach are very minimal. PSA v5 and let
me know if any more changes are needed.
With Regards,
Bharath Rupireddy.
From 20e0972a6bc4ee0d48174cd83f01391919ab0152 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Wed, 2 Jun 2021 02:31:40 -0700
Subject: [PATCH v5] Avoid alias name collisions in REFRESH MATERIALIZED VIEW
There are alias names such as "mv", "newdata", "newdata2", "diff"
used in REFRESH MATERIALIZED VIEW .. CONCURRENTLY code. These names
are so common that users can have them in the queries and
refreshing materialized view can fail.
This patch randomizes those alias names with prepending "_$" before
them. So the names will be "_$mv", "_$newdata", "_$newdata2",
"_$diff".
---
src/backend/commands/matview.c | 36 +++++++++++++++++-----------------
1 file changed, 18 insertions(+), 18 deletions(-)
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 172ec6e982..25bbd8a5c1 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -629,12 +629,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);
@@ -662,8 +662,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);
/*
@@ -756,9 +756,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,
@@ -786,8 +786,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. */
@@ -813,10 +813,10 @@ 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);
@@ -824,8 +824,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
/* 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);
--
2.25.1
