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

Commitba9f665

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 parentda188b9 commitba9f665

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
@@ -525,9 +525,12 @@ transientrel_destroy(DestReceiver *self)
525525
/*
526526
* Given a qualified temporary table name, append an underscore followed by
527527
* the given integer, to make a new table name based on the old one.
528+
* The result is a palloc'd string.
528529
*
529-
* This leaks memory through palloc(), which won't be cleaned up until the
530-
* current memory context is freed.
530+
* As coded, this would fail to make a valid SQL name if the given name were,
531+
* say, "FOO"."BAR". Currently, the table name portion of the input will
532+
* never be double-quoted because it's of the form "pg_temp_NNN", cf
533+
* make_new_heap(). But we might have to work harder someday.
531534
*/
532535
staticchar*
533536
make_temptable_name_n(char*tempname,intn)
@@ -615,16 +618,20 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
615618
* that in a way that allows showing the first duplicated row found. Even
616619
* after we pass this test, a unique index on the materialized view may
617620
* find a duplicate key problem.
621+
*
622+
* Note: here and below, we use "tablename.*::tablerowtype" as a hack to
623+
* keep ".*" from being expanded into multiple columns in a SELECT list.
624+
* Compare ruleutils.c's get_variable().
618625
*/
619626
resetStringInfo(&querybuf);
620627
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)",
627-
tempname,tempname);
628+
"SELECT newdata.*::%s FROM %s newdata "
629+
"WHERE newdata.* IS NOT NULL AND EXISTS "
630+
"(SELECT 1 FROM %s newdata2 WHERE newdata2.* IS NOT NULL "
631+
"AND newdata2.* OPERATOR(pg_catalog.*=) newdata.* "
632+
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
633+
"newdata.ctid)",
634+
tempname,tempname,tempname);
628635
if (SPI_execute(querybuf.data, false,1)!=SPI_OK_SELECT)
629636
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
630637
if (SPI_processed>0)
@@ -651,9 +658,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
651658
resetStringInfo(&querybuf);
652659
appendStringInfo(&querybuf,
653660
"CREATE TEMP TABLE %s AS "
654-
"SELECT_$mv.ctid AS tid,_$newdata "
655-
"FROM %s_$mv FULL JOIN %s_$newdata ON (",
656-
diffname,matviewname,tempname);
661+
"SELECT mv.ctid AS tid,newdata.*::%s ASnewdata "
662+
"FROM %s mv FULL JOIN %s newdata ON (",
663+
diffname,tempname,matviewname,tempname);
657664

658665
/*
659666
* Get the list of index OIDs for the table from the relcache, and look up
@@ -745,9 +752,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
745752
if (foundUniqueIndex)
746753
appendStringInfoString(&querybuf," AND ");
747754

748-
leftop=quote_qualified_identifier("_$newdata",
755+
leftop=quote_qualified_identifier("newdata",
749756
NameStr(attr->attname));
750-
rightop=quote_qualified_identifier("_$mv",
757+
rightop=quote_qualified_identifier("mv",
751758
NameStr(attr->attname));
752759

753760
generate_operator_clause(&querybuf,
@@ -775,8 +782,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
775782
Assert(foundUniqueIndex);
776783

777784
appendStringInfoString(&querybuf,
778-
" AND_$newdata OPERATOR(pg_catalog.*=)_$mv) "
779-
"WHERE_$newdata IS NULL OR_$mv IS NULL "
785+
" AND newdata.* OPERATOR(pg_catalog.*=)mv.*) "
786+
"WHERE newdata.* IS NULL ORmv.* IS NULL "
780787
"ORDER BY tid");
781788

782789
/* Create the temporary "diff" table. */
@@ -802,19 +809,19 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
802809
/* Deletes must come before inserts; do them first. */
803810
resetStringInfo(&querybuf);
804811
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)",
812+
"DELETE FROM %s mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
813+
"(SELECT diff.tid FROM %s diff "
814+
"WHERE diff.tid IS NOT NULL "
815+
"AND diff.newdata IS NULL)",
809816
matviewname,diffname);
810817
if (SPI_exec(querybuf.data,0)!=SPI_OK_DELETE)
811818
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
812819

813820
/* Inserts go last. */
814821
resetStringInfo(&querybuf);
815822
appendStringInfo(&querybuf,
816-
"INSERT INTO %s SELECT (_$diff._$newdata).* "
817-
"FROM %s_$diff WHERE tid IS NULL",
823+
"INSERT INTO %s SELECT (diff.newdata).* "
824+
"FROM %s diff WHERE tid IS NULL",
818825
matviewname,diffname);
819826
if (SPI_exec(querybuf.data,0)!=SPI_OK_INSERT)
820827
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