($INBOX_DIR/description missing)
help / color / mirror / Atom feedFrom: Bertrand Drouvot <[email protected]>
Subject: [PATCH v4 4/4] Add FLUSH_MIXED support and implement it for RELATION stats
Date: Mon, 19 Jan 2026 06:27:55 +0000
This commit extends the non transactional stats infrastructure to support statistics
kinds with mixed transaction behavior: some fields are transactional (e.g., tuple
inserts/updates/deletes) while others are non transactional (e.g., sequential scans
blocks read, ...).
It introduces FLUSH_MIXED as a third flush mode type, alongside FLUSH_ANYTIME
and FLUSH_AT_TXN_BOUNDARY. For FLUSH_MIXED kinds, a new flush_anytime_cb callback
enables partial flushing of only the non transactional fields during running
transactions.
Some tests are also added.
Implementation details:
- Add FLUSH_MIXED to PgStat_FlushMode enum
- Add flush_anytime_cb to PgStat_KindInfo for partial flushing callback
- Update pgstat_flush_pending_entries() to call flush_anytime_cb for
FLUSH_MIXED entries when in anytime_only mode
- Keep FLUSH_MIXED entries in the pending list after partial flush, as
transactional fields still need to be flushed at transaction boundary
RELATION stats are making use of FLUSH_MIXED:
- Change RELATION from FLUSH_AT_TXN_BOUNDARY to FLUSH_MIXED
- Implement pgstat_relation_flush_anytime_cb() to flush only read related
stats: numscans, tuples_returned, tuples_fetched, blocks_fetched,
blocks_hit
- Clear these fields after flushing to prevent double counting when
pgstat_relation_flush_cb() runs at transaction commit
- Transactional stats (tuples_inserted, tuples_updated, tuples_deleted,
live_tuples, dead_tuples) remain pending until transaction boundary
The DATABASE kind is also changed from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME, so
that some stats inherited from relations stats are also visible while the transaction
is in progress.
Remark:
We could also imagine adding a new flush_anytime_static_cb() callback for
future FLUSH_MIXED fixed amount stats.
---
doc/src/sgml/monitoring.sgml | 29 ++++++++
src/backend/utils/activity/pgstat.c | 32 ++++++--
src/backend/utils/activity/pgstat_relation.c | 78 ++++++++++++++++++++
src/include/utils/pgstat_internal.h | 9 +++
src/test/isolation/expected/stats.out | 40 ++++++++++
src/test/isolation/expected/stats_1.out | 40 ++++++++++
src/test/isolation/specs/stats.spec | 17 ++++-
7 files changed, 236 insertions(+), 9 deletions(-)
15.3% doc/src/sgml/
44.8% src/backend/utils/activity/
4.6% src/include/utils/
29.4% src/test/isolation/expected/
5.6% src/test/isolation/specs/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b77d189a500..581d6ea7811 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3767,6 +3767,19 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</tgroup>
</table>
+ <note>
+ <para>
+ Some statistics are updated while a transaction is in progress (for example,
+ <structfield>blks_read</structfield>, <structfield>blks_hit</structfield>,
+ <structfield>tup_returned</structfield> and <structfield>tup_fetched</structfield>).
+ Statistics that either do not depend on transactions or require transactional
+ consistency are updated only when the transaction ends. Statistics that require
+ transactional consistency include <structfield>xact_commit</structfield>,
+ <structfield>xact_rollback</structfield>, <structfield>tup_inserted</structfield>,
+ <structfield>tup_updated</structfield> and <structfield>tup_deleted</structfield>.
+ </para>
+ </note>
+
</sect2>
<sect2 id="monitoring-pg-stat-database-conflicts-view">
@@ -4223,6 +4236,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</tgroup>
</table>
+ <note>
+ <para>
+ The <structfield>seq_scan</structfield>, <structfield>last_seq_scan</structfield>,
+ <structfield>seq_tup_read</structfield>, <structfield>idx_scan</structfield>,
+ <structfield>last_idx_scan</structfield> and <structfield>idx_tup_fetch</structfield>
+ are updated while the transactions are in progress.
+ </para>
+ </note>
+
</sect2>
<sect2 id="monitoring-pg-stat-all-indexes-view">
@@ -4404,6 +4426,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage
tuples (see <xref linkend="indexes-multicolumn"/>).
</para>
</note>
+ <note>
+ <para>
+ The <structfield>idx_scan</structfield>, <structfield>last_idx_scan</structfield>,
+ <structfield>idx_tup_read</structfield> and <structfield>idx_tup_fetch</structfield>
+ are updated while the transactions are in progress.
+ </para>
+ </note>
<tip>
<para>
<command>EXPLAIN ANALYZE</command> outputs the total number of index
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index ca08dd49cd7..987bf1c0f6e 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -289,7 +289,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.fixed_amount = false,
.write_to_file = true,
- .flush_mode = FLUSH_AT_TXN_BOUNDARY,
+ .flush_mode = FLUSH_ANYTIME,
/* so pg_stat_database entries can be seen in all databases */
.accessed_across_databases = true,
@@ -307,7 +307,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.fixed_amount = false,
.write_to_file = true,
- .flush_mode = FLUSH_AT_TXN_BOUNDARY,
+ .flush_mode = FLUSH_MIXED,
.shared_size = sizeof(PgStatShared_Relation),
.shared_data_off = offsetof(PgStatShared_Relation, stats),
@@ -315,6 +315,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.pending_size = sizeof(PgStat_TableStatus),
.flush_pending_cb = pgstat_relation_flush_cb,
+ .flush_anytime_cb = pgstat_relation_flush_anytime_cb,
.delete_pending_cb = pgstat_relation_delete_pending_cb,
.reset_timestamp_cb = pgstat_relation_reset_timestamp_cb,
},
@@ -1347,10 +1348,11 @@ pgstat_delete_pending_entry(PgStat_EntryRef *entry_ref)
/*
* Flush out pending variable-numbered stats.
*
- * If anytime_only is true, only flushes FLUSH_ANYTIME entries.
+ * If anytime_only is true, only flushes FLUSH_ANYTIME and FLUSH_MIXED entries,
+ * using flush_anytime_cb for FLUSH_MIXED.
* This is safe to call inside transactions.
*
- * If anytime_only is false, flushes all entries.
+ * If anytime_only is false, flushes all entries using flush_pending_cb.
*/
static bool
pgstat_flush_pending_entries(bool nowait, bool anytime_only)
@@ -1378,6 +1380,7 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only)
PgStat_Kind kind = key.kind;
const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind);
bool did_flush;
+ bool is_partial_flush = false;
dlist_node *next;
Assert(!kind_info->fixed_amount);
@@ -1397,8 +1400,21 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only)
continue;
}
- /* flush the stats, if possible */
- did_flush = kind_info->flush_pending_cb(entry_ref, nowait);
+ /* flush the stats (with the appropriate callback), if possible */
+ if (anytime_only &&
+ kind_info->flush_mode == FLUSH_MIXED &&
+ kind_info->flush_anytime_cb != NULL)
+ {
+ /* Partial flush of non-transactional fields only */
+ did_flush = kind_info->flush_anytime_cb(entry_ref, nowait);
+ is_partial_flush = true;
+ }
+ else
+ {
+ /* Full flush */
+ did_flush = kind_info->flush_pending_cb(entry_ref, nowait);
+ is_partial_flush = false;
+ }
Assert(did_flush || nowait);
@@ -1408,8 +1424,8 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only)
else
next = NULL;
- /* if successfully flushed, remove entry */
- if (did_flush)
+ /* if successfull non partial flush, remove entry */
+ if (did_flush && !is_partial_flush)
pgstat_delete_pending_entry(entry_ref);
else
have_pending = true;
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index feae2ae5f44..d6b799c4354 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -887,6 +887,84 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
return true;
}
+/*
+ * Flush only non-transactional relation stats.
+ *
+ * This is called periodically during running transactions to make some
+ * statistics visible without waiting for the transaction to finish.
+ *
+ * Transactional stats (inserts/updates/deletes and their effects on live/dead
+ * tuple counts) remain in pending until the transaction ends, at which point
+ * pgstat_relation_flush_cb() will flush them.
+ *
+ * If nowait is true and the lock could not be immediately acquired, returns
+ * false without flushing the entry. Otherwise returns true.
+ */
+bool
+pgstat_relation_flush_anytime_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
+ Oid dboid;
+ PgStat_TableStatus *lstats; /* pending stats entry */
+ PgStatShared_Relation *shtabstats;
+ PgStat_StatTabEntry *tabentry; /* table entry of shared stats */
+ PgStat_StatDBEntry *dbentry; /* pending database entry */
+
+ dboid = entry_ref->shared_entry->key.dboid;
+ lstats = (PgStat_TableStatus *) entry_ref->pending;
+ shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats;
+
+ /*
+ * Check if there are any non-transactional stats to flush. Avoid
+ * unnecessarily locking the entry if nothing accumulated.
+ */
+ if (!(lstats->counts.numscans > 0 ||
+ lstats->counts.tuples_returned > 0 ||
+ lstats->counts.tuples_fetched > 0 ||
+ lstats->counts.blocks_fetched > 0 ||
+ lstats->counts.blocks_hit > 0))
+ return true;
+
+ if (!pgstat_lock_entry(entry_ref, nowait))
+ return false;
+
+ /* Add only the non-transactional values to the shared entry */
+ tabentry = &shtabstats->stats;
+
+ tabentry->numscans += lstats->counts.numscans;
+ if (lstats->counts.numscans)
+ {
+ TimestampTz t = GetCurrentTimestamp();
+
+ if (t > tabentry->lastscan)
+ tabentry->lastscan = t;
+ }
+ tabentry->tuples_returned += lstats->counts.tuples_returned;
+ tabentry->tuples_fetched += lstats->counts.tuples_fetched;
+ tabentry->blocks_fetched += lstats->counts.blocks_fetched;
+ tabentry->blocks_hit += lstats->counts.blocks_hit;
+
+ pgstat_unlock_entry(entry_ref);
+
+ /* Also update the corresponding fields in database stats */
+ dbentry = pgstat_prep_database_pending(dboid);
+ dbentry->tuples_returned += lstats->counts.tuples_returned;
+ dbentry->tuples_fetched += lstats->counts.tuples_fetched;
+ dbentry->blocks_fetched += lstats->counts.blocks_fetched;
+ dbentry->blocks_hit += lstats->counts.blocks_hit;
+
+ /*
+ * Clear the flushed fields from pending stats to prevent double-counting
+ * when pgstat_relation_flush_cb() runs at transaction boundary.
+ */
+ lstats->counts.numscans = 0;
+ lstats->counts.tuples_returned = 0;
+ lstats->counts.tuples_fetched = 0;
+ lstats->counts.blocks_fetched = 0;
+ lstats->counts.blocks_hit = 0;
+
+ return true;
+}
+
void
pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref)
{
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 9ca39ea9a9a..f91bbfd460f 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -236,6 +236,8 @@ typedef enum PgStat_FlushMode
* transaction boundary */
FLUSH_ANYTIME, /* All fields can be flushed anytime,
* including within transactions */
+ FLUSH_MIXED, /* Mix of fields that can be flushed anytime
+ * or only at transaction boundary */
} PgStat_FlushMode;
/*
@@ -271,6 +273,12 @@ typedef struct PgStat_KindInfo
*/
PgStat_FlushMode flush_mode;
+ /*
+ * For FLUSH_MIXED kinds: callback to flush only some fields. If NULL for
+ * a MIXED kind, treated as FLUSH_AT_TXN_BOUNDARY.
+ */
+ bool (*flush_anytime_cb) (PgStat_EntryRef *entry_ref, bool nowait);
+
/*
* The size of an entry in the shared stats hash table (pointed to by
* PgStatShared_HashEntry->body). For fixed-numbered statistics, this is
@@ -783,6 +791,7 @@ extern void AtPrepare_PgStat_Relations(PgStat_SubXactStatus *xact_state);
extern void PostPrepare_PgStat_Relations(PgStat_SubXactStatus *xact_state);
extern bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
+extern bool pgstat_relation_flush_anytime_cb(PgStat_EntryRef *entry_ref, bool nowait);
extern void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref);
extern void pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts);
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index cfad309ccf3..6d62b30e4a7 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -2245,6 +2245,46 @@ seq_scan|seq_tup_read|n_tup_ins|n_tup_upd|n_tup_del|n_live_tup|n_dead_tup|vacuum
(1 row)
+starting permutation: s2_begin s2_table_select s1_sleep s1_table_stats s2_table_drop s2_commit
+pg_stat_force_next_flush
+------------------------
+
+(1 row)
+
+step s2_begin: BEGIN;
+step s2_table_select: SELECT * FROM test_stat_tab ORDER BY key, value;
+key|value
+---+-----
+k0 | 1
+(1 row)
+
+step s1_sleep: SELECT pg_sleep(1.5);
+pg_sleep
+--------
+
+(1 row)
+
+step s1_table_stats:
+ SELECT
+ pg_stat_get_numscans(tso.oid) AS seq_scan,
+ pg_stat_get_tuples_returned(tso.oid) AS seq_tup_read,
+ pg_stat_get_tuples_inserted(tso.oid) AS n_tup_ins,
+ pg_stat_get_tuples_updated(tso.oid) AS n_tup_upd,
+ pg_stat_get_tuples_deleted(tso.oid) AS n_tup_del,
+ pg_stat_get_live_tuples(tso.oid) AS n_live_tup,
+ pg_stat_get_dead_tuples(tso.oid) AS n_dead_tup,
+ pg_stat_get_vacuum_count(tso.oid) AS vacuum_count
+ FROM test_stat_oid AS tso
+ WHERE tso.name = 'test_stat_tab'
+
+seq_scan|seq_tup_read|n_tup_ins|n_tup_upd|n_tup_del|n_live_tup|n_dead_tup|vacuum_count
+--------+------------+---------+---------+---------+----------+----------+------------
+ 1| 1| 1| 0| 0| 1| 0| 0
+(1 row)
+
+step s2_table_drop: DROP TABLE test_stat_tab;
+step s2_commit: COMMIT;
+
starting permutation: s1_track_counts_off s1_table_stats s1_track_counts_on
pg_stat_force_next_flush
------------------------
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index e1d937784cb..2fade10e817 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -2253,6 +2253,46 @@ seq_scan|seq_tup_read|n_tup_ins|n_tup_upd|n_tup_del|n_live_tup|n_dead_tup|vacuum
(1 row)
+starting permutation: s2_begin s2_table_select s1_sleep s1_table_stats s2_table_drop s2_commit
+pg_stat_force_next_flush
+------------------------
+
+(1 row)
+
+step s2_begin: BEGIN;
+step s2_table_select: SELECT * FROM test_stat_tab ORDER BY key, value;
+key|value
+---+-----
+k0 | 1
+(1 row)
+
+step s1_sleep: SELECT pg_sleep(1.5);
+pg_sleep
+--------
+
+(1 row)
+
+step s1_table_stats:
+ SELECT
+ pg_stat_get_numscans(tso.oid) AS seq_scan,
+ pg_stat_get_tuples_returned(tso.oid) AS seq_tup_read,
+ pg_stat_get_tuples_inserted(tso.oid) AS n_tup_ins,
+ pg_stat_get_tuples_updated(tso.oid) AS n_tup_upd,
+ pg_stat_get_tuples_deleted(tso.oid) AS n_tup_del,
+ pg_stat_get_live_tuples(tso.oid) AS n_live_tup,
+ pg_stat_get_dead_tuples(tso.oid) AS n_dead_tup,
+ pg_stat_get_vacuum_count(tso.oid) AS vacuum_count
+ FROM test_stat_oid AS tso
+ WHERE tso.name = 'test_stat_tab'
+
+seq_scan|seq_tup_read|n_tup_ins|n_tup_upd|n_tup_del|n_live_tup|n_dead_tup|vacuum_count
+--------+------------+---------+---------+---------+----------+----------+------------
+ 0| 0| 1| 0| 0| 1| 0| 0
+(1 row)
+
+step s2_table_drop: DROP TABLE test_stat_tab;
+step s2_commit: COMMIT;
+
starting permutation: s1_track_counts_off s1_table_stats s1_track_counts_on
pg_stat_force_next_flush
------------------------
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index da16710da0f..a4084efda49 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -50,6 +50,8 @@ step s1_rollback { ROLLBACK; }
step s1_prepare_a { PREPARE TRANSACTION 'a'; }
step s1_commit_prepared_a { COMMIT PREPARED 'a'; }
step s1_rollback_prepared_a { ROLLBACK PREPARED 'a'; }
+# Has to be greater than session 2 stats_flush_interval
+step s1_sleep { SELECT pg_sleep(1.5); }
# Function stats steps
step s1_ff { SELECT pg_stat_force_next_flush(); }
@@ -132,12 +134,16 @@ step s1_slru_check_stats {
session s2
-setup { SET stats_fetch_consistency = 'none'; }
+setup {
+ SET stats_fetch_consistency = 'none';
+ SET stats_flush_interval = '1s';
+}
step s2_begin { BEGIN; }
step s2_commit { COMMIT; }
step s2_commit_prepared_a { COMMIT PREPARED 'a'; }
step s2_rollback_prepared_a { ROLLBACK PREPARED 'a'; }
step s2_ff { SELECT pg_stat_force_next_flush(); }
+step s2_table_drop { DROP TABLE test_stat_tab; }
# Function stats steps
step s2_track_funcs_all { SET track_functions = 'all'; }
@@ -435,6 +441,15 @@ permutation
s1_table_drop
s1_table_stats
+### Check that some stats are updated (seq_scan and seq_tup_read)
+### while the transaction is still running
+permutation
+ s2_begin
+ s2_table_select
+ s1_sleep
+ s1_table_stats
+ s2_table_drop
+ s2_commit
### Check that we don't count changes with track counts off, but allow access
### to prior stats
--
2.34.1
--wR/mWGXukst4NraF--
view thread (27+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v4 4/4] Add FLUSH_MIXED support and implement it for RELATION stats
In-Reply-To: <no-message-id-884932@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox