From 5731abccd65e002c3a3ad47a40d982b7eb3bd209 Mon Sep 17 00:00:00 2001 From: Gregory Burd Date: Mon, 27 Jan 2025 13:28:59 -0500 Subject: [PATCH v4] Expand HOT update path to include expression and partial indexes. This patch extends the cases where HOT updates are possible in the heapam by examining expression indexes and determining if indexed values where mutated or not. Previously, any expression index on a column would disqualify it from the HOT update path. Also examines partial indexes to see if the values are within the predicate or not. This is a modified application of a patch proposed on the pgsql-hackers list: https://www.postgresql.org/message-id/flat/4d9928ee-a9e6-15f9-9c82-5981f13ffca6%40postgrespro.ru applied: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c203d6cf8 reverted: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=05f84605dbeb9cf8279a157234b24bbb706c5256 Signed-off-by: Greg Burd --- doc/src/sgml/config.sgml | 16 + src/backend/access/heap/heapam.c | 82 ++--- src/backend/access/heap/heapam_handler.c | 27 +- src/backend/access/table/tableam.c | 5 +- src/backend/catalog/index.c | 15 + src/backend/catalog/indexing.c | 51 +-- src/backend/executor/execIndexing.c | 239 ++++++++++++- src/backend/executor/execReplication.c | 10 +- src/backend/executor/nodeModifyTable.c | 13 +- src/backend/utils/cache/relcache.c | 34 +- src/backend/utils/misc/guc_tables.c | 13 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/heapam.h | 5 +- src/include/access/tableam.h | 28 +- src/include/executor/executor.h | 8 +- src/include/nodes/execnodes.h | 8 + src/include/optimizer/optimizer.h | 1 + src/include/utils/rel.h | 5 +- src/include/utils/relcache.h | 1 + .../regress/expected/heap_hot_updates.out | 318 ++++++++++++++++++ src/test/regress/expected/sysviews.out | 3 +- src/test/regress/parallel_schedule | 5 + src/test/regress/sql/heap_hot_updates.sql | 208 ++++++++++++ 23 files changed, 921 insertions(+), 175 deletions(-) create mode 100644 src/test/regress/expected/heap_hot_updates.out create mode 100644 src/test/regress/sql/heap_hot_updates.sql diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index a782f10998..5b756e7754 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5242,6 +5242,22 @@ ANY num_sync ( + enable_expression_checks (boolean) + + enable_expression_checks configuration parameter + + + + + Enables or disables the ability to determine if indexes with + expressions partial indexes have changed allowing for the heap-only + tuple (HOT) optimzation on update when using the heap access method. + The default is on. + + + + enable_gathermerge (boolean) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index ea0a12b39a..0124ba4e5b 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -3164,12 +3164,11 @@ TM_Result heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, CommandId cid, Snapshot crosscheck, bool wait, TM_FailureData *tmfd, LockTupleMode *lockmode, - TU_UpdateIndexes *update_indexes) + Bitmapset **modified_indexes, struct EState *estate) { TM_Result result; TransactionId xid = GetCurrentTransactionId(); Bitmapset *hot_attrs; - Bitmapset *sum_attrs; Bitmapset *key_attrs; Bitmapset *id_attrs; Bitmapset *interesting_attrs; @@ -3192,7 +3191,6 @@ 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; @@ -3244,14 +3242,11 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, */ 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); @@ -3307,10 +3302,8 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, tmfd->ctid = *otid; tmfd->xmax = InvalidTransactionId; tmfd->cmax = InvalidCommandId; - *update_indexes = TU_None; bms_free(hot_attrs); - bms_free(sum_attrs); bms_free(key_attrs); bms_free(id_attrs); /* modified_attrs not yet initialized */ @@ -3608,10 +3601,8 @@ l2: UnlockTupleTuplock(relation, &(oldtup.t_self), *lockmode); if (vmbuffer != InvalidBuffer) ReleaseBuffer(vmbuffer); - *update_indexes = TU_None; bms_free(hot_attrs); - bms_free(sum_attrs); bms_free(key_attrs); bms_free(id_attrs); bms_free(modified_attrs); @@ -3926,30 +3917,57 @@ l2: * one pin is held. */ - if (newbuf == buffer) + if (modified_indexes && newbuf == buffer) { + bool only_summarizing = false; + int num_indexes = list_length(relation->rd_indexinfolist); + /* * Since the new tuple is going into the same page, we might be able - * to do a HOT update. Check if any of the index columns have been - * changed. + * to do a HOT update. */ if (!bms_overlap(modified_attrs, hot_attrs)) { + /* + * If no attributes were modified, we can be on the HOT path but + * we need to signal with a non-NULL modified_indexes bitmap that + * none of the indexes need to be updated, so add a non-existent + * index which won't match later when creating index entries. + */ + *modified_indexes = bms_add_member(*modified_indexes, num_indexes); use_hot_update = true; - + } + else + { /* - * If none of the columns that are used in hot-blocking indexes - * were updated, we can apply HOT, but we do still need to check - * if we need to update the summarizing indexes, and update those - * indexes if the columns were updated, or we may fail to detect - * e.g. value bound changes in BRIN minmax indexes. + * Some of the columns used in HOT-blocking indexes were updated, + * but there are still cases where we can do a HOT update. Check + * if the columns that are used in the HOT-blocking indexes were + * updated, and if not, we can do a HOT update. */ - if (bms_overlap(modified_attrs, sum_attrs)) - summarized_update = true; + *modified_indexes = + ExecIndexesRequiringUpdates(relation, modified_attrs, + estate, &oldtup, newtup, + &only_summarizing); + if (bms_is_empty(*modified_indexes) || only_summarizing) + { + /* See earlier comment... */ + *modified_indexes = bms_add_member(*modified_indexes, num_indexes); + use_hot_update = true; + } + else + { + bms_free(*modified_indexes); + *modified_indexes = NULL; + } } } else { + /* Signal that we're not on the HOT path by setting this to NULL */ + if (modified_indexes) + *modified_indexes = NULL; + /* Set a hint that the old page could use prune/defrag */ PageSetFull(page); } @@ -4106,27 +4124,10 @@ l2: heap_freetuple(heaptup); } - /* - * If it is a HOT update, the update may still need to update summarized - * indexes, lest we fail to update those summaries and get incorrect - * results (for example, minmax bounds of the block may change with this - * update). - */ - if (use_hot_update) - { - if (summarized_update) - *update_indexes = TU_Summarizing; - else - *update_indexes = TU_None; - } - else - *update_indexes = TU_All; - 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); @@ -4403,8 +4404,7 @@ HeapDetermineColumnsInfo(Relation relation, * via ereport(). */ void -simple_heap_update(Relation relation, ItemPointer otid, HeapTuple tup, - TU_UpdateIndexes *update_indexes) +simple_heap_update(Relation relation, ItemPointer otid, HeapTuple tup) { TM_Result result; TM_FailureData tmfd; @@ -4413,7 +4413,7 @@ simple_heap_update(Relation relation, ItemPointer otid, HeapTuple tup, result = heap_update(relation, otid, tup, GetCurrentCommandId(true), InvalidSnapshot, true /* wait for commit */ , - &tmfd, &lockmode, update_indexes); + &tmfd, &lockmode, NULL, NULL); switch (result) { case TM_SelfModified: diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index a4003cf59e..e2ac9f486e 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -312,8 +312,8 @@ heapam_tuple_delete(Relation relation, ItemPointer tid, CommandId cid, 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, TU_UpdateIndexes *update_indexes) + bool wait, TM_FailureData *tmfd, LockTupleMode *lockmode, + Bitmapset **modified_indexes, EState *estate) { bool shouldFree = true; HeapTuple tuple = ExecFetchSlotHeapTuple(slot, true, &shouldFree); @@ -324,30 +324,9 @@ heapam_tuple_update(Relation relation, ItemPointer otid, TupleTableSlot *slot, tuple->t_tableOid = slot->tts_tableOid; result = heap_update(relation, otid, tuple, cid, crosscheck, wait, - tmfd, lockmode, update_indexes); + tmfd, lockmode, modified_indexes, estate); ItemPointerCopy(&tuple->t_self, &slot->tts_tid); - /* - * Decide whether new index entries are needed for the tuple - * - * Note: heap_update returns the tid (location) of the new tuple in the - * t_self field. - * - * If the update is not HOT, we must update all indexes. If the update is - * HOT, it could be that we updated summarized columns, so we either - * update only summarized indexes, or none at all. - */ - if (result != TM_Ok) - { - Assert(*update_indexes == TU_None); - *update_indexes = TU_None; - } - else if (!HeapTupleIsHeapOnly(tuple)) - Assert(*update_indexes == TU_All); - else - Assert((*update_indexes == TU_Summarizing) || - (*update_indexes == TU_None)); - if (shouldFree) pfree(tuple); diff --git a/src/backend/access/table/tableam.c b/src/backend/access/table/tableam.c index e18a8f8250..2b5fe33e43 100644 --- a/src/backend/access/table/tableam.c +++ b/src/backend/access/table/tableam.c @@ -334,8 +334,7 @@ simple_table_tuple_delete(Relation rel, ItemPointer tid, Snapshot snapshot) void simple_table_tuple_update(Relation rel, ItemPointer otid, TupleTableSlot *slot, - Snapshot snapshot, - TU_UpdateIndexes *update_indexes) + Snapshot snapshot) { TM_Result result; TM_FailureData tmfd; @@ -345,7 +344,7 @@ simple_table_tuple_update(Relation rel, ItemPointer otid, GetCurrentCommandId(true), snapshot, InvalidSnapshot, true /* wait for commit */ , - &tmfd, &lockmode, update_indexes); + &tmfd, &lockmode, NULL, NULL); switch (result) { diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 7377912b41..b0e670c1f7 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -2455,7 +2455,20 @@ BuildIndexInfo(Relation index) /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) + { ii->ii_IndexAttrNumbers[i] = indexStruct->indkey.values[i]; + ii->ii_IndexAttrs = + bms_add_member(ii->ii_IndexAttrs, + indexStruct->indkey.values[i] - FirstLowInvalidHeapAttributeNumber); + } + + /* collect attributes used in the expression, if one is present */ + if (ii->ii_Expressions) + pull_varattnos((Node *) ii->ii_Expressions, 1, &ii->ii_ExpressionAttrs); + + /* collect attributes used in the predicate, if one is present */ + if (ii->ii_Predicate) + pull_varattnos((Node *) ii->ii_Predicate, 1, &ii->ii_PredicateAttrs); /* fetch exclusion constraint info if any */ if (indexStruct->indisexclusion) @@ -2466,6 +2479,8 @@ BuildIndexInfo(Relation index) &ii->ii_ExclusionStrats); } + ii->ii_OpClassDataTypes = index->rd_opcintype; + return ii; } diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c index 25c4b6bdc8..3fdebba3f0 100644 --- a/src/backend/catalog/indexing.c +++ b/src/backend/catalog/indexing.c @@ -72,8 +72,7 @@ CatalogCloseIndexes(CatalogIndexState indstate) * This is effectively a cut-down version of ExecInsertIndexTuples. */ static void -CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple, - TU_UpdateIndexes updateIndexes) +CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple) { int i; int numIndexes; @@ -83,20 +82,9 @@ CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple, IndexInfo **indexInfoArray; Datum values[INDEX_MAX_KEYS]; bool isnull[INDEX_MAX_KEYS]; - bool onlySummarized = (updateIndexes == TU_Summarizing); - /* - * 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. - */ -#ifndef USE_ASSERT_CHECKING - if (HeapTupleIsHeapOnly(heapTuple) && !onlySummarized) - return; -#endif - - /* When only updating summarized indexes, the tuple has to be HOT. */ - Assert((!onlySummarized) || HeapTupleIsHeapOnly(heapTuple)); + /* The tuple never be HOT. */ + Assert(!HeapTupleIsHeapOnly(heapTuple)); /* * Get information from the state structure. Fall out if nothing to do. @@ -138,22 +126,6 @@ CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple, Assert(index->rd_index->indimmediate); Assert(indexInfo->ii_NumIndexKeyAttrs != 0); - /* see earlier check above */ -#ifdef USE_ASSERT_CHECKING - 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. @@ -240,7 +212,7 @@ CatalogTupleInsert(Relation heapRel, HeapTuple tup) simple_heap_insert(heapRel, tup); - CatalogIndexInsert(indstate, tup, TU_All); + CatalogIndexInsert(indstate, tup); CatalogCloseIndexes(indstate); } @@ -260,7 +232,7 @@ CatalogTupleInsertWithInfo(Relation heapRel, HeapTuple tup, simple_heap_insert(heapRel, tup); - CatalogIndexInsert(indstate, tup, TU_All); + CatalogIndexInsert(indstate, tup); } /* @@ -291,7 +263,7 @@ CatalogTuplesMultiInsertWithInfo(Relation heapRel, TupleTableSlot **slot, tuple = ExecFetchSlotHeapTuple(slot[i], true, &should_free); tuple->t_tableOid = slot[i]->tts_tableOid; - CatalogIndexInsert(indstate, tuple, TU_All); + CatalogIndexInsert(indstate, tuple); if (should_free) heap_freetuple(tuple); @@ -313,15 +285,14 @@ void CatalogTupleUpdate(Relation heapRel, ItemPointer otid, HeapTuple tup) { CatalogIndexState indstate; - TU_UpdateIndexes updateIndexes = TU_All; CatalogTupleCheckConstraints(heapRel, tup); indstate = CatalogOpenIndexes(heapRel); - simple_heap_update(heapRel, otid, tup, &updateIndexes); + simple_heap_update(heapRel, otid, tup); - CatalogIndexInsert(indstate, tup, updateIndexes); + CatalogIndexInsert(indstate, tup); CatalogCloseIndexes(indstate); } @@ -337,13 +308,11 @@ void CatalogTupleUpdateWithInfo(Relation heapRel, ItemPointer otid, HeapTuple tup, CatalogIndexState indstate) { - TU_UpdateIndexes updateIndexes = TU_All; - CatalogTupleCheckConstraints(heapRel, tup); - simple_heap_update(heapRel, otid, tup, &updateIndexes); + simple_heap_update(heapRel, otid, tup); - CatalogIndexInsert(indstate, tup, updateIndexes); + CatalogIndexInsert(indstate, tup); } /* diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c index 7c87f012c3..3cbbf54970 100644 --- a/src/backend/executor/execIndexing.c +++ b/src/backend/executor/execIndexing.c @@ -117,6 +117,8 @@ #include "utils/multirangetypes.h" #include "utils/rangetypes.h" #include "utils/snapmgr.h" +#include "utils/datum.h" +#include "utils/lsyscache.h" /* waitMode argument to check_exclusion_or_unique_constraint() */ typedef enum @@ -146,6 +148,11 @@ static bool index_expression_changed_walker(Node *node, static void ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval, char typtype, Oid atttypid); +/* + * GUC parameters + */ +bool enable_expression_checks = true; + /* ---------------------------------------------------------------- * ExecOpenIndices * @@ -168,6 +175,7 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative) IndexInfo **indexInfoArray; resultRelInfo->ri_NumIndices = 0; + resultRelation->rd_indexinfolist = NIL; /* fast path if no indexes */ if (!RelationGetForm(resultRelation)->relhasindex) @@ -210,6 +218,10 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative) /* extract index key information from the index's pg_index info */ ii = BuildIndexInfo(indexDesc); + /* used when determining if indexed values changed during update */ + resultRelation->rd_indexinfolist = + lappend(resultRelation->rd_indexinfolist, ii); + /* * If the indexes are to be used for speculative insertion or conflict * detection in logical replication, add extra information required by @@ -307,7 +319,7 @@ ExecInsertIndexTuples(ResultRelInfo *resultRelInfo, bool noDupErr, bool *specConflict, List *arbiterIndexes, - bool onlySummarizing) + Bitmapset *modified_indexes) { ItemPointer tupleid = &slot->tts_tid; List *result = NIL; @@ -364,10 +376,12 @@ ExecInsertIndexTuples(ResultRelInfo *resultRelInfo, continue; /* - * Skip processing of non-summarizing indexes if we only update - * summarizing indexes + * If modified_indexes is NULL, we're not in a HOT update, so we + * should update all indexes. If it's not NULL, we should only update + * the listed indexes. This list will include any summarizing indexes + * that require updates on the HOT path as well. */ - if (onlySummarizing && !indexInfo->ii_Summarizing) + if (update && modified_indexes && !bms_is_member(i, modified_indexes)) continue; /* Check for partial index */ @@ -1089,6 +1103,9 @@ index_unchanged_by_update(ResultRelInfo *resultRelInfo, EState *estate, if (hasexpression) { + if (indexInfo->ii_IndexUnchanged) + return true; + indexInfo->ii_IndexUnchanged = false; return false; } @@ -1166,3 +1183,217 @@ ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval, char t errmsg("empty WITHOUT OVERLAPS value found in column \"%s\" in relation \"%s\"", NameStr(attname), RelationGetRelationName(rel)))); } + +/* + * Determine which indexes must be invoked during ExecIndexInsertTuple(). + * + * That will include: any index on a column where there is an attribute that was + * modified, any expression index where the expression's value updated, any + * index used in a constraint, and any summarizing index. + */ +Bitmapset * +ExecIndexesRequiringUpdates(Relation relation, + Bitmapset *modified_attrs, + EState *estate, + HeapTuple old_tuple, + HeapTuple new_tuple, + bool *only_summarizing) +{ + ListCell *lc; + List *indexinfolist = relation->rd_indexinfolist; + ExprContext *econtext = NULL; + int num_summarizing = 0; + TupleDesc tupledesc; + TupleTableSlot *old_tts, + *new_tts; + Bitmapset *result = NULL; + + /* + * Examine each index on this relation relative to the changes between old + * and new tuples. + */ + foreach(lc, indexinfolist) + { + IndexInfo *indexInfo = (IndexInfo *) lfirst(lc); + + /* + * Summarizing indexes don't prevent HOT updates, but do require that + * they are updated so we include it in the set. + */ + if (indexInfo->ii_Summarizing) + { + num_summarizing++; + result = bms_add_member(result, foreach_current_index(lc)); + continue; + } + + /* + * If this is a partial index it has a predicate, evaluate the + * expression to determine if we need to include it or not. + */ + if (enable_expression_checks && estate != NULL && + bms_overlap(indexInfo->ii_PredicateAttrs, modified_attrs)) + { + ExprState *pstate; + bool old_tuple_qualifies, + new_tuple_qualifies; + + /* Create these once, only if necessary, then reuse them. */ + if (!econtext) + { + econtext = GetPerTupleExprContext(estate); + tupledesc = RelationGetDescr(relation); + old_tts = MakeSingleTupleTableSlot(tupledesc, + &TTSOpsHeapTuple); + new_tts = MakeSingleTupleTableSlot(tupledesc, + &TTSOpsHeapTuple); + + ExecStoreHeapTuple(old_tuple, old_tts, InvalidBuffer); + ExecStoreHeapTuple(new_tuple, new_tts, InvalidBuffer); + } + + pstate = ExecPrepareQual(indexInfo->ii_Predicate, estate); + + /* + * Here the term "qualifies" means "satisfies the predicate + * condition of the partial index". + */ + econtext->ecxt_scantuple = old_tts; + old_tuple_qualifies = ExecQual(pstate, econtext); + + econtext->ecxt_scantuple = new_tts; + new_tuple_qualifies = ExecQual(pstate, econtext); + + /* + * If neither the old nor the new tuples satisfy the predicate we + * can be sure that this index doesn't need updating, continue to + * the next index. + */ + if ((new_tuple_qualifies == false) && (old_tuple_qualifies == false)) + continue; + + /* + * If there is a transition between indexed and not indexed, + * that's enough to require that this index is updated. + */ + if (new_tuple_qualifies != old_tuple_qualifies) + { + result = bms_add_member(result, foreach_current_index(lc)); + continue; + } + + /* + * Otherwise the old and new values exist in the index, but did + * they get updated? We don't yet know, so proceed with the next + * statement in the loop to find out. + */ + } + + + /* + * Indexes with expressions may or may not have changed, it is + * impossible to know without exercising their expression and + * reviewing index tuple state for changes. This is a lot of work, + * but because all indexes on JSONB columns fall into this category it + * can be worth it to avoid index updates and remain on the HOT update + * path when possible. + */ + if (bms_overlap(indexInfo->ii_ExpressionAttrs, modified_attrs)) + { + Datum old_values[INDEX_MAX_KEYS]; + bool old_isnull[INDEX_MAX_KEYS]; + Datum new_values[INDEX_MAX_KEYS]; + bool new_isnull[INDEX_MAX_KEYS]; + bool changed = false; + + /* + * Assume the index is changed when we don't have an estate + * context to use or the GUC is disabled. + */ + if (!enable_expression_checks || estate == NULL) + { + result = bms_add_member(result, foreach_current_index(lc)); + continue; + } + + /* Create these once, only if necessary, then reuse them. */ + if (!econtext) + { + econtext = GetPerTupleExprContext(estate); + tupledesc = RelationGetDescr(relation); + old_tts = MakeSingleTupleTableSlot(tupledesc, + &TTSOpsHeapTuple); + new_tts = MakeSingleTupleTableSlot(tupledesc, + &TTSOpsHeapTuple); + + ExecStoreHeapTuple(old_tuple, old_tts, InvalidBuffer); + ExecStoreHeapTuple(new_tuple, new_tts, InvalidBuffer); + } + + indexInfo->ii_ExpressionsState = NIL; + + econtext->ecxt_scantuple = old_tts; + FormIndexDatum(indexInfo, + old_tts, + estate, + old_values, + old_isnull); + + econtext->ecxt_scantuple = new_tts; + FormIndexDatum(indexInfo, + new_tts, + estate, + new_values, + new_isnull); + + for (int i = 0; i < indexInfo->ii_NumIndexKeyAttrs; i++) + { + if (old_isnull[i] != new_isnull[i]) + { + changed = true; + break; + } + else if (!old_isnull[i]) + { + int16 elmlen; + bool elmbyval; + Oid opcintyp = indexInfo->ii_OpClassDataTypes[i]; + + get_typlenbyval(opcintyp, &elmlen, &elmbyval); + if (!datum_image_eq(old_values[i], new_values[i], + elmbyval, elmlen)) + { + changed = true; + break; + } + } + } + + if (changed) + result = bms_add_member(result, foreach_current_index(lc)); + + /* Shortcut index_unchanged_by_update(), we know the answer. */ + indexInfo->ii_CheckedUnchanged = true; + indexInfo->ii_IndexUnchanged = !changed; + continue; + } + + /* + * If the index references modified attributes then it needs to be + * updated. + */ + if (bms_overlap(indexInfo->ii_IndexAttrs, modified_attrs)) + result = bms_add_member(result, foreach_current_index(lc)); + } + + if (econtext) + { + ExecDropSingleTupleTableSlot(old_tts); + ExecDropSingleTupleTableSlot(new_tts); + } + + *only_summarizing = (list_length(indexinfolist) > 0 && + num_summarizing == bms_num_members(result)); + + return result; +} diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c index 3985e84d3a..bbafb4fe0c 100644 --- a/src/backend/executor/execReplication.c +++ b/src/backend/executor/execReplication.c @@ -671,9 +671,9 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo, if (!skip_tuple) { List *recheckIndexes = NIL; - TU_UpdateIndexes update_indexes; List *conflictindexes; bool conflict = false; + Bitmapset *modified_indexes = NULL; /* Compute stored generated columns */ if (rel->rd_att->constr && @@ -687,17 +687,16 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo, if (rel->rd_rel->relispartition) ExecPartitionCheck(resultRelInfo, slot, estate, true); - simple_table_tuple_update(rel, tid, slot, estate->es_snapshot, - &update_indexes); + simple_table_tuple_update(rel, tid, slot, estate->es_snapshot); conflictindexes = resultRelInfo->ri_onConflictArbiterIndexes; - if (resultRelInfo->ri_NumIndices > 0 && (update_indexes != TU_None)) + if (resultRelInfo->ri_NumIndices > 0) recheckIndexes = ExecInsertIndexTuples(resultRelInfo, slot, estate, true, conflictindexes ? true : false, &conflict, conflictindexes, - (update_indexes == TU_Summarizing)); + &modified_indexes); /* * Refer to the comments above the call to CheckAndReportConflict() in @@ -715,6 +714,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo, recheckIndexes, NULL, false); list_free(recheckIndexes); + bms_free(modified_indexes); } } diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index bc82e035ba..f99bf66177 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -120,8 +120,12 @@ typedef struct ModifyTableContext */ typedef struct UpdateContext { + Bitmapset *modifiedIndexes; /* Either NULL indicating that all indexes + * should be updated or a bitmap of + * IndexInfo array positions for the + * subset of modified indexes requiring + * updates. */ 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 @@ -2266,7 +2270,8 @@ lreplace: estate->es_crosscheck_snapshot, true /* wait for commit */ , &context->tmfd, &updateCxt->lockmode, - &updateCxt->updateIndexes); + &updateCxt->modifiedIndexes, + estate); return result; } @@ -2286,12 +2291,12 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, List *recheckIndexes = NIL; /* insert index entries for tuple if necessary */ - if (resultRelInfo->ri_NumIndices > 0 && (updateCxt->updateIndexes != TU_None)) + if (resultRelInfo->ri_NumIndices > 0) recheckIndexes = ExecInsertIndexTuples(resultRelInfo, slot, context->estate, true, false, NULL, NIL, - (updateCxt->updateIndexes == TU_Summarizing)); + updateCxt->modifiedIndexes); /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(context->estate, resultRelInfo, diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 43219a9629..f9a21a604a 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -2433,7 +2433,6 @@ RelationDestroyRelation(Relation relation, bool remember_tupdesc) 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) @@ -5226,7 +5225,6 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind) 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; @@ -5247,8 +5245,6 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind) 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); } @@ -5292,7 +5288,6 @@ restart: pkindexattrs = NULL; idindexattrs = NULL; hotblockingattrs = NULL; - summarizedattrs = NULL; foreach(l, indexoidlist) { Oid indexOid = lfirst_oid(l); @@ -5305,7 +5300,6 @@ restart: bool isKey; /* candidate key */ bool isPK; /* primary key */ bool isIDKey; /* replica identity index */ - Bitmapset **attrs; indexDesc = index_open(indexOid, AccessShareLock); @@ -5343,16 +5337,6 @@ restart: /* Is this index the configured (or default) replica identity? */ isIDKey = (indexOid == relreplindex); - /* - * If the index is summarizing, it doesn't block HOT updates, but we - * may still need to update it (if the attributes were modified). So - * decide which bitmap we'll update in the following loop. - */ - if (indexDesc->rd_indam->amsummarizing) - attrs = &summarizedattrs; - else - attrs = &hotblockingattrs; - /* Collect simple attribute references */ for (i = 0; i < indexDesc->rd_index->indnatts; i++) { @@ -5374,8 +5358,8 @@ restart: */ if (attrnum != 0) { - *attrs = bms_add_member(*attrs, - attrnum - FirstLowInvalidHeapAttributeNumber); + hotblockingattrs = bms_add_member(hotblockingattrs, + attrnum - FirstLowInvalidHeapAttributeNumber); if (isKey && i < indexDesc->rd_index->indnkeyatts) uindexattrs = bms_add_member(uindexattrs, @@ -5392,12 +5376,14 @@ restart: } /* Collect all attributes used in expressions, too */ - pull_varattnos(indexExpressions, 1, attrs); + pull_varattnos(indexExpressions, 1, &hotblockingattrs); /* Collect all attributes in the index predicate, too */ - pull_varattnos(indexPredicate, 1, attrs); + pull_varattnos(indexPredicate, 1, &hotblockingattrs); + + if (indexDesc->rd_index) - index_close(indexDesc, AccessShareLock); + index_close(indexDesc, AccessShareLock); } /* @@ -5424,7 +5410,6 @@ restart: bms_free(pkindexattrs); bms_free(idindexattrs); bms_free(hotblockingattrs); - bms_free(summarizedattrs); goto restart; } @@ -5439,8 +5424,6 @@ restart: 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 @@ -5454,7 +5437,6 @@ restart: relation->rd_pkattr = bms_copy(pkindexattrs); relation->rd_idattr = bms_copy(idindexattrs); relation->rd_hotblockingattr = bms_copy(hotblockingattrs); - relation->rd_summarizedattr = bms_copy(summarizedattrs); relation->rd_attrsvalid = true; MemoryContextSwitchTo(oldcxt); @@ -5469,8 +5451,6 @@ restart: 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; diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 38cb9e970d..1f3eb0caa1 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -1007,6 +1007,19 @@ struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"enable_expression_checks", PGC_USERSET, QUERY_TUNING_METHOD, + gettext_noop("Enables the ability to examine expression and partial indexes " + "for changes during updates."), + gettext_noop("Evaluates the expressions on the index to determine if " + "the indexed values changed. This allows for heap-only " + "tuple (HOT) updates when indexes have not changed."), + GUC_EXPLAIN + }, + &enable_expression_checks, + true, + NULL, NULL, NULL + }, { {"geqo", PGC_USERSET, QUERY_TUNING_GEQO, gettext_noop("Enables genetic query optimization."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 079efa1baa..61ab1bb59b 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -408,6 +408,7 @@ #enable_tidscan = on #enable_group_by_reordering = on #enable_distinct_reordering = on +#enable_expression_checks = on # - Planner Cost Constants - diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index 1640d9c32f..e12173eba4 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -21,6 +21,7 @@ #include "access/skey.h" #include "access/table.h" /* for backward compatibility */ #include "access/tableam.h" +#include "executor/executor.h" #include "nodes/lockoptions.h" #include "nodes/primnodes.h" #include "storage/bufpage.h" @@ -339,7 +340,7 @@ extern TM_Result heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, CommandId cid, Snapshot crosscheck, bool wait, struct TM_FailureData *tmfd, LockTupleMode *lockmode, - TU_UpdateIndexes *update_indexes); + Bitmapset **modified_indexes, struct EState *estate); extern TM_Result heap_lock_tuple(Relation relation, HeapTuple tuple, CommandId cid, LockTupleMode mode, LockWaitPolicy wait_policy, bool follow_updates, @@ -374,7 +375,7 @@ extern bool heap_tuple_needs_eventual_freeze(HeapTupleHeader tuple); extern void simple_heap_insert(Relation relation, HeapTuple tup); extern void simple_heap_delete(Relation relation, ItemPointer tid); extern void simple_heap_update(Relation relation, ItemPointer otid, - HeapTuple tup, TU_UpdateIndexes *update_indexes); + HeapTuple tup); extern TransactionId heap_index_delete_tuples(Relation rel, TM_IndexDeleteOp *delstate); diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index 131c050c15..300226d44d 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -38,6 +38,7 @@ struct IndexInfo; struct SampleScanState; struct VacuumParams; struct ValidateIndexState; +struct EState; /* * Bitmask values for the flags argument to the scan_begin callback. @@ -109,21 +110,6 @@ 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) */ - TU_None, - - /* A non-summarizing indexed column was updated, or the TID has changed */ - TU_All, - - /* Only summarized columns were updated, TID is unchanged */ - TU_Summarizing, -} TU_UpdateIndexes; /* * When table_tuple_update, table_tuple_delete, or table_tuple_lock fail @@ -550,7 +536,8 @@ typedef struct TableAmRoutine bool wait, TM_FailureData *tmfd, LockTupleMode *lockmode, - TU_UpdateIndexes *update_indexes); + Bitmapset **modified_indexes, + struct EState *estate); /* see table_tuple_lock() for reference about parameters */ TM_Result (*tuple_lock) (Relation rel, @@ -1541,12 +1528,12 @@ 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, - TU_UpdateIndexes *update_indexes) + Bitmapset **modified_indexes, struct EState *estate) { return rel->rd_tableam->tuple_update(rel, otid, slot, cid, snapshot, crosscheck, - wait, tmfd, - lockmode, update_indexes); + wait, tmfd, lockmode, + modified_indexes, estate); } /* @@ -2075,8 +2062,7 @@ extern void simple_table_tuple_insert(Relation rel, TupleTableSlot *slot); 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, - TU_UpdateIndexes *update_indexes); + TupleTableSlot *slot, Snapshot snapshot); /* ---------------------------------------------------------------------------- diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index c7db6defd3..2da448fca0 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -649,7 +649,7 @@ extern List *ExecInsertIndexTuples(ResultRelInfo *resultRelInfo, bool update, bool noDupErr, bool *specConflict, List *arbiterIndexes, - bool onlySummarizing); + Bitmapset *modified_indexes); extern bool ExecCheckIndexConstraints(ResultRelInfo *resultRelInfo, TupleTableSlot *slot, EState *estate, ItemPointer conflictTid, @@ -660,6 +660,12 @@ extern void check_exclusion_constraint(Relation heap, Relation index, ItemPointer tupleid, const Datum *values, const bool *isnull, EState *estate, bool newIndex); +extern Bitmapset *ExecIndexesRequiringUpdates(Relation relation, + Bitmapset *modified_attrs, + EState *estate, + HeapTuple old_tuple, + HeapTuple new_tuple, + bool *only_summarizing); /* * prototypes from functions in execReplication.c diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 8ce4430af0..6417769a9a 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -159,12 +159,15 @@ typedef struct ExprState * * NumIndexAttrs total number of columns in this index * NumIndexKeyAttrs number of key columns in index + * IndexAttrs bitmap of index attributes * IndexAttrNumbers underlying-rel attribute numbers used as keys * (zeroes indicate expressions). It also contains * info about included columns. * Expressions expr trees for expression entries, or NIL if none + * ExpressionAttrs bitmap of attributes used within the expression * ExpressionsState exec state for expressions, or NIL if none * Predicate partial-index predicate, or NIL if none + * PredicateAttrs bitmap of attributes used within the predicate * PredicateState exec state for predicate, or NIL if none * ExclusionOps Per-column exclusion operators, or NULL if none * ExclusionProcs Underlying function OIDs for ExclusionOps @@ -183,6 +186,7 @@ typedef struct ExprState * ParallelWorkers # of workers requested (excludes leader) * Am Oid of index AM * AmCache private cache area for index AM + * OpClassDataTypes operator class data types * Context memory context holding this IndexInfo * * ii_Concurrent, ii_BrokenHotChain, and ii_ParallelWorkers are used only @@ -194,10 +198,13 @@ typedef struct IndexInfo NodeTag type; int ii_NumIndexAttrs; /* total number of columns in index */ int ii_NumIndexKeyAttrs; /* number of key columns in index */ + Bitmapset *ii_IndexAttrs; AttrNumber ii_IndexAttrNumbers[INDEX_MAX_KEYS]; List *ii_Expressions; /* list of Expr */ + Bitmapset *ii_ExpressionAttrs; List *ii_ExpressionsState; /* list of ExprState */ List *ii_Predicate; /* list of Expr */ + Bitmapset *ii_PredicateAttrs; ExprState *ii_PredicateState; Oid *ii_ExclusionOps; /* array with one entry per column */ Oid *ii_ExclusionProcs; /* array with one entry per column */ @@ -217,6 +224,7 @@ typedef struct IndexInfo int ii_ParallelWorkers; Oid ii_Am; void *ii_AmCache; + Oid *ii_OpClassDataTypes; MemoryContext ii_Context; } IndexInfo; diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h index bcf8ed645c..f98774e940 100644 --- a/src/include/optimizer/optimizer.h +++ b/src/include/optimizer/optimizer.h @@ -112,6 +112,7 @@ typedef enum extern PGDLLIMPORT int debug_parallel_query; extern PGDLLIMPORT bool parallel_leader_participation; extern PGDLLIMPORT bool enable_distinct_reordering; +extern PGDLLIMPORT bool enable_expression_checks; extern struct PlannedStmt *planner(Query *parse, const char *query_string, int cursorOptions, diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 33d1e4a4e2..3294bcf054 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -154,6 +154,10 @@ typedef struct RelationData bool rd_ispkdeferrable; /* is rd_pkindex a deferrable PK? */ Oid rd_replidindex; /* OID of replica identity index, if any */ + /* list of IndexInfo for this relation */ + List *rd_indexinfolist; /* an ordered list of IndexInfo for + * indexes on relation */ + /* data managed by RelationGetStatExtList: */ List *rd_statlist; /* list of OIDs of extended stats */ @@ -163,7 +167,6 @@ typedef struct RelationData 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 summarizing indexes */ PublicationDesc *rd_pubdesc; /* publication descriptor, or NULL */ diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h index a7c55db339..0cc28cb97e 100644 --- a/src/include/utils/relcache.h +++ b/src/include/utils/relcache.h @@ -63,6 +63,7 @@ typedef enum IndexAttrBitmapKind INDEX_ATTR_BITMAP_IDENTITY_KEY, INDEX_ATTR_BITMAP_HOT_BLOCKING, INDEX_ATTR_BITMAP_SUMMARIZED, + INDEX_ATTR_BITMAP_EXPRESSION, } IndexAttrBitmapKind; extern Bitmapset *RelationGetIndexAttrBitmap(Relation relation, diff --git a/src/test/regress/expected/heap_hot_updates.out b/src/test/regress/expected/heap_hot_updates.out new file mode 100644 index 0000000000..177c6109f5 --- /dev/null +++ b/src/test/regress/expected/heap_hot_updates.out @@ -0,0 +1,318 @@ +SHOW enable_expression_checks; + enable_expression_checks +-------------------------- + on +(1 row) + +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- This table will have two columns and two indexes, one on the primary key +-- id and one on the expression (info->>'name'). That means that the indexed +-- attributes are 'id' and 'info'. +create table keyvalue(id integer primary key, info jsonb); +create index nameindex on keyvalue((info->>'name')); +insert into keyvalue values (1, '{"name": "john", "data": "some data"}'); +-- Disable the GUC that allows us to skip expression checks. +SET enable_expression_checks = 'off'; +-- The indexed attribute "name" with value "john" is unchanged, don't expect a HOT update. +update keyvalue set info='{"name": "john", "data": "something else"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 0 row + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 0 +(1 row) + +-- Re-enable the GUC so that we don't skip expression checks. +SET enable_expression_checks = 'on'; +-- The indexed attribute "name" with value "john" is unchanged, expect a HOT update. +update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 1 row + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- The following update changes the indexed attribute "name", this should not be a HOT update. +update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 1 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- Now, this update does not change the indexed attribute "name" from "smith", this should be HOT. +update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 2 rows now + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 2 +(1 row) + +drop table keyvalue; +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- This table is the same as the previous one but it has a third index. The +-- index 'colindex' isn't an expression index, it indexes the entire value +-- in the info column. There are still only two indexed attributes for this +-- relation, the same two as before. The presence of an index on the entire +-- value of the info column should prevent HOT updates for any updates to any +-- portion of JSONB content in that column. +create table keyvalue(id integer primary key, info jsonb); +create index nameindex on keyvalue((info->>'name')); +create index colindex on keyvalue(info); +insert into keyvalue values (1, '{"name": "john", "data": "some data"}'); +-- This update doesn't change the value of the expression index, but it does +-- change the content of the info column and so should not be HOT because the +-- indexed value changed as a result of the update. +update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 0 rows + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 0 +(1 row) + +drop table keyvalue; +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- The table has one column _v and two indexes. They are both expression +-- indexes referencing the same column attribute (_v) but one is a partial +-- index. +CREATE TABLE public.ex (_v JSONB) WITH (fillfactor = 60); +INSERT INTO ex (_v) VALUES ('{"a": 0, "b": 0}'); +INSERT INTO ex (_v) SELECT jsonb_build_object('b', n) FROM generate_series(100, 10000) as n; +CREATE INDEX idx_ex_a ON ex ((_v->>'a')); +CREATE INDEX idx_ex_b ON ex ((_v->>'b')) WHERE (_v->>'b')::numeric > 9; +-- We're using BTREE indexes and for this test we want to make sure that they remain +-- in sync with changes to our relation. Force the choice of index scans below so +-- that we know we're checking the index's understanding of what values should be +-- in the index or not. +SET SESSION enable_seqscan = OFF; +SET SESSION enable_bitmapscan = OFF; +-- Leave 'a' unchanged but modify 'b' to a value outside of the index predicate. +-- This should be a HOT update because neither index is changed. +UPDATE ex SET _v = jsonb_build_object('a', 0, 'b', 1) WHERE (_v->>'a')::numeric = 0; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- Let's check to make sure that the index does not contain a value for 'b' +EXPLAIN (COSTS OFF) SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + QUERY PLAN +------------------------------------------------------------ + Index Scan using idx_ex_b on ex + Filter: (((_v ->> 'b'::text))::numeric < '100'::numeric) +(2 rows) + +SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + _v +---- +(0 rows) + +-- Leave 'a' unchanged but modify 'b' to a value within the index predicate. +-- This represents a change for field 'b' from unindexed to indexed and so +-- this should not take the HOT path. +UPDATE ex SET _v = jsonb_build_object('a', 0, 'b', 10) WHERE (_v->>'a')::numeric = 0; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- Let's check to make sure that the index contains the new value of 'b' +EXPLAIN (COSTS OFF) SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + QUERY PLAN +------------------------------------------------------------ + Index Scan using idx_ex_b on ex + Filter: (((_v ->> 'b'::text))::numeric < '100'::numeric) +(2 rows) + +SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + _v +------------------- + {"a": 0, "b": 10} +(1 row) + +-- This update modifies the value of 'a', an indexed field, so it also cannot +-- be a HOT update. +UPDATE ex SET _v = jsonb_build_object('a', 1, 'b', 10) WHERE (_v->>'b')::numeric = 10; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- This update changes both 'a' and 'b' to new values that require index updates, +-- this cannot use the HOT path. +UPDATE ex SET _v = jsonb_build_object('a', 2, 'b', 12) WHERE (_v->>'b')::numeric = 10; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- Let's check to make sure that the index contains the new value of 'b' +EXPLAIN (COSTS OFF) SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + QUERY PLAN +------------------------------------------------------------ + Index Scan using idx_ex_b on ex + Filter: (((_v ->> 'b'::text))::numeric < '100'::numeric) +(2 rows) + +SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + _v +------------------- + {"a": 2, "b": 12} +(1 row) + +-- This update changes 'b' to a value outside its predicate requiring that +-- we remove it from the index. That's a transition that can't be done +-- during a HOT update. +UPDATE ex SET _v = jsonb_build_object('a', 2, 'b', 1) WHERE (_v->>'b')::numeric = 12; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- Let's check to make sure that the index no longer contains the value of 'b' +EXPLAIN (COSTS OFF) SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + QUERY PLAN +------------------------------------------------------------ + Index Scan using idx_ex_b on ex + Filter: (((_v ->> 'b'::text))::numeric < '100'::numeric) +(2 rows) + +SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + _v +---- +(0 rows) + +-- Let's make sure we're recording HOT updates for our 'ex' relation properly in the system +-- table pg_stat_user_tables. Note that statistics are stored within a transaction context +-- first (xact) and then later into the global statistics for a relation. +SELECT + c.relname AS table_name, + -- Transaction statistics + pg_stat_get_xact_tuples_updated(c.oid) AS xact_updates, + pg_stat_get_xact_tuples_hot_updated(c.oid) AS xact_hot_updates, + ROUND(( + pg_stat_get_xact_tuples_hot_updated(c.oid)::float / + NULLIF(pg_stat_get_xact_tuples_updated(c.oid), 0) * 100 + )::numeric, 2) AS xact_hot_update_percentage, + -- Cumulative statistics + s.n_tup_upd AS total_updates, + s.n_tup_hot_upd AS hot_updates, + ROUND(( + s.n_tup_hot_upd::float / + NULLIF(s.n_tup_upd, 0) * 100 + )::numeric, 2) AS total_hot_update_percentage +FROM pg_class c +LEFT JOIN pg_stat_user_tables s ON c.relname = s.relname +WHERE c.relname = 'ex' +AND c.relnamespace = 'public'::regnamespace; + table_name | xact_updates | xact_hot_updates | xact_hot_update_percentage | total_updates | hot_updates | total_hot_update_percentage +------------+--------------+------------------+----------------------------+---------------+-------------+----------------------------- + ex | 5 | 1 | 20.00 | 0 | 0 | +(1 row) + +-- expect: 5 xact updates with 1 xact hot update and no cumulative updates as yet +DROP TABLE public.ex; +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- This table has a single column 'email' and a unique constraint on it that +-- should preclude HOT updates. +CREATE TABLE users ( + user_id serial primary key, + name VARCHAR(255) NOT NULL, + email VARCHAR(255) NOT NULL, + EXCLUDE USING btree (lower(email) WITH =) +); +-- Add some data to the table and then update it in ways that should and should +-- not be HOT updates. +INSERT INTO users (name, email) VALUES +('user1', 'user1@example.com'), +('user2', 'user2@example.com'), +('taken', 'taken@EXAMPLE.com'), +('you', 'you@domain.com'), +('taken', 'taken@domain.com'); +-- Should fail because of the unique constraint on the email column. +UPDATE users SET email = 'user1@example.com' WHERE email = 'user2@example.com'; +ERROR: conflicting key value violates exclusion constraint "users_lower_excl" +DETAIL: Key (lower(email::text))=(user1@example.com) conflicts with existing key (lower(email::text))=(user1@example.com). +SELECT pg_stat_get_xact_tuples_hot_updated('users'::regclass); -- expect: 0 rows, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 0 +(1 row) + +-- Should succeed because the email column is not being updated and should go HOT. +UPDATE users SET name = 'foo' WHERE email = 'user1@example.com'; +SELECT pg_stat_get_xact_tuples_hot_updated('users'::regclass); -- expect: 1 row, a single new HOT update + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- Create a partial index on the email column, updates +CREATE INDEX idx_users_email_no_example ON users (lower(email)) WHERE lower(email) LIKE '%@example.com%'; +-- An update that changes the email column but not the indexed portion of it and falls outside the constraint. +-- Shouldn't be a HOT update because of the exclusion constraint. +UPDATE users SET email = 'you+2@domain.com' WHERE name = 'you'; +SELECT pg_stat_get_xact_tuples_hot_updated('users'::regclass); -- expect: 1 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +-- An update that changes the email column but not the indexed portion of it and falls within the constraint. +-- Again, should fail constraint and fail to be a HOT update. +UPDATE users SET email = 'taken@domain.com' WHERE name = 'you'; +ERROR: conflicting key value violates exclusion constraint "users_lower_excl" +DETAIL: Key (lower(email::text))=(taken@domain.com) conflicts with existing key (lower(email::text))=(taken@domain.com). +SELECT pg_stat_get_xact_tuples_hot_updated('users'::regclass); -- expect: 1 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +DROP TABLE users; +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- Another test of constraints spoiling HOT updates, this time with a range. +CREATE TABLE events ( + id serial primary key, + name VARCHAR(255) NOT NULL, + event_time tstzrange, + constraint no_screening_time_overlap exclude using gist ( + event_time WITH && + ) +); +-- Add two non-overlapping events. +INSERT INTO events (id, event_time, name) +VALUES + (1, '["2023-01-01 19:00:00", "2023-01-01 20:45:00"]', 'event1'), + (2, '["2023-01-01 21:00:00", "2023-01-01 21:45:00"]', 'event2'); +-- Update the first event to overlap with the second, should fail the constraint and not be HOT. +UPDATE events SET event_time = '["2023-01-01 20:00:00", "2023-01-01 21:45:00"]' WHERE id = 1; +ERROR: conflicting key value violates exclusion constraint "no_screening_time_overlap" +DETAIL: Key (event_time)=(["Sun Jan 01 20:00:00 2023 PST","Sun Jan 01 21:45:00 2023 PST"]) conflicts with existing key (event_time)=(["Sun Jan 01 21:00:00 2023 PST","Sun Jan 01 21:45:00 2023 PST"]). +SELECT pg_stat_get_xact_tuples_hot_updated('events'::regclass); -- expect: 0 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 0 +(1 row) + +-- Update the first event to not overlap with the second, again not HOT due to the constraint. +UPDATE events SET event_time = '["2023-01-01 22:00:00", "2023-01-01 22:45:00"]' WHERE id = 1; +SELECT pg_stat_get_xact_tuples_hot_updated('events'::regclass); -- expect: 0 row, no new HOT updates + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 0 +(1 row) + +-- Update the first event to not overlap with the second, this time we're HOT because we don't overlap with the constraint. +UPDATE events SET name = 'new name here' WHERE id = 1; +SELECT pg_stat_get_xact_tuples_hot_updated('events'::regclass); -- expect: 1 row, one new HOT update + pg_stat_get_xact_tuples_hot_updated +------------------------------------- + 1 +(1 row) + +DROP TABLE events; diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out index 352abc0bd4..56451bf783 100644 --- a/src/test/regress/expected/sysviews.out +++ b/src/test/regress/expected/sysviews.out @@ -151,6 +151,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_async_append | on enable_bitmapscan | on enable_distinct_reordering | on + enable_expression_checks | on enable_gathermerge | on enable_group_by_reordering | on enable_hashagg | on @@ -171,7 +172,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_seqscan | on enable_sort | on enable_tidscan | on -(23 rows) +(24 rows) -- There are always wait event descriptions for various types. InjectionPoint -- may be present or absent, depending on history since last postmaster start. diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 1edd9e45eb..34232fd337 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -68,6 +68,11 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi # ---------- test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password identity generated_stored join_hash +# ---------- +# Another group of parallel tests +# ---------- +test: heap_hot_updates + # ---------- # Additional BRIN tests # ---------- diff --git a/src/test/regress/sql/heap_hot_updates.sql b/src/test/regress/sql/heap_hot_updates.sql new file mode 100644 index 0000000000..22c6eafeab --- /dev/null +++ b/src/test/regress/sql/heap_hot_updates.sql @@ -0,0 +1,208 @@ +SHOW enable_expression_checks; + +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- This table will have two columns and two indexes, one on the primary key +-- id and one on the expression (info->>'name'). That means that the indexed +-- attributes are 'id' and 'info'. +create table keyvalue(id integer primary key, info jsonb); +create index nameindex on keyvalue((info->>'name')); +insert into keyvalue values (1, '{"name": "john", "data": "some data"}'); + +-- Disable the GUC that allows us to skip expression checks. +SET enable_expression_checks = 'off'; + +-- The indexed attribute "name" with value "john" is unchanged, don't expect a HOT update. +update keyvalue set info='{"name": "john", "data": "something else"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 0 row + +-- Re-enable the GUC so that we don't skip expression checks. +SET enable_expression_checks = 'on'; + +-- The indexed attribute "name" with value "john" is unchanged, expect a HOT update. +update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 1 row + +-- The following update changes the indexed attribute "name", this should not be a HOT update. +update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 1 row, no new HOT updates + +-- Now, this update does not change the indexed attribute "name" from "smith", this should be HOT. +update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 2 rows now +drop table keyvalue; + + +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- This table is the same as the previous one but it has a third index. The +-- index 'colindex' isn't an expression index, it indexes the entire value +-- in the info column. There are still only two indexed attributes for this +-- relation, the same two as before. The presence of an index on the entire +-- value of the info column should prevent HOT updates for any updates to any +-- portion of JSONB content in that column. +create table keyvalue(id integer primary key, info jsonb); +create index nameindex on keyvalue((info->>'name')); +create index colindex on keyvalue(info); +insert into keyvalue values (1, '{"name": "john", "data": "some data"}'); + +-- This update doesn't change the value of the expression index, but it does +-- change the content of the info column and so should not be HOT because the +-- indexed value changed as a result of the update. +update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1; +select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass); -- expect: 0 rows +drop table keyvalue; + +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- The table has one column _v and two indexes. They are both expression +-- indexes referencing the same column attribute (_v) but one is a partial +-- index. +CREATE TABLE public.ex (_v JSONB) WITH (fillfactor = 60); +INSERT INTO ex (_v) VALUES ('{"a": 0, "b": 0}'); +INSERT INTO ex (_v) SELECT jsonb_build_object('b', n) FROM generate_series(100, 10000) as n; +CREATE INDEX idx_ex_a ON ex ((_v->>'a')); +CREATE INDEX idx_ex_b ON ex ((_v->>'b')) WHERE (_v->>'b')::numeric > 9; + +-- We're using BTREE indexes and for this test we want to make sure that they remain +-- in sync with changes to our relation. Force the choice of index scans below so +-- that we know we're checking the index's understanding of what values should be +-- in the index or not. +SET SESSION enable_seqscan = OFF; +SET SESSION enable_bitmapscan = OFF; + +-- Leave 'a' unchanged but modify 'b' to a value outside of the index predicate. +-- This should be a HOT update because neither index is changed. +UPDATE ex SET _v = jsonb_build_object('a', 0, 'b', 1) WHERE (_v->>'a')::numeric = 0; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row +-- Let's check to make sure that the index does not contain a value for 'b' +EXPLAIN (COSTS OFF) SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; +SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + +-- Leave 'a' unchanged but modify 'b' to a value within the index predicate. +-- This represents a change for field 'b' from unindexed to indexed and so +-- this should not take the HOT path. +UPDATE ex SET _v = jsonb_build_object('a', 0, 'b', 10) WHERE (_v->>'a')::numeric = 0; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row, no new HOT updates +-- Let's check to make sure that the index contains the new value of 'b' +EXPLAIN (COSTS OFF) SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; +SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + +-- This update modifies the value of 'a', an indexed field, so it also cannot +-- be a HOT update. +UPDATE ex SET _v = jsonb_build_object('a', 1, 'b', 10) WHERE (_v->>'b')::numeric = 10; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row, no new HOT updates + +-- This update changes both 'a' and 'b' to new values that require index updates, +-- this cannot use the HOT path. +UPDATE ex SET _v = jsonb_build_object('a', 2, 'b', 12) WHERE (_v->>'b')::numeric = 10; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row, no new HOT updates +-- Let's check to make sure that the index contains the new value of 'b' +EXPLAIN (COSTS OFF) SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; +SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + +-- This update changes 'b' to a value outside its predicate requiring that +-- we remove it from the index. That's a transition that can't be done +-- during a HOT update. +UPDATE ex SET _v = jsonb_build_object('a', 2, 'b', 1) WHERE (_v->>'b')::numeric = 12; +SELECT pg_stat_get_xact_tuples_hot_updated('ex'::regclass); -- expect: 1 row, no new HOT updates +-- Let's check to make sure that the index no longer contains the value of 'b' +EXPLAIN (COSTS OFF) SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; +SELECT * FROM ex WHERE (_v->>'b')::numeric > 9 AND (_v->>'b')::numeric < 100; + +-- Let's make sure we're recording HOT updates for our 'ex' relation properly in the system +-- table pg_stat_user_tables. Note that statistics are stored within a transaction context +-- first (xact) and then later into the global statistics for a relation. +SELECT + c.relname AS table_name, + -- Transaction statistics + pg_stat_get_xact_tuples_updated(c.oid) AS xact_updates, + pg_stat_get_xact_tuples_hot_updated(c.oid) AS xact_hot_updates, + ROUND(( + pg_stat_get_xact_tuples_hot_updated(c.oid)::float / + NULLIF(pg_stat_get_xact_tuples_updated(c.oid), 0) * 100 + )::numeric, 2) AS xact_hot_update_percentage, + -- Cumulative statistics + s.n_tup_upd AS total_updates, + s.n_tup_hot_upd AS hot_updates, + ROUND(( + s.n_tup_hot_upd::float / + NULLIF(s.n_tup_upd, 0) * 100 + )::numeric, 2) AS total_hot_update_percentage +FROM pg_class c +LEFT JOIN pg_stat_user_tables s ON c.relname = s.relname +WHERE c.relname = 'ex' +AND c.relnamespace = 'public'::regnamespace; +-- expect: 5 xact updates with 1 xact hot update and no cumulative updates as yet + +DROP TABLE public.ex; + +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- This table has a single column 'email' and a unique constraint on it that +-- should preclude HOT updates. +CREATE TABLE users ( + user_id serial primary key, + name VARCHAR(255) NOT NULL, + email VARCHAR(255) NOT NULL, + EXCLUDE USING btree (lower(email) WITH =) +); + +-- Add some data to the table and then update it in ways that should and should +-- not be HOT updates. +INSERT INTO users (name, email) VALUES +('user1', 'user1@example.com'), +('user2', 'user2@example.com'), +('taken', 'taken@EXAMPLE.com'), +('you', 'you@domain.com'), +('taken', 'taken@domain.com'); + +-- Should fail because of the unique constraint on the email column. +UPDATE users SET email = 'user1@example.com' WHERE email = 'user2@example.com'; +SELECT pg_stat_get_xact_tuples_hot_updated('users'::regclass); -- expect: 0 rows, no new HOT updates + +-- Should succeed because the email column is not being updated and should go HOT. +UPDATE users SET name = 'foo' WHERE email = 'user1@example.com'; +SELECT pg_stat_get_xact_tuples_hot_updated('users'::regclass); -- expect: 1 row, a single new HOT update + +-- Create a partial index on the email column, updates +CREATE INDEX idx_users_email_no_example ON users (lower(email)) WHERE lower(email) LIKE '%@example.com%'; + +-- An update that changes the email column but not the indexed portion of it and falls outside the constraint. +-- Shouldn't be a HOT update because of the exclusion constraint. +UPDATE users SET email = 'you+2@domain.com' WHERE name = 'you'; +SELECT pg_stat_get_xact_tuples_hot_updated('users'::regclass); -- expect: 1 row, no new HOT updates + +-- An update that changes the email column but not the indexed portion of it and falls within the constraint. +-- Again, should fail constraint and fail to be a HOT update. +UPDATE users SET email = 'taken@domain.com' WHERE name = 'you'; +SELECT pg_stat_get_xact_tuples_hot_updated('users'::regclass); -- expect: 1 row, no new HOT updates + +DROP TABLE users; + +-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +-- Another test of constraints spoiling HOT updates, this time with a range. +CREATE TABLE events ( + id serial primary key, + name VARCHAR(255) NOT NULL, + event_time tstzrange, + constraint no_screening_time_overlap exclude using gist ( + event_time WITH && + ) +); + +-- Add two non-overlapping events. +INSERT INTO events (id, event_time, name) +VALUES + (1, '["2023-01-01 19:00:00", "2023-01-01 20:45:00"]', 'event1'), + (2, '["2023-01-01 21:00:00", "2023-01-01 21:45:00"]', 'event2'); + +-- Update the first event to overlap with the second, should fail the constraint and not be HOT. +UPDATE events SET event_time = '["2023-01-01 20:00:00", "2023-01-01 21:45:00"]' WHERE id = 1; +SELECT pg_stat_get_xact_tuples_hot_updated('events'::regclass); -- expect: 0 row, no new HOT updates + +-- Update the first event to not overlap with the second, again not HOT due to the constraint. +UPDATE events SET event_time = '["2023-01-01 22:00:00", "2023-01-01 22:45:00"]' WHERE id = 1; +SELECT pg_stat_get_xact_tuples_hot_updated('events'::regclass); -- expect: 0 row, no new HOT updates + +-- Update the first event to not overlap with the second, this time we're HOT because we don't overlap with the constraint. +UPDATE events SET name = 'new name here' WHERE id = 1; +SELECT pg_stat_get_xact_tuples_hot_updated('events'::regclass); -- expect: 1 row, one new HOT update + +DROP TABLE events; -- 2.42.0