On 6/1/22 22:38, Robert Haas wrote:
> On Sat, May 28, 2022 at 4:51 PM Tomas Vondra
> <tomas.von...@enterprisedb.com> wrote:
>> Yeah, I think that might/should work. We could still create the HOT
>> chain, but we'd have to update the BRIN indexes. But that seems like a
>> fairly complicated change to be done this late for PG15.
>
> Yeah, I think a revert is better for now. But I agree that the basic
> idea seems salvageable. I think that the commit message is correct
> when it states that "When determining whether an index update may be
> skipped by using HOT, we can ignore attributes indexed only by BRIN
> indexes." However, that doesn't mean that we can ignore the need to
> update those indexes. In that regard, the commit message makes it
> sound like all is well, because it states that "the page range summary
> will be updated anyway" which reads to me like the indexes are in fact
> getting updated. Your example, however, seems to show that the indexes
> are not getting updated.
>
Yeah, agreed :-( I agree we can probably salvage some of the idea, but
it's far too late for major reworks in PG15.
Attached is a patch reverting both commits (5753d4ee32 and fe60b67250).
This changes the IndexAmRoutine struct, so it's an ABI break. That's not
great post-beta :-( In principle we might also leave amhotblocking in
the struct but ignore it in the code (and treat it as false), but that
seems weird and it's going to be a pain when backpatching. Opinions?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From 22911ff0df0284bd9c97d4971ab10332444c528d Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Mon, 6 Jun 2022 08:21:36 +0200
Subject: [PATCH] Revert changes in HOT handling of BRIN indexes
This reverts commits 5753d4ee32 and fe60b67250 that modified HOT to
ignore BRIN indexes. The commit message for 5753d4ee32 claims that:
When determining whether an index update may be skipped by using
HOT, we can ignore attributes indexed only by BRIN indexes. There
are no index pointers to individual tuples in BRIN, and the page
range summary will be updated anyway as it relies on visibility
info.
This is partially incorrect - it's true BRIN indexes don't point to
individual tuples, so HOT chains are not an issue, but the visibitlity
info is not sufficient to keep the index up to date. This can easily
result in corrupted indexes, as demonstrated in the hackers thread.
This does not mean relaxing the HOT restrictions for BRIN is a lost
cause, but it needs to handle the two aspects (allowing HOT chains and
updating the page range summaries) as separate. But that requires a
major changes, and it's too late for that in the current dev cycle.
Reported-by: Tomas Vondra
Discussion: https://postgr.es/m/05ebcb44-f383-86e3-4f31-0a97a5563...@enterprisedb.com
---
doc/src/sgml/indexam.sgml | 11 -
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 | 2 +-
src/backend/access/nbtree/nbtree.c | 1 -
src/backend/access/spgist/spgutils.c | 1 -
src/backend/utils/cache/relcache.c | 53 ++--
src/include/access/amapi.h | 2 -
src/include/utils/rel.h | 3 +-
src/include/utils/relcache.h | 4 +-
.../modules/dummy_index_am/dummy_index_am.c | 1 -
src/test/regress/expected/brin.out | 58 -----
src/test/regress/expected/stats.out | 242 ------------------
src/test/regress/sql/brin.sql | 36 ---
src/test/regress/sql/stats.sql | 111 --------
17 files changed, 27 insertions(+), 502 deletions(-)
diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml
index d4163c96e9f..cf359fa9ffd 100644
--- a/doc/src/sgml/indexam.sgml
+++ b/doc/src/sgml/indexam.sgml
@@ -126,8 +126,6 @@ typedef struct IndexAmRoutine
bool amcaninclude;
/* does AM use maintenance_work_mem? */
bool amusemaintenanceworkmem;
- /* does AM block HOT update? */
- bool amhotblocking;
/* OR of parallel vacuum flags */
uint8 amparallelvacuumoptions;
/* type of data stored in index, or InvalidOid if variable */
@@ -248,15 +246,6 @@ typedef struct IndexAmRoutine
null, independently of <structfield>amoptionalkey</structfield>.
</para>
- <para>
- The <structfield>amhotblocking</structfield> flag indicates whether the
- access method blocks <acronym>HOT</acronym> when an indexed attribute is
- updated. Access methods without pointers to individual tuples (like
- <acronym>BRIN</acronym>) may allow <acronym>HOT</acronym> even in this
- case. 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 0de1441dc6d..e88f7efa7e4 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -108,7 +108,6 @@ brinhandler(PG_FUNCTION_ARGS)
amroutine->amcanparallel = false;
amroutine->amcaninclude = false;
amroutine->amusemaintenanceworkmem = false;
- amroutine->amhotblocking = false;
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 3d15701a01e..20f470648be 100644
--- a/src/backend/access/gin/ginutil.c
+++ b/src/backend/access/gin/ginutil.c
@@ -56,7 +56,6 @@ ginhandler(PG_FUNCTION_ARGS)
amroutine->amcanparallel = false;
amroutine->amcaninclude = false;
amroutine->amusemaintenanceworkmem = true;
- amroutine->amhotblocking = true;
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 8c6c744ab74..5866c6aaaf7 100644
--- a/src/backend/access/gist/gist.c
+++ b/src/backend/access/gist/gist.c
@@ -78,7 +78,6 @@ gisthandler(PG_FUNCTION_ARGS)
amroutine->amcanparallel = false;
amroutine->amcaninclude = true;
amroutine->amusemaintenanceworkmem = false;
- amroutine->amhotblocking = true;
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 fd1a7119b6c..c361509d68d 100644
--- a/src/backend/access/hash/hash.c
+++ b/src/backend/access/hash/hash.c
@@ -75,7 +75,6 @@ hashhandler(PG_FUNCTION_ARGS)
amroutine->amcanparallel = false;
amroutine->amcaninclude = false;
amroutine->amusemaintenanceworkmem = false;
- amroutine->amhotblocking = true;
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 74218510276..637de1116c9 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -3190,7 +3190,7 @@ 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_HOT_BLOCKING);
+ hot_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_ALL);
key_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_KEY);
id_attrs = RelationGetIndexAttrBitmap(relation,
INDEX_ATTR_BITMAP_IDENTITY_KEY);
diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c
index 06131f23d4b..9b730f303fb 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -114,7 +114,6 @@ bthandler(PG_FUNCTION_ARGS)
amroutine->amcanparallel = true;
amroutine->amcaninclude = true;
amroutine->amusemaintenanceworkmem = false;
- amroutine->amhotblocking = true;
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 a171ca8a08a..2c661fcf96f 100644
--- a/src/backend/access/spgist/spgutils.c
+++ b/src/backend/access/spgist/spgutils.c
@@ -62,7 +62,6 @@ spghandler(PG_FUNCTION_ARGS)
amroutine->amcanparallel = false;
amroutine->amcaninclude = true;
amroutine->amusemaintenanceworkmem = false;
- amroutine->amhotblocking = true;
amroutine->amparallelvacuumoptions =
VACUUM_OPTION_PARALLEL_BULKDEL | VACUUM_OPTION_PARALLEL_COND_CLEANUP;
amroutine->amkeytype = InvalidOid;
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 60e72f9e8bf..0e8fda97f86 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2439,10 +2439,10 @@ 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);
if (relation->rd_pubdesc)
pfree(relation->rd_pubdesc);
if (relation->rd_options)
@@ -5104,10 +5104,10 @@ 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 */
List *indexoidlist;
List *newindexoidlist;
Oid relpkindex;
@@ -5116,18 +5116,18 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
MemoryContext oldcxt;
/* Quick exit if we already computed the result. */
- if (relation->rd_attrsvalid)
+ if (relation->rd_indexattr != NULL)
{
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);
default:
elog(ERROR, "unknown attrKind %u", attrKind);
}
@@ -5158,7 +5158,7 @@ restart:
relreplindex = relation->rd_replidindex;
/*
- * For each index, add referenced attributes to appropriate bitmaps.
+ * For each index, add referenced attributes to indexattrs.
*
* Note: we consider all indexes returned by RelationGetIndexList, even if
* they are not indisready or indisvalid. This is important because an
@@ -5167,10 +5167,10 @@ 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;
foreach(l, indexoidlist)
{
Oid indexOid = lfirst_oid(l);
@@ -5235,9 +5235,8 @@ restart:
*/
if (attrnum != 0)
{
- if (indexDesc->rd_indam->amhotblocking)
- hotblockingattrs = bms_add_member(hotblockingattrs,
- attrnum - FirstLowInvalidHeapAttributeNumber);
+ indexattrs = bms_add_member(indexattrs,
+ attrnum - FirstLowInvalidHeapAttributeNumber);
if (isKey && i < indexDesc->rd_index->indnkeyatts)
uindexattrs = bms_add_member(uindexattrs,
@@ -5254,15 +5253,10 @@ restart:
}
/* Collect all attributes used in expressions, too */
- if (indexDesc->rd_indam->amhotblocking)
- pull_varattnos(indexExpressions, 1, &hotblockingattrs);
+ pull_varattnos(indexExpressions, 1, &indexattrs);
- /*
- * Collect all attributes in the index predicate, too. We have to
- * ignore amhotblocking flag, because the row might become indexable,
- * in which case we have to add it to the index.
- */
- pull_varattnos(indexPredicate, 1, &hotblockingattrs);
+ /* Collect all attributes in the index predicate, too */
+ pull_varattnos(indexPredicate, 1, &indexattrs);
index_close(indexDesc, AccessShareLock);
}
@@ -5290,46 +5284,46 @@ restart:
bms_free(uindexattrs);
bms_free(pkindexattrs);
bms_free(idindexattrs);
- bms_free(hotblockingattrs);
+ bms_free(indexattrs);
goto restart;
}
/* Don't leak the old values of these bitmaps, if any */
+ bms_free(relation->rd_indexattr);
+ relation->rd_indexattr = NULL;
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;
/*
* Now save copies of the bitmaps in the relcache entry. We intentionally
- * set rd_attrsvalid last, because that's what 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.
+ * set rd_indexattr 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.
*/
oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
relation->rd_keyattr = bms_copy(uindexattrs);
relation->rd_pkattr = bms_copy(pkindexattrs);
relation->rd_idattr = bms_copy(idindexattrs);
- relation->rd_hotblockingattr = bms_copy(hotblockingattrs);
- relation->rd_attrsvalid = true;
+ relation->rd_indexattr = bms_copy(indexattrs);
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;
default:
elog(ERROR, "unknown attrKind %u", attrKind);
return NULL;
@@ -6250,11 +6244,10 @@ load_relcache_init_file(bool shared)
rel->rd_indexlist = NIL;
rel->rd_pkindex = InvalidOid;
rel->rd_replidindex = InvalidOid;
- rel->rd_attrsvalid = false;
+ rel->rd_indexattr = NULL;
rel->rd_keyattr = NULL;
rel->rd_pkattr = NULL;
rel->rd_idattr = NULL;
- rel->rd_hotblockingattr = NULL;
rel->rd_pubdesc = NULL;
rel->rd_statvalid = false;
rel->rd_statlist = NIL;
diff --git a/src/include/access/amapi.h b/src/include/access/amapi.h
index 0b89f399f08..1dc674d2305 100644
--- a/src/include/access/amapi.h
+++ b/src/include/access/amapi.h
@@ -244,8 +244,6 @@ typedef struct IndexAmRoutine
bool amcaninclude;
/* does AM use maintenance_work_mem? */
bool amusemaintenanceworkmem;
- /* does AM block HOT update? */
- bool amhotblocking;
/* 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/utils/rel.h b/src/include/utils/rel.h
index 90b3c49bc12..1896a9a06d1 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -155,11 +155,10 @@ typedef struct RelationData
List *rd_statlist; /* list of OIDs of extended stats */
/* data managed by RelationGetIndexAttrBitmap: */
- bool rd_attrsvalid; /* are bitmaps of attrs valid? */
+ Bitmapset *rd_indexattr; /* identifies columns used in indexes */
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 */
PublicationDesc *rd_pubdesc; /* publication descriptor, or NULL */
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 86dddbd975d..c93d8654bb9 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -55,10 +55,10 @@ 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_HOT_BLOCKING
+ INDEX_ATTR_BITMAP_IDENTITY_KEY
} 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 22578b6246e..a0894ff9860 100644
--- a/src/test/modules/dummy_index_am/dummy_index_am.c
+++ b/src/test/modules/dummy_index_am/dummy_index_am.c
@@ -298,7 +298,6 @@ dihandler(PG_FUNCTION_ARGS)
amroutine->amcanparallel = false;
amroutine->amcaninclude = false;
amroutine->amusemaintenanceworkmem = false;
- amroutine->amhotblocking = true;
amroutine->amparallelvacuumoptions = VACUUM_OPTION_NO_PARALLEL;
amroutine->amkeytype = InvalidOid;
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index 96cbb5de4ef..ae4c424e79f 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -567,61 +567,3 @@ SELECT * FROM brintest_3 WHERE b < '0';
DROP TABLE brintest_3;
RESET enable_seqscan;
--- Test handling of index predicates - updating attributes in predicates
--- 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)
-
--- 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);
-UPDATE brin_hot SET val = -3 WHERE id = 42;
--- ensure pending stats are flushed
-SELECT pg_stat_force_next_flush();
- pg_stat_force_next_flush
---------------------------
-
-(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;
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 6b233ff4c05..5b0ebf090f4 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -554,246 +554,4 @@ SELECT pg_stat_get_live_tuples(:drop_stats_test_subxact_oid);
DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
DROP TABLE prevstats;
------
--- Test that various stats views are being properly populated
------
--- Test that sessions is incremented when a new session is started in pg_stat_database
-SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-\c
-SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database());
- ?column?
-----------
- t
-(1 row)
-
--- Test pg_stat_bgwriter checkpointer-related stats, together with pg_stat_wal
-SELECT checkpoints_req AS rqst_ckpts_before FROM pg_stat_bgwriter \gset
--- Test pg_stat_wal
-SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
-CREATE TABLE test_stats_temp AS SELECT 17;
-DROP TABLE test_stats_temp;
--- Checkpoint twice: The checkpointer reports stats after reporting completion
--- of the checkpoint. But after a second checkpoint we'll see at least the
--- results of the first.
-CHECKPOINT;
-CHECKPOINT;
-SELECT checkpoints_req > :rqst_ckpts_before FROM pg_stat_bgwriter;
- ?column?
-----------
- t
-(1 row)
-
-SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
- ?column?
-----------
- t
-(1 row)
-
------
--- Test that resetting stats works for reset timestamp
------
--- Test that reset_slru with a specified SLRU works.
-SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'CommitTs' \gset
-SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'Notify' \gset
-SELECT pg_stat_reset_slru('CommitTs');
- pg_stat_reset_slru
---------------------
-
-(1 row)
-
-SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'CommitTs';
- ?column?
-----------
- t
-(1 row)
-
-SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'CommitTs' \gset
--- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
-SELECT pg_stat_reset_slru(NULL);
- pg_stat_reset_slru
---------------------
-
-(1 row)
-
-SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'CommitTs';
- ?column?
-----------
- t
-(1 row)
-
-SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'Notify';
- ?column?
-----------
- t
-(1 row)
-
--- Test that reset_shared with archiver specified as the stats type works
-SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-SELECT pg_stat_reset_shared('archiver');
- pg_stat_reset_shared
-----------------------
-
-(1 row)
-
-SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
- ?column?
-----------
- t
-(1 row)
-
-SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
--- Test that reset_shared with bgwriter specified as the stats type works
-SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-SELECT pg_stat_reset_shared('bgwriter');
- pg_stat_reset_shared
-----------------------
-
-(1 row)
-
-SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
- ?column?
-----------
- t
-(1 row)
-
-SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
--- Test that reset_shared with wal specified as the stats type works
-SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-SELECT pg_stat_reset_shared('wal');
- pg_stat_reset_shared
-----------------------
-
-(1 row)
-
-SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
- ?column?
-----------
- t
-(1 row)
-
-SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
--- Test that reset_shared with no specified stats type doesn't reset anything
-SELECT pg_stat_reset_shared(NULL);
- pg_stat_reset_shared
-----------------------
-
-(1 row)
-
-SELECT stats_reset = :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
- ?column?
-----------
- t
-(1 row)
-
-SELECT stats_reset = :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
- ?column?
-----------
- t
-(1 row)
-
-SELECT stats_reset = :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
- ?column?
-----------
- t
-(1 row)
-
--- Test that reset works for pg_stat_database
--- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
-SELECT pg_stat_reset();
- pg_stat_reset
----------------
-
-(1 row)
-
-SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-SELECT pg_stat_reset();
- pg_stat_reset
----------------
-
-(1 row)
-
-SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
- ?column?
-----------
- t
-(1 row)
-
-----
--- pg_stat_get_snapshot_timestamp behavior
-----
-BEGIN;
-SET LOCAL stats_fetch_consistency = snapshot;
--- no snapshot yet, return NULL
-SELECT pg_stat_get_snapshot_timestamp();
- pg_stat_get_snapshot_timestamp
---------------------------------
-
-(1 row)
-
--- any attempt at accessing stats will build snapshot
-SELECT pg_stat_get_function_calls(0);
- pg_stat_get_function_calls
-----------------------------
-
-(1 row)
-
-SELECT pg_stat_get_snapshot_timestamp() >= NOW();
- ?column?
-----------
- t
-(1 row)
-
--- shows NULL again after clearing
-SELECT pg_stat_clear_snapshot();
- pg_stat_clear_snapshot
-------------------------
-
-(1 row)
-
-SELECT pg_stat_get_snapshot_timestamp();
- pg_stat_get_snapshot_timestamp
---------------------------------
-
-(1 row)
-
-COMMIT;
-----
--- pg_stat_have_stats behavior
-----
--- fixed-numbered stats exist
-SELECT pg_stat_have_stats('bgwriter', 0, 0);
- pg_stat_have_stats
---------------------
- t
-(1 row)
-
--- unknown stats kinds error out
-SELECT pg_stat_have_stats('zaphod', 0, 0);
-ERROR: invalid statistics kind: "zaphod"
--- db stats have objoid 0
-SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname = current_database()), 1);
- pg_stat_have_stats
---------------------
- f
-(1 row)
-
-SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname = current_database()), 0);
- pg_stat_have_stats
---------------------
- t
-(1 row)
-
--- ensure that stats accessors handle NULL input correctly
-SELECT pg_stat_get_replication_slot(NULL);
- pg_stat_get_replication_slot
-------------------------------
-
-(1 row)
-
-SELECT pg_stat_get_subscription_stats(NULL);
- pg_stat_get_subscription_stats
---------------------------------
-
-(1 row)
-
-- End of Stats Test
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index eec73b7fbe2..33a30fcf777 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -509,39 +509,3 @@ SELECT * FROM brintest_3 WHERE b < '0';
DROP TABLE brintest_3;
RESET enable_seqscan;
-
--- Test handling of index predicates - updating attributes in predicates
--- 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;
-
-
--- 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);
-
-UPDATE brin_hot SET val = -3 WHERE id = 42;
-
--- ensure pending stats are flushed
-SELECT pg_stat_force_next_flush();
-
-SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid);
-
-DROP TABLE brin_hot;
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 096f00ce8be..3f3cf8fb56b 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -285,115 +285,4 @@ SELECT pg_stat_get_live_tuples(:drop_stats_test_subxact_oid);
DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
DROP TABLE prevstats;
-
-
------
--- Test that various stats views are being properly populated
------
-
--- Test that sessions is incremented when a new session is started in pg_stat_database
-SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-\c
-SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database());
-
--- Test pg_stat_bgwriter checkpointer-related stats, together with pg_stat_wal
-SELECT checkpoints_req AS rqst_ckpts_before FROM pg_stat_bgwriter \gset
-
--- Test pg_stat_wal
-SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
-
-CREATE TABLE test_stats_temp AS SELECT 17;
-DROP TABLE test_stats_temp;
-
--- Checkpoint twice: The checkpointer reports stats after reporting completion
--- of the checkpoint. But after a second checkpoint we'll see at least the
--- results of the first.
-CHECKPOINT;
-CHECKPOINT;
-
-SELECT checkpoints_req > :rqst_ckpts_before FROM pg_stat_bgwriter;
-SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
-
-
------
--- Test that resetting stats works for reset timestamp
------
-
--- Test that reset_slru with a specified SLRU works.
-SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'CommitTs' \gset
-SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'Notify' \gset
-SELECT pg_stat_reset_slru('CommitTs');
-SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'CommitTs';
-SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'CommitTs' \gset
-
--- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
-SELECT pg_stat_reset_slru(NULL);
-SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'CommitTs';
-SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'Notify';
-
--- Test that reset_shared with archiver specified as the stats type works
-SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-SELECT pg_stat_reset_shared('archiver');
-SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
-SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-
--- Test that reset_shared with bgwriter specified as the stats type works
-SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-SELECT pg_stat_reset_shared('bgwriter');
-SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
-SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-
--- Test that reset_shared with wal specified as the stats type works
-SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-SELECT pg_stat_reset_shared('wal');
-SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
-SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-
--- Test that reset_shared with no specified stats type doesn't reset anything
-SELECT pg_stat_reset_shared(NULL);
-SELECT stats_reset = :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
-SELECT stats_reset = :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
-SELECT stats_reset = :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
-
--- Test that reset works for pg_stat_database
-
--- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
-SELECT pg_stat_reset();
-SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-SELECT pg_stat_reset();
-SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
-
-
-----
--- pg_stat_get_snapshot_timestamp behavior
-----
-BEGIN;
-SET LOCAL stats_fetch_consistency = snapshot;
--- no snapshot yet, return NULL
-SELECT pg_stat_get_snapshot_timestamp();
--- any attempt at accessing stats will build snapshot
-SELECT pg_stat_get_function_calls(0);
-SELECT pg_stat_get_snapshot_timestamp() >= NOW();
--- shows NULL again after clearing
-SELECT pg_stat_clear_snapshot();
-SELECT pg_stat_get_snapshot_timestamp();
-COMMIT;
-
-----
--- pg_stat_have_stats behavior
-----
--- fixed-numbered stats exist
-SELECT pg_stat_have_stats('bgwriter', 0, 0);
--- unknown stats kinds error out
-SELECT pg_stat_have_stats('zaphod', 0, 0);
--- db stats have objoid 0
-SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname = current_database()), 1);
-SELECT pg_stat_have_stats('database', (SELECT oid FROM pg_database WHERE datname = current_database()), 0);
-
-
--- ensure that stats accessors handle NULL input correctly
-SELECT pg_stat_get_replication_slot(NULL);
-SELECT pg_stat_get_subscription_stats(NULL);
-
-
-- End of Stats Test
--
2.34.3
