Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit75d66d1

Browse files
committed
Reduce risks of conflicts in internal queries of REFRESH MATVIEW CONCURRENTLY
The internal SQL queries used by REFRESH MATERIALIZED VIEW CONCURRENTLYinclude some aliases for its diff and temporary relations withrather-generic names: diff, newdata, newdata2 and mv. Depending on thequeries used for the materialized view, using CONCURRENTLY could lead tosome internal failures if the query and those internal aliases conflict.Those names have been chosen in841c29c. This commit switches insteadto a naming pattern which is less likely going to cause conflicts, basedon an idea from Thomas Munro, by appending _$ to those aliases. This isnot perfect as those new names could still conflict, but at least it hasthe advantage to keep the code readable and simple while reducing thelikelihood of conflicts to be close to zero.Reported-by: Mathis RudolfAuthor: Bharath RupireddyReviewed-by: Bernd Helmle, Thomas Munro, Michael PaquierDiscussion:https://postgr.es/m/109c267a-10d2-3c53-b60e-720fcf44d9e8@credativ.deBackpatch-through: 9.6
1 parent9d66067 commit75d66d1

File tree

1 file changed

+18
-18
lines changed

1 file changed

+18
-18
lines changed

‎src/backend/commands/matview.c‎

Lines changed: 18 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -618,12 +618,12 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
618618
*/
619619
resetStringInfo(&querybuf);
620620
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)",
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)",
627627
tempname,tempname);
628628
if (SPI_execute(querybuf.data, false,1)!=SPI_OK_SELECT)
629629
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
@@ -651,8 +651,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
651651
resetStringInfo(&querybuf);
652652
appendStringInfo(&querybuf,
653653
"CREATE TEMP TABLE %s AS "
654-
"SELECT mv.ctid AS tid, newdata "
655-
"FROM %s mv FULL JOIN %s newdata ON (",
654+
"SELECT_$mv.ctid AS tid,_$newdata "
655+
"FROM %s_$mv FULL JOIN %s_$newdata ON (",
656656
diffname,matviewname,tempname);
657657

658658
/*
@@ -745,9 +745,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
745745
if (foundUniqueIndex)
746746
appendStringInfoString(&querybuf," AND ");
747747

748-
leftop=quote_qualified_identifier("newdata",
748+
leftop=quote_qualified_identifier("_$newdata",
749749
NameStr(attr->attname));
750-
rightop=quote_qualified_identifier("mv",
750+
rightop=quote_qualified_identifier("_$mv",
751751
NameStr(attr->attname));
752752

753753
generate_operator_clause(&querybuf,
@@ -775,8 +775,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
775775
Assert(foundUniqueIndex);
776776

777777
appendStringInfoString(&querybuf,
778-
" AND newdata OPERATOR(pg_catalog.*=) mv) "
779-
"WHERE newdata IS NULL OR mv IS NULL "
778+
" AND_$newdata OPERATOR(pg_catalog.*=)_$mv) "
779+
"WHERE_$newdata IS NULL OR_$mv IS NULL "
780780
"ORDER BY tid");
781781

782782
/* Create the temporary "diff" table. */
@@ -802,19 +802,19 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
802802
/* Deletes must come before inserts; do them first. */
803803
resetStringInfo(&querybuf);
804804
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)",
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)",
809809
matviewname,diffname);
810810
if (SPI_exec(querybuf.data,0)!=SPI_OK_DELETE)
811811
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
812812

813813
/* Inserts go last. */
814814
resetStringInfo(&querybuf);
815815
appendStringInfo(&querybuf,
816-
"INSERT INTO %s SELECT (diff.newdata).* "
817-
"FROM %s diff WHERE tid IS NULL",
816+
"INSERT INTO %s SELECT (_$diff._$newdata).* "
817+
"FROM %s_$diff WHERE tid IS NULL",
818818
matviewname,diffname);
819819
if (SPI_exec(querybuf.data,0)!=SPI_OK_INSERT)
820820
elog(ERROR,"SPI_exec failed: %s",querybuf.data);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp