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

Commit187682c

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 matview query and those internal aliasesconflict.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 parentcb3cffe commit187682c

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
@@ -629,12 +629,12 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
629629
*/
630630
resetStringInfo(&querybuf);
631631
appendStringInfo(&querybuf,
632-
"SELECT newdata FROM %s newdata "
633-
"WHERE newdata IS NOT NULL AND EXISTS "
634-
"(SELECT 1 FROM %s newdata2 WHERE newdata2 IS NOT NULL "
635-
"AND newdata2 OPERATOR(pg_catalog.*=) newdata "
636-
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
637-
"newdata.ctid)",
632+
"SELECT_$newdata FROM %s_$newdata "
633+
"WHERE_$newdata IS NOT NULL AND EXISTS "
634+
"(SELECT 1 FROM %s_$newdata2 WHERE_$newdata2 IS NOT NULL "
635+
"AND_$newdata2 OPERATOR(pg_catalog.*=)_$newdata "
636+
"AND_$newdata2.ctid OPERATOR(pg_catalog.<>) "
637+
"_$newdata.ctid)",
638638
tempname,tempname);
639639
if (SPI_execute(querybuf.data, false,1)!=SPI_OK_SELECT)
640640
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
@@ -662,8 +662,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
662662
resetStringInfo(&querybuf);
663663
appendStringInfo(&querybuf,
664664
"CREATE TEMP TABLE %s AS "
665-
"SELECT mv.ctid AS tid, newdata "
666-
"FROM %s mv FULL JOIN %s newdata ON (",
665+
"SELECT_$mv.ctid AS tid,_$newdata "
666+
"FROM %s_$mv FULL JOIN %s_$newdata ON (",
667667
diffname,matviewname,tempname);
668668

669669
/*
@@ -756,9 +756,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
756756
if (foundUniqueIndex)
757757
appendStringInfoString(&querybuf," AND ");
758758

759-
leftop=quote_qualified_identifier("newdata",
759+
leftop=quote_qualified_identifier("_$newdata",
760760
NameStr(attr->attname));
761-
rightop=quote_qualified_identifier("mv",
761+
rightop=quote_qualified_identifier("_$mv",
762762
NameStr(attr->attname));
763763

764764
generate_operator_clause(&querybuf,
@@ -786,8 +786,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
786786
Assert(foundUniqueIndex);
787787

788788
appendStringInfoString(&querybuf,
789-
" AND newdata OPERATOR(pg_catalog.*=) mv) "
790-
"WHERE newdata IS NULL OR mv IS NULL "
789+
" AND_$newdata OPERATOR(pg_catalog.*=)_$mv) "
790+
"WHERE_$newdata IS NULL OR_$mv IS NULL "
791791
"ORDER BY tid");
792792

793793
/* Create the temporary "diff" table. */
@@ -813,19 +813,19 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
813813
/* Deletes must come before inserts; do them first. */
814814
resetStringInfo(&querybuf);
815815
appendStringInfo(&querybuf,
816-
"DELETE FROM %s mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
817-
"(SELECT diff.tid FROM %s diff "
818-
"WHERE diff.tid IS NOT NULL "
819-
"AND diff.newdata IS NULL)",
816+
"DELETE FROM %s_$mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
817+
"(SELECT_$diff.tid FROM %s_$diff "
818+
"WHERE_$diff.tid IS NOT NULL "
819+
"AND_$diff._$newdata IS NULL)",
820820
matviewname,diffname);
821821
if (SPI_exec(querybuf.data,0)!=SPI_OK_DELETE)
822822
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
823823

824824
/* Inserts go last. */
825825
resetStringInfo(&querybuf);
826826
appendStringInfo(&querybuf,
827-
"INSERT INTO %s SELECT (diff.newdata).* "
828-
"FROM %s diff WHERE tid IS NULL",
827+
"INSERT INTO %s SELECT (_$diff._$newdata).* "
828+
"FROM %s_$diff WHERE tid IS NULL",
829829
matviewname,diffname);
830830
if (SPI_exec(querybuf.data,0)!=SPI_OK_INSERT)
831831
elog(ERROR,"SPI_exec failed: %s",querybuf.data);

0 commit comments

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