From: Bertrand Drouvot Date: Mon, 19 Jan 2026 06:27:55 +0000 Subject: [PATCH v5 4/4] Add FLUSH_MIXED support and implement it for RELATION stats 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 - Add pgstat_report_mixed_anytime, a new global variable to track when the ANYTIME_STATS_UPDATE_TIMEOUT needs to be enabled for FLUSH_MIXED mode 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 | 44 ++++++++++--- src/backend/utils/activity/pgstat_relation.c | 67 ++++++++++++++++++++ src/include/pgstat.h | 10 +++ src/include/utils/pgstat_internal.h | 11 ++++ src/test/isolation/expected/stats.out | 40 ++++++++++++ src/test/isolation/expected/stats_1.out | 40 ++++++++++++ src/test/isolation/specs/stats.spec | 17 ++++- 8 files changed, 247 insertions(+), 11 deletions(-) 14.3% doc/src/sgml/ 43.1% src/backend/utils/activity/ 4.9% src/include/utils/ 4.6% src/include/ 27.5% src/test/isolation/expected/ 5.3% 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 + + + Some statistics are updated while a transaction is in progress (for example, + blks_read, blks_hit, + tup_returned and tup_fetched). + 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 xact_commit, + xact_rollback, tup_inserted, + tup_updated and tup_deleted. + + + @@ -4223,6 +4236,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + + The seq_scan, last_seq_scan, + seq_tup_read, idx_scan, + last_idx_scan and idx_tup_fetch + are updated while the transactions are in progress. + + + @@ -4404,6 +4426,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage tuples (see ). + + + The idx_scan, last_idx_scan, + idx_tup_read and idx_tup_fetch + are updated while the transactions are in progress. + + EXPLAIN ANALYZE outputs the total number of index diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index dd174129403..d54ccb95c6a 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -219,6 +219,12 @@ PgStat_LocalState pgStatLocal; */ bool pgstat_report_fixed = false; +/* + * Track pending reports for mixed anytime stats, used by + * pgstat_report_anytime_stat(). + */ +bool pgstat_report_mixed_anytime = false; + /* ---------- * Local data * @@ -289,7 +295,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 +313,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 +321,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, }, @@ -1303,8 +1310,8 @@ pgstat_prep_pending_entry(PgStat_Kind kind, Oid dboid, uint64 objid, bool *creat dlist_push_tail(&pgStatPending, &entry_ref->pending_node); /* Schedule next anytime stats update timeout */ - if (kind_info->flush_mode == FLUSH_ANYTIME && IsUnderPostmaster && - !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + if ((kind_info->flush_mode == FLUSH_ANYTIME || pgstat_report_mixed_anytime) && + IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); } @@ -1353,10 +1360,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) @@ -1384,6 +1392,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); @@ -1403,8 +1412,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); @@ -1414,8 +1436,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; @@ -2201,6 +2223,8 @@ pgstat_report_anytime_stat(bool force) /* Flush stats outside of transaction boundary */ pgstat_flush_pending_entries(nowait, true); pgstat_flush_fixed_stats(nowait, true); + + pgstat_report_mixed_anytime = false; } /* diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index feae2ae5f44..ae8778d8e39 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -887,6 +887,73 @@ 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; + + 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/pgstat.h b/src/include/pgstat.h index e0f222695bf..66cc7745498 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -38,6 +38,9 @@ /* Minimum interval non-forced stats flushes */ #define PGSTAT_MIN_INTERVAL 1000 +/* Track if mixed anytime stats need to be flushed */ +extern PGDLLIMPORT bool pgstat_report_mixed_anytime; + /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -693,36 +696,43 @@ extern void pgstat_report_analyze(Relation rel, #define pgstat_count_heap_scan(rel) \ do { \ + pgstat_report_mixed_anytime = true; \ if (pgstat_should_count_relation(rel)) \ (rel)->pgstat_info->counts.numscans++; \ } while (0) #define pgstat_count_heap_getnext(rel) \ do { \ + pgstat_report_mixed_anytime = true; \ if (pgstat_should_count_relation(rel)) \ (rel)->pgstat_info->counts.tuples_returned++; \ } while (0) #define pgstat_count_heap_fetch(rel) \ do { \ + pgstat_report_mixed_anytime = true; \ if (pgstat_should_count_relation(rel)) \ (rel)->pgstat_info->counts.tuples_fetched++; \ } while (0) #define pgstat_count_index_scan(rel) \ do { \ + pgstat_report_mixed_anytime = true; \ if (pgstat_should_count_relation(rel)) \ (rel)->pgstat_info->counts.numscans++; \ } while (0) #define pgstat_count_index_tuples(rel, n) \ do { \ + pgstat_report_mixed_anytime = true; \ if (pgstat_should_count_relation(rel)) \ (rel)->pgstat_info->counts.tuples_returned += (n); \ } while (0) #define pgstat_count_buffer_read(rel) \ do { \ + pgstat_report_mixed_anytime = true; \ if (pgstat_should_count_relation(rel)) \ (rel)->pgstat_info->counts.blocks_fetched++; \ } while (0) #define pgstat_count_buffer_hit(rel) \ do { \ + pgstat_report_mixed_anytime = true; \ if (pgstat_should_count_relation(rel)) \ (rel)->pgstat_info->counts.blocks_hit++; \ } while (0) diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index a9190078d0e..db4d86cf31c 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 @@ -784,6 +792,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); @@ -883,6 +892,8 @@ extern void pgstat_create_transactional(PgStat_Kind kind, Oid dboid, uint64 obji */ extern PGDLLIMPORT bool pgstat_report_fixed; +/* Track if mixed anytime stats need to be flushed */ + /* Backend-local stats state */ extern PGDLLIMPORT PgStat_LocalState pgStatLocal; 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 --WLtTrIUEfQDmERxy--