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

Commit841c29c

Browse files
committed
Various cleanups for REFRESH MATERIALIZED VIEW CONCURRENTLY.
Open and lock each index before checking definition in RMVC. TheExclusiveLock on the related table is not viewed as sufficient toensure that no changes are made to the index definition, andinvalidation messages from other backends might have been missed.Additionally, use RelationGetIndexExpressions() and check for NILrather than doing our own loop.Protect against redefinition of tid and rowvar operators in RMVC.While working on this, noticed that the fixes for bugs found duringthe CF made the UPDATE statement useless, since no rows couldqualify for that treatment any more. Ripping out code to supportthe UPDATE statement simplified the operator cleanups.Change slightly confusing local field name.Use meaningful alias names on queries in refresh_by_match_merge().Per concerns of raised by Andres Freund and comments andsuggestions from Noah Misch. Some additional issues remain, whichwill be addressed separately.
1 parent221e92f commit841c29c

File tree

1 file changed

+34
-79
lines changed

1 file changed

+34
-79
lines changed

‎src/backend/commands/matview.c

Lines changed: 34 additions & 79 deletions
Original file line numberDiff line numberDiff line change
@@ -496,19 +496,14 @@ mv_GenerateOper(StringInfo buf, Oid opoid)
496496
* columns equal. The behavior of NULLs on equality tests and on UNIQUE
497497
* indexes turns out to be quite convenient here; the tests we need to make
498498
* are consistent with default behavior. If there is at least one UNIQUE
499-
* index on the materialized view, we have exactly the guarantee we need. By
500-
* joining based on equality on all columns which are part of any unique
501-
* index, we identify the rows on which we can use UPDATE without any problem.
502-
* If any column is NULL in either the old or new version of a row (or both),
503-
* we must use DELETE and INSERT, since there could be multiple rows which are
504-
* NOT DISTINCT FROM each other, and we could otherwise end up with the wrong
505-
* number of occurrences in the updated relation. The temporary table used to
506-
* hold the diff results contains just the TID of the old record (if matched)
507-
* and the ROW from the new table as a single column of complex record type
508-
* (if matched).
499+
* index on the materialized view, we have exactly the guarantee we need.
509500
*
510-
* Once we have the diff table, we perform set-based DELETE, UPDATE, and
511-
* INSERT operations against the materialized view, and discard both temporary
501+
* The temporary table used to hold the diff results contains just the TID of
502+
* the old record (if matched) and the ROW from the new table as a single
503+
* column of complex record type (if matched).
504+
*
505+
* Once we have the diff table, we perform set-based DELETE and INSERT
506+
* operations against the materialized view, and discard both temporary
512507
* tables.
513508
*
514509
* Everything from the generation of the new data to applying the differences
@@ -567,9 +562,12 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
567562
*/
568563
resetStringInfo(&querybuf);
569564
appendStringInfo(&querybuf,
570-
"SELECT x FROM %s x WHERE x IS NOT NULL AND EXISTS "
571-
"(SELECT * FROM %s y WHERE y IS NOT NULL "
572-
"AND (y.*) = (x.*) AND y.ctid <> x.ctid) LIMIT 1",
565+
"SELECT newdata FROM %s newdata "
566+
"WHERE newdata IS NOT NULL AND EXISTS "
567+
"(SELECT * FROM %s newdata2 WHERE newdata2 IS NOT NULL "
568+
"AND newdata2 OPERATOR(pg_catalog.=) newdata "
569+
"AND newdata2.ctid OPERATOR(pg_catalog.<>) "
570+
"newdata.ctid) LIMIT 1",
573571
tempname,tempname);
574572
if (SPI_execute(querybuf.data, false,1)!=SPI_OK_SELECT)
575573
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
@@ -587,7 +585,8 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
587585
resetStringInfo(&querybuf);
588586
appendStringInfo(&querybuf,
589587
"CREATE TEMP TABLE %s AS "
590-
"SELECT x.ctid AS tid, y FROM %s x FULL JOIN %s y ON (",
588+
"SELECT mv.ctid AS tid, newdata "
589+
"FROM %s mv FULL JOIN %s newdata ON (",
591590
diffname,matviewname,tempname);
592591

593592
/*
@@ -603,52 +602,45 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
603602
foreach(indexoidscan,indexoidlist)
604603
{
605604
Oidindexoid=lfirst_oid(indexoidscan);
605+
RelationindexRel;
606606
HeapTupleindexTuple;
607-
Form_pg_indexindex;
607+
Form_pg_indexindexStruct;
608608

609+
indexRel=index_open(indexoid,RowExclusiveLock);
609610
indexTuple=SearchSysCache1(INDEXRELID,ObjectIdGetDatum(indexoid));
610611
if (!HeapTupleIsValid(indexTuple))/* should not happen */
611612
elog(ERROR,"cache lookup failed for index %u",indexoid);
612-
index= (Form_pg_index)GETSTRUCT(indexTuple);
613+
indexStruct= (Form_pg_index)GETSTRUCT(indexTuple);
613614

