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

Commit2c91590

Browse files
committed
Really fix the ambiguity in REFRESH MATERIALIZED VIEW CONCURRENTLY.
Rather than trying to pick table aliases that won't conflict withany possible user-defined matview column name, adjust the queries'syntax so that the aliases are only used in places where they can't bemistaken for column names. Mostly this consists of writing "alias.*"not just "alias", which adds clarity for humans as well as machines.We do have the issue that "SELECT alias.*" acts differently from"SELECT alias", but we can use the same hack ruleutils.c uses forwhole-row variables in SELECT lists: write "alias.*::compositetype".We might as well revert to the original aliases after doing this;they're a bit easier to read.Like75d66d1, back-patch to all supported branches.Discussion:https://postgr.es/m/2488325.1628261320@sss.pgh.pa.us
1 parent9b0d717 commit2c91590

File tree

3 files changed

+47
-24
lines changed

3 files changed

+47
-24
lines changed

‎src/backend/commands/matview.c

Lines changed: 29 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -536,9 +536,12 @@ transientrel_destroy(DestReceiver *self)
536536
/*
537537
* Given a qualified temporary table name, append an underscore followed by
538538
* the given integer, to make a new table name based on the old one.
539+
* The result is a palloc'd string.
539540
*
540-
* This leaks memory through palloc(), which won't be cleaned up until the
541-
* current memory context is freed.
541+
* As coded, this would fail to make a valid SQL name if the given name were,
542+
* say, "FOO"."BAR". Currently, the table name portion of the input will
543+
* never be double-quoted because it's of the form "pg_temp_NNN", cf
544+
* make_new_heap(). But we might have to work harder someday.
542545
*/
543546
staticchar*
544547
make_temptable_name_n(char*tempname,intn)
@@ -626,16 +629,20 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
626629
* that in a way that allows showing the first duplicated row found. Even
627630
* after we pass this test, a unique index on the materialized view may
628631
* find a duplicate key problem.
632+
*
633+
* Note: here and below, we use "tablename.*::tablerowtype" as a hack to
634+
* keep ".*" from being expanded into multiple columns in a SELECT list.
635+
* Compare ruleutils.c's get_variable().
629636
*/
630637
resetStringInfo(&querybuf);
631638
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)",
638-
tempname,tempname);
639+
"SELECT newdata.*::%s FROM %s newdata "
640+
"WHERE newdata.* IS NOT NULL AND EXISTS "
641+
"(SELECT 1 FROM %s newdata2 WHERE newdata2.* IS NOT NULL "
642+
"AND newdata2.* OPERATOR(pg_catalog.*=) newdata.* "
643+
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
644+
"newdata.ctid)",
645+
tempname,tempname,tempname);
639646
if (SPI_execute(querybuf.data, false,1)!=SPI_OK_SELECT)
640647
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
641648
if (SPI_processed>0)
@@ -662,9 +669,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
662669
resetStringInfo(&querybuf);
663670
appendStringInfo(&querybuf,
664671
"CREATE TEMP TABLE %s AS "
665-
"SELECT_$mv.ctid AS tid,_$newdata "
666-
"FROM %s_$mv FULL JOIN %s_$newdata ON (",
667-
diffname,matviewname,tempname);
672+
"SELECT mv.ctid AS tid,newdata.*::%s ASnewdata "
673+
"FROM %s mv FULL JOIN %s newdata ON (",
674+
diffname,tempname,matviewname,tempname);
668675

