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

Commit9179a82

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 parent789d806 commit9179a82

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

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

760-
leftop=quote_qualified_identifier("_$newdata",
767+
leftop=quote_qualified_identifier("newdata",
761768
NameStr(attr->attname));
762-
rightop=quote_qualified_identifier("_$mv",
769+
rightop=quote_qualified_identifier("mv",
763770
NameStr(attr->attname));
764771

765772
generate_operator_clause(&querybuf,
@@ -787,8 +794,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
787794
Assert(foundUniqueIndex);
788795

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

794801
/* Create the temporary "diff" table. */
@@ -814,19 +821,19 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
814821
/* Deletes must come before inserts; do them first. */
815822
resetStringInfo(&querybuf);
816823
appendStringInfo(&querybuf,
817-
"DELETE FROM %s_$mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
818-
"(SELECT_$diff.tid FROM %s_$diff "
819-
"WHERE_$diff.tid IS NOT NULL "
820-
"AND_$diff._$newdata IS NULL)",
824+
"DELETE FROM %s mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
825+
"(SELECT diff.tid FROM %s diff "
826+
"WHERE diff.tid IS NOT NULL "
827+
"AND diff.newdata IS NULL)",
821828
matviewname,diffname);
822829
if (SPI_exec(querybuf.data,0)!=SPI_OK_DELETE)
823830
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
824831

825832
/* Inserts go last. */
826833
resetStringInfo(&querybuf);
827834
appendStringInfo(&querybuf,
828-
"INSERT INTO %s SELECT (_$diff._$newdata).* "
829-
"FROM %s_$diff WHERE tid IS NULL",
835+
"INSERT INTO %s SELECT (diff.newdata).* "
836+
"FROM %s diff WHERE tid IS NULL",
830837
matviewname,diffname);
831838
if (SPI_exec(querybuf.data,0)!=SPI_OK_INSERT)
832839
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