On Thu, 16 Jun 2022 at 15:05, Tomas Vondra <tomas.von...@enterprisedb.com> wrote: > > I've pushed the revert. Let's try again for PG16.
As we discussed in person at the developer meeting, here's a patch to try again for PG16. It combines the committed patches with my fix, and adds some additional comments and polish. I am confident the code is correct, but not that it is clean (see the commit message of the patch for details). Kind regards, Matthias van de Meent PS. I'm adding this to the commitfest Original patch thread: https://www.postgresql.org/message-id/flat/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg%40mail.gmail.com Other relevant: https://www.postgresql.org/message-id/flat/CA%2BTgmoZOgdoAFH9HatRwuydOZkMdyPi%3D97rNhsu%3DhQBBYs%2BgXQ%40mail.gmail.com
From 402a07d45b9aae70f8a01edcce059eaa13783360 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <tomas.vondra@postgresql.org> Date: Tue, 30 Nov 2021 19:15:14 +0100 Subject: [PATCH v1] Ignore BRIN indexes when checking for HOT updates When determining whether an index update may be skipped by using HOT, we can ignore attributes indexed by block summarizing indexes without references to individual tuples that need to be cleaned up. This also removes rd_indexattr list, and replaces it with rd_attrsvalid flag. The list was not used anywhere, and a simple flag is sufficient. A new type TU_UpdateIndexes is invented provide a signal to the executor to determine which indexes to update - no indexes, all indexes, or only the summarizing indexes. One otherwise unused bit in the heap tuple header is (ab)used to signal that the HOT update would still update at least one summarizing index. The bit is cleared immediately Original patch by Josef Simanek, various fixes and improvements by Tomas Vondra and me. Authors: Josef Simanek, Tomas Vondra, Matthias van de Meent Reviewed-by: Tomas Vondra, Alvaro Herrera --- doc/src/sgml/indexam.sgml | 13 +++ src/backend/access/brin/brin.c | 1 + src/backend/access/gin/ginutil.c | 1 + src/backend/access/gist/gist.c | 1 + src/backend/access/hash/hash.c | 1 + src/backend/access/heap/heapam.c | 15 ++- src/backend/access/heap/heapam_handler.c | 21 +++- src/backend/access/nbtree/nbtree.c | 1 + src/backend/access/spgist/spgutils.c | 1 + src/backend/access/table/tableam.c | 2 +- src/backend/catalog/index.c | 9 +- src/backend/catalog/indexing.c | 29 ++++- src/backend/commands/copyfrom.c | 5 +- src/backend/commands/indexcmds.c | 10 +- src/backend/executor/execIndexing.c | 37 ++++-- src/backend/executor/execReplication.c | 9 +- src/backend/executor/nodeModifyTable.c | 13 ++- src/backend/nodes/makefuncs.c | 4 +- src/backend/utils/cache/relcache.c | 62 +++++++--- src/include/access/amapi.h | 2 + src/include/access/htup_details.h | 29 +++++ src/include/access/tableam.h | 19 ++- src/include/executor/executor.h | 3 +- src/include/nodes/execnodes.h | 2 + src/include/nodes/makefuncs.h | 4 +- src/include/utils/rel.h | 4 +- src/include/utils/relcache.h | 5 +- .../modules/dummy_index_am/dummy_index_am.c | 1 + src/test/regress/expected/stats.out | 110 ++++++++++++++++++ src/test/regress/sql/stats.sql | 82 ++++++++++++- 30 files changed, 431 insertions(+), 65 deletions(-) diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml index 4f83970c85..897419ec95 100644 --- a/doc/src/sgml/indexam.sgml +++ b/doc/src/sgml/indexam.sgml @@ -127,6 +127,9 @@ typedef struct IndexAmRoutine bool amcaninclude; /* does AM use maintenance_work_mem? */ bool amusemaintenanceworkmem; + /* does AM summarize tuples, with at least all tuples in the block + * summarized in one summary */ + bool amsummarizing; /* OR of parallel vacuum flags */ uint8 amparallelvacuumoptions; /* type of data stored in index, or InvalidOid if variable */ @@ -247,6 +250,16 @@ typedef struct IndexAmRoutine null, independently of <structfield>amoptionalkey</structfield>. </para> + <para> + The <structfield>amsummarizing</structfield> flag indicates whether the + access method summarizes the indexed tuples, with summarizing granularity + of at least per block. + Access methods that do not point to individual tuples, but to (like + <acronym>BRIN</acronym>), may allow the <acronym>HOT</acronym> optimization + to continue. This does not apply to attributes referenced in index + predicates, an update of such attribute always disables <acronym>HOT</acronym>. + </para> + </sect1> <sect1 id="index-functions"> diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c index de1427a1e0..c4bdd0e7b0 100644 --- a/src/backend/access/brin/brin.c +++ b/src/backend/access/brin/brin.c @@ -109,6 +109,7 @@ brinhandler(PG_FUNCTION_ARGS) amroutine->amcanparallel = false; amroutine->amcaninclude = false; amroutine->amusemaintenanceworkmem = false; + amroutine->amsummarizing = true; amroutine->amparallelvacuumoptions = VACUUM_OPTION_PARALLEL_CLEANUP; amroutine->amkeytype = InvalidOid; diff --git a/src/backend/access/gin/ginutil.c b/src/backend/access/gin/ginutil.c index f05128ecf5..03fec1704e 100644 --- a/src/backend/access/gin/ginutil.c +++ b/src/backend/access/gin/ginutil.c @@ -56,6 +56,7 @@ ginhandler(PG_FUNCTION_ARGS) amroutine->amcanparallel = false; amroutine->amcaninclude = false; amroutine->amusemaintenanceworkmem = true; + amroutine->amsummarizing = false; amroutine->amparallelvacuumoptions = VACUUM_OPTION_PARALLEL_BULKDEL | VACUUM_OPTION_PARALLEL_CLEANUP; amroutine->amkeytype = InvalidOid; diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c index ba394f08f6..ea72bcce1b 100644 --- a/src/backend/access/gist/gist.c +++ b/src/backend/access/gist/gist.c @@ -78,6 +78,7 @@ gisthandler(PG_FUNCTION_ARGS) amroutine->amcanparallel = false; amroutine->amcaninclude = true; amroutine->amusemaintenanceworkmem = false; + amroutine->amsummarizing = false; amroutine->amparallelvacuumoptions = VACUUM_OPTION_PARALLEL_BULKDEL | VACUUM_OPTION_PARALLEL_COND_CLEANUP; amroutine->amkeytype = InvalidOid; diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c index eb258337d6..fc5d97f606 100644 --- a/src/backend/access/hash/hash.c +++ b/src/backend/access/hash/hash.c @@ -75,6 +75,7 @@ hashhandler(PG_FUNCTION_ARGS) amroutine->amcanparallel = false; amroutine->amcaninclude = false; amroutine->amusemaintenanceworkmem = false; + amroutine->amsummarizing = false; amroutine->amparallelvacuumoptions = VACUUM_OPTION_PARALLEL_BULKDEL; amroutine->amkeytype = INT4OID; diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 7eb79cee58..99668c3eae 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -2929,6 +2929,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, TM_Result result; TransactionId xid = GetCurrentTransactionId(); Bitmapset *hot_attrs; + Bitmapset *sum_attrs; Bitmapset *key_attrs; Bitmapset *id_attrs; Bitmapset *interesting_attrs; @@ -2951,6 +2952,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, bool have_tuple_lock = false; bool iscombo; bool use_hot_update = false; + bool summarized_update = false; bool key_intact; bool all_visible_cleared = false; bool all_visible_cleared_new = false; @@ -2996,12 +2998,14 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, * Note that we get copies of each bitmap, so we need not worry about * relcache flush happening midway through. */ - hot_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_ALL); + hot_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_HOT_BLOCKING); + sum_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_SUMMARIZED); key_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_KEY); id_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_IDENTITY_KEY); interesting_attrs = NULL; interesting_attrs = bms_add_members(interesting_attrs, hot_attrs); + interesting_attrs = bms_add_members(interesting_attrs, sum_attrs); interesting_attrs = bms_add_members(interesting_attrs, key_attrs); interesting_attrs = bms_add_members(interesting_attrs, id_attrs); @@ -3312,6 +3316,7 @@ l2: if (vmbuffer != InvalidBuffer) ReleaseBuffer(vmbuffer); bms_free(hot_attrs); + bms_free(sum_attrs); bms_free(key_attrs); bms_free(id_attrs); bms_free(modified_attrs); @@ -3633,7 +3638,11 @@ l2: * changed. */ if (!bms_overlap(modified_attrs, hot_attrs)) + { use_hot_update = true; + if (bms_overlap(modified_attrs, sum_attrs)) + summarized_update = true; + } } else { @@ -3793,10 +3802,14 @@ l2: heap_freetuple(heaptup); } + if (summarized_update) + HeapTupleHeaderSetSummaryUpdate(newtup->t_data); + if (old_key_tuple != NULL && old_key_copied) heap_freetuple(old_key_tuple); bms_free(hot_attrs); + bms_free(sum_attrs); bms_free(key_attrs); bms_free(id_attrs); bms_free(modified_attrs); diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index c4b1916d36..2072eb351d 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -314,7 +314,7 @@ static TM_Result heapam_tuple_update(Relation relation, ItemPointer otid, TupleTableSlot *slot, CommandId cid, Snapshot snapshot, Snapshot crosscheck, bool wait, TM_FailureData *tmfd, - LockTupleMode *lockmode, bool *update_indexes) + LockTupleMode *lockmode, TU_UpdateIndexes *update_indexes) { bool shouldFree = true; HeapTuple tuple = ExecFetchSlotHeapTuple(slot, true, &shouldFree); @@ -334,9 +334,24 @@ heapam_tuple_update(Relation relation, ItemPointer otid, TupleTableSlot *slot, * Note: heap_update returns the tid (location) of the new tuple in the * t_self field. * - * If it's a HOT update, we mustn't insert new index entries. + * If it is a HOT update, we must not insert new index entries on all index. + * However, if it updates columns in summarized indexes, we must still update + * those summarizing indexes, lest we fail to update those summaries and + * get incorrect results (for example, minmax bounds of the block may change). */ - *update_indexes = result == TM_Ok && !HeapTupleIsHeapOnly(tuple); + if (result != TM_Ok) + *update_indexes = TUUI_None; + else if (!HeapTupleIsHeapOnly(tuple)) + *update_indexes = TUUI_All; + else if (HeapTupleHeaderIsHOTWithSummaryUpdate(tuple->t_data)) + { + *update_indexes = TUUI_Summarizing; + + /* Clear temporary bits */ + HeapTupleHeaderClearSummaryUpdate(tuple->t_data); + } + else + *update_indexes = TUUI_None; if (shouldFree) pfree(tuple); diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index 1cc88da032..681c30b0d8 100644 --- a/src/backend/access/nbtree/nbtree.c +++ b/src/backend/access/nbtree/nbtree.c @@ -114,6 +114,7 @@ bthandler(PG_FUNCTION_ARGS) amroutine->amcanparallel = true; amroutine->amcaninclude = true; amroutine->amusemaintenanceworkmem = false; + amroutine->amsummarizing = false; amroutine->amparallelvacuumoptions = VACUUM_OPTION_PARALLEL_BULKDEL | VACUUM_OPTION_PARALLEL_COND_CLEANUP; amroutine->amkeytype = InvalidOid; diff --git a/src/backend/access/spgist/spgutils.c b/src/backend/access/spgist/spgutils.c index 3761f2c193..4e7ff1d160 100644 --- a/src/backend/access/spgist/spgutils.c +++ b/src/backend/access/spgist/spgutils.c @@ -62,6 +62,7 @@ spghandler(PG_FUNCTION_ARGS) amroutine->amcanparallel = false; amroutine->amcaninclude = true; amroutine->amusemaintenanceworkmem = false; + amroutine->amsummarizing = false; amroutine->amparallelvacuumoptions = VACUUM_OPTION_PARALLEL_BULKDEL | VACUUM_OPTION_PARALLEL_COND_CLEANUP; amroutine->amkeytype = InvalidOid; diff --git a/src/backend/access/table/tableam.c b/src/backend/access/table/tableam.c index ef0d34fcee..a5e6c92f35 100644 --- a/src/backend/access/table/tableam.c +++ b/src/backend/access/table/tableam.c @@ -345,7 +345,7 @@ void simple_table_tuple_update(Relation rel, ItemPointer otid, TupleTableSlot *slot, Snapshot snapshot, - bool *update_indexes) + TU_UpdateIndexes *update_indexes) { TM_Result result; TM_FailureData tmfd; diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 41b16cb89b..e2fd035f44 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1370,7 +1370,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, oldInfo->ii_Unique, oldInfo->ii_NullsNotDistinct, false, /* not ready for inserts */ - true); + true, + indexRelation->rd_indam->amsummarizing); /* * Extract the list of column names and the column numbers for the new @@ -2442,7 +2443,8 @@ BuildIndexInfo(Relation index) indexStruct->indisunique, indexStruct->indnullsnotdistinct, indexStruct->indisready, - false); + false, + index->rd_indam->amsummarizing); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) @@ -2502,7 +2504,8 @@ BuildDummyIndexInfo(Relation index) indexStruct->indisunique, indexStruct->indnullsnotdistinct, indexStruct->indisready, - false); + false, + index->rd_indam->amsummarizing); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c index bb7cc3601c..262a246e44 100644 --- a/src/backend/catalog/indexing.c +++ b/src/backend/catalog/indexing.c @@ -82,15 +82,27 @@ CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple) IndexInfo **indexInfoArray; Datum values[INDEX_MAX_KEYS]; bool isnull[INDEX_MAX_KEYS]; + bool onlySummarized = false; /* - * HOT update does not require index inserts. But with asserts enabled we - * want to check that it'd be legal to currently insert into the - * table/index. + * HOT updates may be a 'summary update', for which we need to make + * sure to update summarized indexes. */ + if (HeapTupleHeaderIsHOTWithSummaryUpdate(heapTuple->t_data)) + { + HeapTupleHeaderClearSummaryUpdate(heapTuple->t_data); + onlySummarized = true; + } #ifndef USE_ASSERT_CHECKING - if (HeapTupleIsHeapOnly(heapTuple)) + else if (HeapTupleIsHeapOnly(heapTuple)) + { + /* + * Normal HOT update does not require index inserts. But with + * asserts enabled we want to check that it'd be legal to + * currently insert into the table/index. + */ return; + } #endif /* @@ -135,13 +147,20 @@ CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple) /* see earlier check above */ #ifdef USE_ASSERT_CHECKING - if (HeapTupleIsHeapOnly(heapTuple)) + if (HeapTupleIsHeapOnly(heapTuple) && !onlySummarized) { Assert(!ReindexIsProcessingIndex(RelationGetRelid(index))); continue; } #endif /* USE_ASSERT_CHECKING */ + /* + * Skip insertions into non-summarizing indexes if we only need + * to update summarizing indexes + */ + if (onlySummarized && !indexInfo->ii_Summarizing) + continue; + /* * FormIndexDatum fills in its values and isnull parameters with the * appropriate values for the column(s) of the index. diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index af52faca6d..564520289a 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -435,7 +435,7 @@ CopyMultiInsertBufferFlush(CopyMultiInsertInfo *miinfo, recheckIndexes = ExecInsertIndexTuples(resultRelInfo, buffer->slots[i], estate, false, - false, NULL, NIL); + false, NULL, NIL, false); ExecARInsertTriggers(estate, resultRelInfo, slots[i], recheckIndexes, cstate->transition_capture); @@ -1254,7 +1254,8 @@ CopyFrom(CopyFromState cstate) false, false, NULL, - NIL); + NIL, + false); } /* AFTER ROW INSERT Triggers */ diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 16ec0b114e..ff48f44c66 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -184,6 +184,7 @@ CheckIndexCompatible(Oid oldId, Form_pg_am accessMethodForm; IndexAmRoutine *amRoutine; bool amcanorder; + bool amsummarizing; int16 *coloptions; IndexInfo *indexInfo; int numberOfAttributes; @@ -222,6 +223,7 @@ CheckIndexCompatible(Oid oldId, ReleaseSysCache(tuple); amcanorder = amRoutine->amcanorder; + amsummarizing = amRoutine->amsummarizing; /* * Compute the operator classes, collations, and exclusion operators for @@ -232,7 +234,8 @@ CheckIndexCompatible(Oid oldId, * ii_NumIndexKeyAttrs with same value. */ indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes, - accessMethodId, NIL, NIL, false, false, false, false); + accessMethodId, NIL, NIL, false, false, + false, false, amsummarizing); typeObjectId = palloc_array(Oid, numberOfAttributes); collationObjectId = palloc_array(Oid, numberOfAttributes); classObjectId = palloc_array(Oid, numberOfAttributes); @@ -550,6 +553,7 @@ DefineIndex(Oid relationId, Form_pg_am accessMethodForm; IndexAmRoutine *amRoutine; bool amcanorder; + bool amissummarizing; amoptions_function amoptions; bool partitioned; bool safe_index; @@ -866,6 +870,7 @@ DefineIndex(Oid relationId, amcanorder = amRoutine->amcanorder; amoptions = amRoutine->amoptions; + amissummarizing = amRoutine->amsummarizing; pfree(amRoutine); ReleaseSysCache(tuple); @@ -897,7 +902,8 @@ DefineIndex(Oid relationId, stmt->unique, stmt->nulls_not_distinct, !concurrent, - concurrent); + concurrent, + amissummarizing); typeObjectId = palloc_array(Oid, numberOfAttributes); collationObjectId = palloc_array(Oid, numberOfAttributes); diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c index 6e88e72813..0533711219 100644 --- a/src/backend/executor/execIndexing.c +++ b/src/backend/executor/execIndexing.c @@ -259,15 +259,24 @@ ExecCloseIndices(ResultRelInfo *resultRelInfo) * into all the relations indexing the result relation * when a heap tuple is inserted into the result relation. * - * When 'update' is true, executor is performing an UPDATE - * that could not use an optimization like heapam's HOT (in - * more general terms a call to table_tuple_update() took - * place and set 'update_indexes' to true). Receiving this - * hint makes us consider if we should pass down the - * 'indexUnchanged' hint in turn. That's something that we - * figure out for each index_insert() call iff 'update' is - * true. (When 'update' is false we already know not to pass - * the hint to any index.) + * When 'update' is true and 'onlySummarizing' is false, + * executor is performing an UPDATE that could not use an + * optimization like heapam's HOT (in more general terms a + * call to table_tuple_update() took place and set + * 'update_indexes' to TUUI_All). Receiving this hint makes + * us consider if we should pass down the 'indexUnchanged' + * hint in turn. That's something that we figure out for + * each index_insert() call iff 'update' is true. + * (When 'update' is false we already know not to pass the + * hint to any index.) + * + * If onlySummarizing is set, an equivalent optimization to + * HOT has been applied and any updated columns are indexed + * only by summarizing indexes (or in more general terms a + * call to table_tuple_update() took place and set + * 'update_indexes' to TUUI_Summarizing). We can (and must) + * therefore only update the indexes that have + * 'amsummarizing' = true. * * Unique and exclusion constraints are enforced at the same * time. This returns a list of index OIDs for any unique or @@ -287,7 +296,8 @@ ExecInsertIndexTuples(ResultRelInfo *resultRelInfo, bool update, bool noDupErr, bool *specConflict, - List *arbiterIndexes) + List *arbiterIndexes, + bool onlySummarizing) { ItemPointer tupleid = &slot->tts_tid; List *result = NIL; @@ -343,6 +353,13 @@ ExecInsertIndexTuples(ResultRelInfo *resultRelInfo, if (!indexInfo->ii_ReadyForInserts) continue; + /* + * Skip processing of non-summarizing indexes if we only need + * to update summarizing indexes + */ + if (onlySummarizing && !indexInfo->ii_Summarizing) + continue; + /* Check for partial index */ if (indexInfo->ii_Predicate != NIL) { diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c index c484f5c301..aca8484e96 100644 --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -445,7 +445,7 @@ ExecSimpleRelationInsert(ResultRelInfo *resultRelInfo, if (resultRelInfo->ri_NumIndices > 0) recheckIndexes = ExecInsertIndexTuples(resultRelInfo, slot, estate, false, false, - NULL, NIL); + NULL, NIL, false); /* AFTER ROW INSERT Triggers */ ExecARInsertTriggers(estate, resultRelInfo, slot, @@ -493,7 +493,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo, if (!skip_tuple) { List *recheckIndexes = NIL; - bool update_indexes; + TU_UpdateIndexes update_indexes; /* Compute stored generated columns */ if (rel->rd_att->constr && @@ -510,10 +510,11 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo, simple_table_tuple_update(rel, tid, slot, estate->es_snapshot, &update_indexes); - if (resultRelInfo->ri_NumIndices > 0 && update_indexes) + if (resultRelInfo->ri_NumIndices > 0 && update_indexes != TUUI_None) recheckIndexes = ExecInsertIndexTuples(resultRelInfo, slot, estate, true, false, - NULL, NIL); + NULL, NIL, + update_indexes == TUUI_Summarizing); /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(estate, resultRelInfo, diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index a94d7f86e5..8320a602e8 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -125,8 +125,8 @@ typedef struct ModifyTableContext typedef struct UpdateContext { bool updated; /* did UPDATE actually occur? */ - bool updateIndexes; /* index update required? */ bool crossPartUpdate; /* was it a cross-partition update? */ + TU_UpdateIndexes updateIndexes; /* Which index updates are required? */ /* * Lock mode to acquire on the latest tuple version before performing @@ -1106,7 +1106,8 @@ ExecInsert(ModifyTableContext *context, recheckIndexes = ExecInsertIndexTuples(resultRelInfo, slot, estate, false, true, &specConflict, - arbiterIndexes); + arbiterIndexes, + false); /* adjust the tuple's state accordingly */ table_tuple_complete_speculative(resultRelationDesc, slot, @@ -1145,7 +1146,8 @@ ExecInsert(ModifyTableContext *context, if (resultRelInfo->ri_NumIndices > 0) recheckIndexes = ExecInsertIndexTuples(resultRelInfo, slot, estate, false, - false, NULL, NIL); + false, NULL, NIL, + false); } } @@ -2108,11 +2110,12 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, ModifyTableState *mtstate = context->mtstate; /* insert index entries for tuple if necessary */ - if (resultRelInfo->ri_NumIndices > 0 && updateCxt->updateIndexes) + if (resultRelInfo->ri_NumIndices > 0 && updateCxt->updateIndexes != TUUI_None) recheckIndexes = ExecInsertIndexTuples(resultRelInfo, slot, context->estate, true, false, - NULL, NIL); + NULL, NIL, + updateCxt->updateIndexes == TUUI_Summarizing); /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(context->estate, resultRelInfo, diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index fe67baf142..73ad67597e 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -743,7 +743,8 @@ make_ands_implicit(Expr *clause) */ IndexInfo * makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, - List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent) + List *predicates, bool unique, bool nulls_not_distinct, + bool isready, bool concurrent, bool summarizing) { IndexInfo *n = makeNode(IndexInfo); @@ -757,6 +758,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, n->ii_CheckedUnchanged = false; n->ii_IndexUnchanged = false; n->ii_Concurrent = concurrent; + n->ii_Summarizing = summarizing; /* expressions */ n->ii_Expressions = expressions; diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 13f7987373..fe260e416a 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -2440,10 +2440,11 @@ RelationDestroyRelation(Relation relation, bool remember_tupdesc) list_free_deep(relation->rd_fkeylist); list_free(relation->rd_indexlist); list_free(relation->rd_statlist); - bms_free(relation->rd_indexattr); bms_free(relation->rd_keyattr); bms_free(relation->rd_pkattr); bms_free(relation->rd_idattr); + bms_free(relation->rd_hotblockingattr); + bms_free(relation->rd_summarizedattr); if (relation->rd_pubdesc) pfree(relation->rd_pubdesc); if (relation->rd_options) @@ -5167,10 +5168,11 @@ RelationGetIndexPredicate(Relation relation) Bitmapset * RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind) { - Bitmapset *indexattrs; /* indexed columns */ Bitmapset *uindexattrs; /* columns in unique indexes */ Bitmapset *pkindexattrs; /* columns in the primary index */ Bitmapset *idindexattrs; /* columns in the replica identity */ + Bitmapset *hotblockingattrs; /* columns with HOT blocking indexes */ + Bitmapset *summarizedattrs; /* columns with summarizing indexes */ List *indexoidlist; List *newindexoidlist; Oid relpkindex; @@ -5179,18 +5181,20 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind) MemoryContext oldcxt; /* Quick exit if we already computed the result. */ - if (relation->rd_indexattr != NULL) + if (relation->rd_attrsvalid) { switch (attrKind) { - case INDEX_ATTR_BITMAP_ALL: - return bms_copy(relation->rd_indexattr); case INDEX_ATTR_BITMAP_KEY: return bms_copy(relation->rd_keyattr); case INDEX_ATTR_BITMAP_PRIMARY_KEY: return bms_copy(relation->rd_pkattr); case INDEX_ATTR_BITMAP_IDENTITY_KEY: return bms_copy(relation->rd_idattr); + case INDEX_ATTR_BITMAP_HOT_BLOCKING: + return bms_copy(relation->rd_hotblockingattr); + case INDEX_ATTR_BITMAP_SUMMARIZED: + return bms_copy(relation->rd_summarizedattr); default: elog(ERROR, "unknown attrKind %u", attrKind); } @@ -5230,10 +5234,11 @@ restart: * CONCURRENTLY is far enough along that we should ignore the index, it * won't be returned at all by RelationGetIndexList. */ - indexattrs = NULL; uindexattrs = NULL; pkindexattrs = NULL; idindexattrs = NULL; + hotblockingattrs = NULL; + summarizedattrs = NULL; foreach(l, indexoidlist) { Oid indexOid = lfirst_oid(l); @@ -5298,8 +5303,12 @@ restart: */ if (attrnum != 0) { - indexattrs = bms_add_member(indexattrs, - attrnum - FirstLowInvalidHeapAttributeNumber); + if (indexDesc->rd_indam->amsummarizing) + summarizedattrs = bms_add_member(summarizedattrs, + attrnum - FirstLowInvalidHeapAttributeNumber); + else + hotblockingattrs = bms_add_member(hotblockingattrs, + attrnum - FirstLowInvalidHeapAttributeNumber); if (isKey && i < indexDesc->rd_index->indnkeyatts) uindexattrs = bms_add_member(uindexattrs, @@ -5316,10 +5325,17 @@ restart: } /* Collect all attributes used in expressions, too */ - pull_varattnos(indexExpressions, 1, &indexattrs); + if (indexDesc->rd_indam->amsummarizing) + pull_varattnos(indexExpressions, 1, &summarizedattrs); + else + pull_varattnos(indexExpressions, 1, &hotblockingattrs); - /* Collect all attributes in the index predicate, too */ - pull_varattnos(indexPredicate, 1, &indexattrs); + /* + * Collect all attributes in the index predicate, too. We have to ignore + * amsummarizing flag, because the row might become indexable, in which + * case we have to add it to the index. + */ + pull_varattnos(indexPredicate, 1, &hotblockingattrs); index_close(indexDesc, AccessShareLock); } @@ -5347,24 +5363,28 @@ restart: bms_free(uindexattrs); bms_free(pkindexattrs); bms_free(idindexattrs); - bms_free(indexattrs); + bms_free(hotblockingattrs); + bms_free(summarizedattrs); goto restart; } /* Don't leak the old values of these bitmaps, if any */ - bms_free(relation->rd_indexattr); - relation->rd_indexattr = NULL; + relation->rd_attrsvalid = false; bms_free(relation->rd_keyattr); relation->rd_keyattr = NULL; bms_free(relation->rd_pkattr); relation->rd_pkattr = NULL; bms_free(relation->rd_idattr); relation->rd_idattr = NULL; + bms_free(relation->rd_hotblockingattr); + relation->rd_hotblockingattr = NULL; + bms_free(relation->rd_summarizedattr); + relation->rd_summarizedattr = NULL; /* * Now save copies of the bitmaps in the relcache entry. We intentionally - * set rd_indexattr last, because that's the one that signals validity of + * set rd_attrsvalid last, because that's the one that signals validity of * the values; if we run out of memory before making that copy, we won't * leave the relcache entry looking like the other ones are valid but * empty. @@ -5373,20 +5393,24 @@ restart: relation->rd_keyattr = bms_copy(uindexattrs); relation->rd_pkattr = bms_copy(pkindexattrs); relation->rd_idattr = bms_copy(idindexattrs); - relation->rd_indexattr = bms_copy(indexattrs); + relation->rd_hotblockingattr = bms_copy(hotblockingattrs); + relation->rd_summarizedattr = bms_copy(summarizedattrs); + relation->rd_attrsvalid = true; MemoryContextSwitchTo(oldcxt); /* We return our original working copy for caller to play with */ switch (attrKind) { - case INDEX_ATTR_BITMAP_ALL: - return indexattrs; case INDEX_ATTR_BITMAP_KEY: return uindexattrs; case INDEX_ATTR_BITMAP_PRIMARY_KEY: return pkindexattrs; case INDEX_ATTR_BITMAP_IDENTITY_KEY: return idindexattrs; + case INDEX_ATTR_BITMAP_HOT_BLOCKING: + return hotblockingattrs; + case INDEX_ATTR_BITMAP_SUMMARIZED: + return summarizedattrs; default: elog(ERROR, "unknown attrKind %u", attrKind); return NULL; @@ -6307,7 +6331,7 @@ load_relcache_init_file(bool shared) rel->rd_indexlist = NIL; rel->rd_pkindex = InvalidOid; rel->rd_replidindex = InvalidOid; - rel->rd_indexattr = NULL; + rel->rd_attrsvalid = false; rel->rd_keyattr = NULL; rel->rd_pkattr = NULL; rel->rd_idattr = NULL; diff --git a/src/include/access/amapi.h b/src/include/access/amapi.h index 4f1f67b4d0..281039ef67 100644 --- a/src/include/access/amapi.h +++ b/src/include/access/amapi.h @@ -244,6 +244,8 @@ typedef struct IndexAmRoutine bool amcaninclude; /* does AM use maintenance_work_mem? */ bool amusemaintenanceworkmem; + /* does AM store tuple information only at block granularity? */ + bool amsummarizing; /* OR of parallel vacuum flags. See vacuum.h for flags. */ uint8 amparallelvacuumoptions; /* type of data stored in index, or InvalidOid if variable */ diff --git a/src/include/access/htup_details.h b/src/include/access/htup_details.h index e01f4f35c8..331eec915d 100644 --- a/src/include/access/htup_details.h +++ b/src/include/access/htup_details.h @@ -291,6 +291,16 @@ struct HeapTupleHeaderData */ #define HEAP_TUPLE_HAS_MATCH HEAP_ONLY_TUPLE /* tuple has a join match */ +/* + * HEAP_TUPLE_SUMMARIZING_UPDATED is a temporary flag used to signal that + * of the indexed columns, only columns used in summarizing indexes were + * updated. It is only used on the in-memory newly inserted updated tuple, + * which can't have been HOT updated at this point, so this should never + * pose an issue. + */ +#define HEAP_TUPLE_SUMMARIZING_UPDATED HEAP_HOT_UPDATED + + /* * HeapTupleHeader accessor macros * @@ -543,6 +553,25 @@ StaticAssertDecl(MaxOffsetNumber < SpecTokenOffsetNumber, (((tup)->t_infomask & HEAP_HASEXTERNAL) != 0) +#define HeapTupleHeaderIsHOTWithSummaryUpdate(tup) \ +( \ + ((tup)->t_infomask2 & HEAP_ONLY_TUPLE) != 0 && \ + ((tup)->t_infomask2 & HEAP_TUPLE_SUMMARIZING_UPDATED) != 0 \ +) + +#define HeapTupleHeaderSetSummaryUpdate(tup) \ +( \ + (tup)->t_infomask2 |= HEAP_TUPLE_SUMMARIZING_UPDATED \ +) + +#define HeapTupleHeaderClearSummaryUpdate(tup) \ +( \ + (tup)->t_infomask2 &= ~HEAP_TUPLE_SUMMARIZING_UPDATED \ +) + + + + /* * BITMAPLEN(NATTS) - * Computes size of null bitmap given number of data columns. diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index 652e96f1b0..db444abb35 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -102,6 +102,19 @@ typedef enum TM_Result TM_WouldBlock } TM_Result; +/* + * Result codes for table_update(..., update_indexes*..). + * Used to determine which indexes to update. + */ +typedef enum TU_UpdateIndexes { + /* No indexed columns were updated (incl. TID addressing of tuple) */ + TUUI_None = 0, + /* A non-summarizing indexed column was updated, or the TID has changed */ + TUUI_All = 1, + /* Only summarized columns were updated, TID is unchanged */ + TUUI_Summarizing = 2 +} TU_UpdateIndexes; + /* * When table_tuple_update, table_tuple_delete, or table_tuple_lock fail * because the target tuple is already outdated, they fill in this struct to @@ -526,7 +539,7 @@ typedef struct TableAmRoutine bool wait, TM_FailureData *tmfd, LockTupleMode *lockmode, - bool *update_indexes); + TU_UpdateIndexes *update_indexes); /* see table_tuple_lock() for reference about parameters */ TM_Result (*tuple_lock) (Relation rel, @@ -1514,7 +1527,7 @@ static inline TM_Result table_tuple_update(Relation rel, ItemPointer otid, TupleTableSlot *slot, CommandId cid, Snapshot snapshot, Snapshot crosscheck, bool wait, TM_FailureData *tmfd, LockTupleMode *lockmode, - bool *update_indexes) + TU_UpdateIndexes *update_indexes) { return rel->rd_tableam->tuple_update(rel, otid, slot, cid, snapshot, crosscheck, @@ -2038,7 +2051,7 @@ extern void simple_table_tuple_delete(Relation rel, ItemPointer tid, Snapshot snapshot); extern void simple_table_tuple_update(Relation rel, ItemPointer otid, TupleTableSlot *slot, Snapshot snapshot, - bool *update_indexes); + TU_UpdateIndexes *update_indexes); /* ---------------------------------------------------------------------------- diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index e7e25c057e..551c5d7ae0 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -620,7 +620,8 @@ extern List *ExecInsertIndexTuples(ResultRelInfo *resultRelInfo, TupleTableSlot *slot, EState *estate, bool update, bool noDupErr, - bool *specConflict, List *arbiterIndexes); + bool *specConflict, List *arbiterIndexes, + bool onlySummarizing); extern bool ExecCheckIndexConstraints(ResultRelInfo *resultRelInfo, TupleTableSlot *slot, EState *estate, ItemPointer conflictTid, diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 20f4c8b35f..3f1b8818a1 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -161,6 +161,7 @@ typedef struct ExprState * IndexUnchanged aminsert hint, cached for retail inserts * Concurrent are we doing a concurrent index build? * BrokenHotChain did we detect any broken HOT chains? + * Summarizing is it summarizing? * ParallelWorkers # of workers requested (excludes leader) * Am Oid of index AM * AmCache private cache area for index AM @@ -194,6 +195,7 @@ typedef struct IndexInfo bool ii_IndexUnchanged; bool ii_Concurrent; bool ii_BrokenHotChain; + bool ii_Summarizing; int ii_ParallelWorkers; Oid ii_Am; void *ii_AmCache; diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index 80f1d5336b..64651c9b00 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -96,7 +96,9 @@ extern List *make_ands_implicit(Expr *clause); extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, List *predicates, - bool unique, bool nulls_not_distinct, bool isready, bool concurrent); + bool unique, bool nulls_not_distinct, + bool isready, bool concurrent, + bool summarizing); extern DefElem *makeDefElem(char *name, Node *arg, int location); extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg, diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 67f994cb3e..2294cb1a6a 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -156,10 +156,12 @@ typedef struct RelationData List *rd_statlist; /* list of OIDs of extended stats */ /* data managed by RelationGetIndexAttrBitmap: */ - Bitmapset *rd_indexattr; /* identifies columns used in indexes */ + bool rd_attrsvalid; /* are bitmaps of attrs valid? */ Bitmapset *rd_keyattr; /* cols that can be ref'd by foreign keys */ Bitmapset *rd_pkattr; /* cols included in primary key */ Bitmapset *rd_idattr; /* included in replica identity index */ + Bitmapset *rd_hotblockingattr; /* cols blocking HOT update */ + Bitmapset *rd_summarizedattr; /* cols indexed by block-or-larger summarizing indexes */ PublicationDesc *rd_pubdesc; /* publication descriptor, or NULL */ diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h index 88460f21c5..beeb28b83c 100644 --- a/src/include/utils/relcache.h +++ b/src/include/utils/relcache.h @@ -56,10 +56,11 @@ extern bytea **RelationGetIndexAttOptions(Relation relation, bool copy); typedef enum IndexAttrBitmapKind { - INDEX_ATTR_BITMAP_ALL, INDEX_ATTR_BITMAP_KEY, INDEX_ATTR_BITMAP_PRIMARY_KEY, - INDEX_ATTR_BITMAP_IDENTITY_KEY + INDEX_ATTR_BITMAP_IDENTITY_KEY, + INDEX_ATTR_BITMAP_HOT_BLOCKING, + INDEX_ATTR_BITMAP_SUMMARIZED } IndexAttrBitmapKind; extern Bitmapset *RelationGetIndexAttrBitmap(Relation relation, diff --git a/src/test/modules/dummy_index_am/dummy_index_am.c b/src/test/modules/dummy_index_am/dummy_index_am.c index dfb1ebb846..c14e0abe0c 100644 --- a/src/test/modules/dummy_index_am/dummy_index_am.c +++ b/src/test/modules/dummy_index_am/dummy_index_am.c @@ -296,6 +296,7 @@ dihandler(PG_FUNCTION_ARGS) amroutine->amcanparallel = false; amroutine->amcaninclude = false; amroutine->amusemaintenanceworkmem = false; + amroutine->amsummarizing = false; amroutine->amparallelvacuumoptions = VACUUM_OPTION_NO_PARALLEL; amroutine->amkeytype = InvalidOid; diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 937b2101b3..728c474cfa 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -1354,4 +1354,114 @@ SELECT :io_stats_post_reset < :io_stats_pre_reset; t (1 row) +-- test BRIN index doesn't block HOT update +CREATE TABLE brin_hot ( + id integer PRIMARY KEY, + val integer NOT NULL +) WITH (autovacuum_enabled = off, fillfactor = 70); +INSERT INTO brin_hot SELECT *, 0 FROM generate_series(1, 235); +CREATE INDEX val_brin ON brin_hot using brin(val); +CREATE FUNCTION wait_for_hot_stats() RETURNS void AS $$ +DECLARE + start_time timestamptz := clock_timestamp(); + updated bool; +BEGIN + -- we don't want to wait forever; loop will exit after 30 seconds + FOR i IN 1 .. 300 LOOP + SELECT (pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid) > 0) INTO updated; + EXIT WHEN updated; + + -- wait a little + PERFORM pg_sleep_for('100 milliseconds'); + -- reset stats snapshot so we can test again + PERFORM pg_stat_clear_snapshot(); + END LOOP; + -- report time waited in postmaster log (where it won't change test output) + RAISE log 'wait_for_hot_stats delayed % seconds', + EXTRACT(epoch FROM clock_timestamp() - start_time); +END +$$ LANGUAGE plpgsql; +UPDATE brin_hot SET val = -3 WHERE id = 42; +-- We can't just call wait_for_hot_stats() at this point, because we only +-- transmit stats when the session goes idle, and we probably didn't +-- transmit the last couple of counts yet thanks to the rate-limiting logic +-- in pgstat_report_stat(). But instead of waiting for the rate limiter's +-- timeout to elapse, let's just start a new session. The old one will +-- then send its stats before dying. +\c - +SELECT wait_for_hot_stats(); + wait_for_hot_stats +-------------------- + +(1 row) + +SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid); + pg_stat_get_tuples_hot_updated +-------------------------------- + 1 +(1 row) + +DROP TABLE brin_hot; +DROP FUNCTION wait_for_hot_stats(); +-- Test handling of index predicates - updating attributes in precicates +-- should block HOT even for BRIN. We update a row that was not indexed +-- due to the index predicate, and becomes indexable. +CREATE TABLE brin_hot_2 (a int, b int); +INSERT INTO brin_hot_2 VALUES (1, 100); +CREATE INDEX ON brin_hot_2 USING brin (b) WHERE a = 2; +UPDATE brin_hot_2 SET a = 2; +EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_2 WHERE a = 2 AND b = 100; + QUERY PLAN +----------------------------------- + Seq Scan on brin_hot_2 + Filter: ((a = 2) AND (b = 100)) +(2 rows) + +SELECT COUNT(*) FROM brin_hot_2 WHERE a = 2 AND b = 100; + count +------- + 1 +(1 row) + +SET enable_seqscan = off; +EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_2 WHERE a = 2 AND b = 100; + QUERY PLAN +--------------------------------------------- + Bitmap Heap Scan on brin_hot_2 + Recheck Cond: ((b = 100) AND (a = 2)) + -> Bitmap Index Scan on brin_hot_2_b_idx + Index Cond: (b = 100) +(4 rows) + +SELECT COUNT(*) FROM brin_hot_2 WHERE a = 2 AND b = 100; + count +------- + 1 +(1 row) + +DROP TABLE brin_hot_2; +-- Test that updates to indexed columns are still propagated to the +-- BRIN column. +-- https://postgr.es/m/05ebcb44-f383-86e3-4f31-0a97a55634cf@enterprisedb.com +CREATE TABLE brin_hot_3 (a int, filler text) WITH (fillfactor = 10); +INSERT INTO brin_hot_3 SELECT 1, repeat(' ', 500) FROM generate_series(1, 20); +CREATE INDEX ON brin_hot_3 USING brin (a) WITH (pages_per_range = 1); +UPDATE brin_hot_3 SET a = 2; +EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_3 WHERE a = 2; + QUERY PLAN +--------------------------------------------- + Bitmap Heap Scan on brin_hot_3 + Recheck Cond: (a = 2) + -> Bitmap Index Scan on brin_hot_3_a_idx + Index Cond: (a = 2) +(4 rows) + +SELECT COUNT(*) FROM brin_hot_3 WHERE a = 2; + count +------- + 20 +(1 row) + +DROP TABLE brin_hot_3; +SET enable_seqscan = on; -- End of Stats Test diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 74e592aa8a..e113f6906c 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -535,7 +535,6 @@ SET enable_seqscan TO on; SELECT pg_stat_get_replication_slot(NULL); SELECT pg_stat_get_subscription_stats(NULL); - -- Test that the following operations are tracked in pg_stat_io: -- - reads of target blocks into shared buffers -- - writes of shared buffers to permanent storage @@ -678,4 +677,85 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + FROM pg_stat_io \gset SELECT :io_stats_post_reset < :io_stats_pre_reset; + +-- test BRIN index doesn't block HOT update +CREATE TABLE brin_hot ( + id integer PRIMARY KEY, + val integer NOT NULL +) WITH (autovacuum_enabled = off, fillfactor = 70); + +INSERT INTO brin_hot SELECT *, 0 FROM generate_series(1, 235); +CREATE INDEX val_brin ON brin_hot using brin(val); + +CREATE FUNCTION wait_for_hot_stats() RETURNS void AS $$ +DECLARE + start_time timestamptz := clock_timestamp(); + updated bool; +BEGIN + -- we don't want to wait forever; loop will exit after 30 seconds + FOR i IN 1 .. 300 LOOP + SELECT (pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid) > 0) INTO updated; + EXIT WHEN updated; + + -- wait a little + PERFORM pg_sleep_for('100 milliseconds'); + -- reset stats snapshot so we can test again + PERFORM pg_stat_clear_snapshot(); + END LOOP; + -- report time waited in postmaster log (where it won't change test output) + RAISE log 'wait_for_hot_stats delayed % seconds', + EXTRACT(epoch FROM clock_timestamp() - start_time); +END +$$ LANGUAGE plpgsql; + +UPDATE brin_hot SET val = -3 WHERE id = 42; + +-- We can't just call wait_for_hot_stats() at this point, because we only +-- transmit stats when the session goes idle, and we probably didn't +-- transmit the last couple of counts yet thanks to the rate-limiting logic +-- in pgstat_report_stat(). But instead of waiting for the rate limiter's +-- timeout to elapse, let's just start a new session. The old one will +-- then send its stats before dying. +\c - + +SELECT wait_for_hot_stats(); +SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid); + +DROP TABLE brin_hot; +DROP FUNCTION wait_for_hot_stats(); + +-- Test handling of index predicates - updating attributes in precicates +-- should block HOT even for BRIN. We update a row that was not indexed +-- due to the index predicate, and becomes indexable. +CREATE TABLE brin_hot_2 (a int, b int); +INSERT INTO brin_hot_2 VALUES (1, 100); +CREATE INDEX ON brin_hot_2 USING brin (b) WHERE a = 2; + +UPDATE brin_hot_2 SET a = 2; + +EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_2 WHERE a = 2 AND b = 100; +SELECT COUNT(*) FROM brin_hot_2 WHERE a = 2 AND b = 100; + +SET enable_seqscan = off; + +EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_2 WHERE a = 2 AND b = 100; +SELECT COUNT(*) FROM brin_hot_2 WHERE a = 2 AND b = 100; + +DROP TABLE brin_hot_2; + +-- Test that updates to indexed columns are still propagated to the +-- BRIN column. +-- https://postgr.es/m/05ebcb44-f383-86e3-4f31-0a97a55634cf@enterprisedb.com +CREATE TABLE brin_hot_3 (a int, filler text) WITH (fillfactor = 10); +INSERT INTO brin_hot_3 SELECT 1, repeat(' ', 500) FROM generate_series(1, 20); +CREATE INDEX ON brin_hot_3 USING brin (a) WITH (pages_per_range = 1); +UPDATE brin_hot_3 SET a = 2; + +EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_3 WHERE a = 2; +SELECT COUNT(*) FROM brin_hot_3 WHERE a = 2; + +DROP TABLE brin_hot_3; + +SET enable_seqscan = on; + -- End of Stats Test -- 2.33.0.windows.2