669676
/*
670677
* Get the list of index OIDs for the table from the relcache, and look up
@@ -756,9 +763,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
756763
if (foundUniqueIndex)
757764
appendStringInfoString(&querybuf," AND ");
758765

759-
leftop=quote_qualified_identifier("_$newdata",
766+
leftop=quote_qualified_identifier("newdata",
760767
NameStr(attr->attname));
761-
rightop=quote_qualified_identifier("_$mv",
768+
rightop=quote_qualified_identifier("mv",
762769
NameStr(attr->attname));
763770

764771
generate_operator_clause(&querybuf,
@@ -786,8 +793,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
786793
Assert(foundUniqueIndex);
787794

788795
appendStringInfoString(&querybuf,
789-
" AND_$newdata OPERATOR(pg_catalog.*=)_$mv) "
790-
"WHERE_$newdata IS NULL OR_$mv IS NULL "
796+
" AND newdata.* OPERATOR(pg_catalog.*=)mv.*) "
797+
"WHERE newdata.* IS NULL ORmv.* IS NULL "
791798
"ORDER BY tid");
792799

793800
/* Create the temporary "diff" table. */
@@ -813,19 +820,19 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
813820
/* Deletes must come before inserts; do them first. */
814821
resetStringInfo(&querybuf);
815822
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)",
823+
"DELETE FROM %s mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
824+
"(SELECT diff.tid FROM %s diff "
825+
"WHERE diff.tid IS NOT NULL "
826+
"AND diff.newdata IS NULL)",
820827
matviewname,diffname);
821828
if (SPI_exec(querybuf.data,0)!=SPI_OK_DELETE)
822829
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
823830

824831
/* Inserts go last. */
825832
resetStringInfo(&querybuf);
826833
appendStringInfo(&querybuf,
827-
"INSERT INTO %s SELECT (_$diff._$newdata).* "
828-
"FROM %s_$diff WHERE tid IS NULL",
834+
"INSERT INTO %s SELECT (diff.newdata).* "
835+
"FROM %s diff WHERE tid IS NULL",
829836
matviewname,diffname);
830837
if (SPI_exec(querybuf.data,0)!=SPI_OK_INSERT)
831838
elog(ERROR,"SPI_exec failed: %s",querybuf.data);

‎src/test/regress/expected/matview.out

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -551,7 +551,15 @@ NOTICE: drop cascades to materialized view mvtest_mv_v
551551
-- make sure running as superuser works when MV owned by another role (bug #11208)
552552
CREATE ROLE regress_user_mvtest;
553553
SET ROLE regress_user_mvtest;
554-
CREATE TABLE mvtest_foo_data AS SELECT i, md5(random()::text)
554+
-- this test case also checks for ambiguity in the queries issued by
555+
-- refresh_by_match_merge(), by choosing column names that intentionally
556+
-- duplicate all the aliases used in those queries
557+
CREATE TABLE mvtest_foo_data AS SELECT i,
558+
i+1 AS tid,
559+
md5(random()::text) AS mv,
560+
md5(random()::text) AS newdata,
561+
md5(random()::text) AS newdata2,
562+
md5(random()::text) AS diff
555563
FROM generate_series(1, 10) i;
556564
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
557565
CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;

‎src/test/regress/sql/matview.sql

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -211,7 +211,15 @@ DROP TABLE mvtest_v CASCADE;
211211
-- make sure running as superuser works when MV owned by another role (bug #11208)
212212
CREATE ROLE regress_user_mvtest;
213213
SET ROLE regress_user_mvtest;
214-
CREATETABLEmvtest_foo_dataASSELECT i, md5(random()::text)
214+
-- this test case also checks for ambiguity in the queries issued by
215+
-- refresh_by_match_merge(), by choosing column names that intentionally
216+
-- duplicate all the aliases used in those queries
217+
CREATETABLEmvtest_foo_dataASSELECT i,
218+
i+1AS tid,
219+
md5(random()::text)AS mv,
220+
md5(random()::text)AS newdata,
221+
md5(random()::text)AS newdata2,
222+
md5(random()::text)AS diff
215223
FROM generate_series(1,10) i;
216224
CREATE MATERIALIZED VIEW mvtest_mv_fooASSELECT*FROM mvtest_foo_data;
217225
CREATE MATERIALIZED VIEW mvtest_mv_fooASSELECT*FROM mvtest_foo_data;

0 commit comments

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