614615
/* We're only interested if it is unique and valid. */
615-
if (index->indisunique&&IndexIsValid(index))
616+
if (indexStruct->indisunique&&IndexIsValid(indexStruct))
616617
{
617-
intnumatts=index->indnatts;
618+
intnumatts=indexStruct->indnatts;
618619
inti;
619-
boolexpr= false;
620-
RelationindexRel;
621620

622621
/* Skip any index on an expression. */
623-
for (i=0;i<numatts;i++)
624-
{
625-
if (index->indkey.values[i]==0)
626-
{
627-
expr= true;
628-
break;
629-
}
630-
}
631-
if (expr)
622+
if (RelationGetIndexExpressions(indexRel)!=NIL)
632623
{
624+
index_close(indexRel,NoLock);
633625
ReleaseSysCache(indexTuple);
634626
continue;
635627
}
636628

637629
/* Skip partial indexes. */
638-
indexRel=index_open(index->indexrelid,RowExclusiveLock);
639630
if (RelationGetIndexPredicate(indexRel)!=NIL)
640631
{
641632
index_close(indexRel,NoLock);
642633
ReleaseSysCache(indexTuple);
643634
continue;
644635
}
636+
645637
/* Hold the locks, since we're about to run DML which needs them. */
646638
index_close(indexRel,NoLock);
647639

648640
/* Add quals for all columns from this index. */
649641
for (i=0;i<numatts;i++)
650642
{
651-
intattnum=index->indkey.values[i];
643+
intattnum=indexStruct->indkey.values[i];
652644
Oidtype;
653645
Oidop;
654646
constchar*colname;
@@ -671,11 +663,11 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
671663
appendStringInfoString(&querybuf," AND ");
672664

673665
colname=quote_identifier(NameStr((tupdesc->attrs[attnum-1])->attname));
674-
appendStringInfo(&querybuf,"y.%s ",colname);
666+
appendStringInfo(&querybuf,"newdata.%s ",colname);
675667
type=attnumTypeId(matviewRel,attnum);
676668
op=lookup_type_cache(type,TYPECACHE_EQ_OPR)->eq_opr;
677669
mv_GenerateOper(&querybuf,op);
678-
appendStringInfo(&querybuf,"x.%s",colname);
670+
appendStringInfo(&querybuf,"mv.%s",colname);
679671

680672
foundUniqueIndex= true;
681673
}
@@ -693,7 +685,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
693685
errhint("Create a UNIQUE index with no WHERE clause on one or more columns of the materialized view.")));
694686

695687
appendStringInfoString(&querybuf,
696-
" ANDy =x) WHERE(y.*) ISDISTINCT FROM (x.*)"
688+
" ANDnewdata =mv) WHEREnewdata ISNULL OR mv IS NULL"
697689
" ORDER BY tid");
698690

699691
/* Create the temporary "diff" table. */
@@ -726,56 +718,19 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid)
726718
/* Deletes must come before inserts; do them first. */
727719
resetStringInfo(&querybuf);
728720
appendStringInfo(&querybuf,
729-
"DELETE FROM %s WHERE ctidIN "
730-
"(SELECTd.tid FROM %sd "
731-
"WHEREd.tid IS NOT NULL "
732-
"AND(d.y) IS NOT DISTINCT FROM NULL)",
721+
"DELETE FROM %smvWHERE ctidOPERATOR(pg_catalog.=) ANY "
722+
"(SELECTdiff.tid FROM %sdiff "
723+
"WHEREdiff.tid IS NOT NULL "
724+
"ANDdiff.newdata IS NULL)",
733725
matviewname,diffname);
734726
if (SPI_exec(querybuf.data,0)!=SPI_OK_DELETE)
735727
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
736728

737-
/* Updates before inserts gives a better chance at HOT updates. */
738-
resetStringInfo(&querybuf);
739-
appendStringInfo(&querybuf,"UPDATE %s x SET ",matviewname);
740-
741-
{
742-
inti;
743-
booltargetColFound= false;
744-
745-
for (i=0;i<tupdesc->natts;i++)
746-
{
747-
constchar*colname;
748-
749-
if (tupdesc->attrs[i]->attisdropped)
750-
continue;
751-
752-
if (usedForQual[i])
753-
continue;
754-
755-
if (targetColFound)
756-
appendStringInfoString(&querybuf,", ");
757-
targetColFound= true;
758-
759-
colname=quote_identifier(NameStr((tupdesc->attrs[i])->attname));
760-
appendStringInfo(&querybuf,"%s = (d.y).%s",colname,colname);
761-
}
762-
763-
if (targetColFound)
764-
{
765-
appendStringInfo(&querybuf,
766-
" FROM %s d "
767-
"WHERE d.tid IS NOT NULL AND x.ctid = d.tid",
768-
diffname);
769-
770-
if (SPI_exec(querybuf.data,0)!=SPI_OK_UPDATE)
771-
elog(ERROR,"SPI_exec failed: %s",querybuf.data);
772-
}
773-
}
774-
775729
/* Inserts go last. */
776730
resetStringInfo(&querybuf);
777731
appendStringInfo(&querybuf,
778-
"INSERT INTO %s SELECT (y).* FROM %s WHERE tid IS NULL",
732+
"INSERT INTO %s SELECT (diff.newdata).* "
733+
"FROM %s diff WHERE tid IS NULL",
779734
matviewname,diffname);
780735
if (SPI_exec(querybuf.data,0)!=SPI_OK_INSERT)
781736
elog(ERROR,"SPI_exec failed: %s",querybuf.data);

0 commit comments

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