agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v11 2/7] Row pattern recognition patch (parse/analysis). 7+ messages / 2 participants [nested] [flat]
* [PATCH v11 2/7] Row pattern recognition patch (parse/analysis). @ 2023-11-08 06:57 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Tatsuo Ishii @ 2023-11-08 06:57 UTC (permalink / raw) --- src/backend/parser/parse_agg.c | 7 + src/backend/parser/parse_clause.c | 278 +++++++++++++++++++++++++++++- src/backend/parser/parse_expr.c | 4 + src/backend/parser/parse_func.c | 3 + 4 files changed, 291 insertions(+), 1 deletion(-) diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 9bbad33fbd..28fb5e0d71 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -575,6 +575,10 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) errkind = true; break; + case EXPR_KIND_RPR_DEFINE: + errkind = true; + break; + /* * There is intentionally no default: case here, so that the * compiler will warn if we add a new ParseExprKind without @@ -964,6 +968,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_RPR_DEFINE: + errkind = true; + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 334b9b42bd..c5d3c10683 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -100,7 +100,10 @@ static WindowClause *findWindowClause(List *wclist, const char *name); static Node *transformFrameOffset(ParseState *pstate, int frameOptions, Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc, Node *clause); - +static void transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist); +static List *transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist); +static void transformPatternClause(ParseState *pstate, WindowClause *wc, WindowDef *windef); +static List *transformMeasureClause(ParseState *pstate, WindowClause *wc, WindowDef *windef); /* * transformFromClause - @@ -2950,6 +2953,10 @@ transformWindowDefinitions(ParseState *pstate, rangeopfamily, rangeopcintype, &wc->endInRangeFunc, windef->endOffset); + + /* Process Row Pattern Recognition related clauses */ + transformRPR(pstate, wc, windef, targetlist); + wc->runCondition = NIL; wc->winref = winref; @@ -3815,3 +3822,272 @@ transformFrameOffset(ParseState *pstate, int frameOptions, return node; } + +/* + * transformRPR + * Process Row Pattern Recognition related clauses + */ +static void +transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist) +{ + /* + * Window definition exists? + */ + if (windef == NULL) + return; + + /* + * Row Pattern Common Syntax clause exists? + */ + if (windef->rpCommonSyntax == NULL) + return; + + /* Check Frame option. Frame must start at current row */ + if ((wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("FRAME must start at current row when row patttern recognition is used"))); + + /* Transform AFTER MACH SKIP TO clause */ + wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo; + + /* Transform SEEK or INITIAL clause */ + wc->initial = windef->rpCommonSyntax->initial; + + /* Transform DEFINE clause into list of TargetEntry's */ + wc->defineClause = transformDefineClause(pstate, wc, windef, targetlist); + + /* Check PATTERN clause and copy to patternClause */ + transformPatternClause(pstate, wc, windef); + + /* Transform MEASURE clause */ + transformMeasureClause(pstate, wc, windef); +} + +/* + * transformDefineClause Process DEFINE clause and transform ResTarget into + * list of TargetEntry. + * + * XXX we only support column reference in row pattern definition search + * condition, e.g. "price". <row pattern definition variable name>.<column + * reference> is not supported, e.g. "A.price". + */ +static List * +transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist) +{ + /* DEFINE variable name initials */ + static char *defineVariableInitials = "abcdefghijklmnopqrstuvwxyz"; + + ListCell *lc, *l; + ResTarget *restarget, *r; + List *restargets; + List *defineClause; + char *name; + int initialLen; + int i; + + /* + * If Row Definition Common Syntax exists, DEFINE clause must exist. + * (the raw parser should have already checked it.) + */ + Assert(windef->rpCommonSyntax->rpDefs != NULL); + + /* + * Check and add "A AS A IS TRUE" if pattern variable is missing in DEFINE + * per the SQL standard. + */ + restargets = NIL; + foreach(lc, windef->rpCommonSyntax->rpPatterns) + { + A_Expr *a; + bool found = false; + + if (!IsA(lfirst(lc), A_Expr)) + ereport(ERROR, + errmsg("node type is not A_Expr")); + + a = (A_Expr *)lfirst(lc); + name = strVal(a->lexpr); + + foreach(l, windef->rpCommonSyntax->rpDefs) + { + restarget = (ResTarget *)lfirst(l); + + if (!strcmp(restarget->name, name)) + { + found = true; + break; + } + } + + if (!found) + { + /* + * "name" is missing. So create "name AS name IS TRUE" ResTarget + * node and add it to the temporary list. + */ + A_Const *n; + + restarget = makeNode(ResTarget); + n = makeNode(A_Const); + n->val.boolval.type = T_Boolean; + n->val.boolval.boolval = true; + n->location = -1; + restarget->name = pstrdup(name); + restarget->indirection = NIL; + restarget->val = (Node *)n; + restarget->location = -1; + restargets = lappend((List *)restargets, restarget); + } + } + + if (list_length(restargets) >= 1) + { + /* add missing DEFINEs */ + windef->rpCommonSyntax->rpDefs = list_concat(windef->rpCommonSyntax->rpDefs, + restargets); + list_free(restargets); + } + + /* + * Check for duplicate row pattern definition variables. The standard + * requires that no two row pattern definition variable names shall be + * equivalent. + */ + restargets = NIL; + foreach(lc, windef->rpCommonSyntax->rpDefs) + { + restarget = (ResTarget *)lfirst(lc); + name = restarget->name; + + /* + * Add DEFINE expression (Restarget->val) to the targetlist as a + * TargetEntry if it does not exist yet. Planner will add the column + * ref var node to the outer plan's target list later on. This makes + * DEFINE expression could access the outer tuple while evaluating + * PATTERN. + * + * XXX: adding whole expressions of DEFINE to the plan.targetlist is + * not so good, because it's not necessary to evalute the expression + * in the target list while running the plan. We should extract the + * var nodes only then add them to the plan.targetlist. + */ + findTargetlistEntrySQL99(pstate, (Node *)restarget->val, targetlist, EXPR_KIND_RPR_DEFINE); + + /* + * Make sure that the row pattern definition search condition is a + * boolean expression. + */ + transformWhereClause(pstate, restarget->val, + EXPR_KIND_RPR_DEFINE, "DEFINE"); + + foreach(l, restargets) + { + char *n; + + r = (ResTarget *) lfirst(l); + n = r->name; + + if (!strcmp(n, name)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("row pattern definition variable name \"%s\" appears more than once in DEFINE clause", + name), + parser_errposition(pstate, exprLocation((Node *)r)))); + } + restargets = lappend(restargets, restarget); + } + list_free(restargets); + + /* + * Create list of row pattern DEFINE variable name's initial. + * We assign [a-z] to them (up to 26 variable names are allowed). + */ + restargets = NIL; + i = 0; + initialLen = strlen(defineVariableInitials); + + foreach(lc, windef->rpCommonSyntax->rpDefs) + { + char initial[2]; + + restarget = (ResTarget *)lfirst(lc); + name = restarget->name; + + if (i >= initialLen) + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("number of row pattern definition variable names exceeds %d", initialLen), + parser_errposition(pstate, exprLocation((Node *)restarget)))); + } + initial[0] = defineVariableInitials[i++]; + initial[1] = '\0'; + wc->defineInitial = lappend(wc->defineInitial, makeString(pstrdup(initial))); + } + + defineClause = transformTargetList(pstate, windef->rpCommonSyntax->rpDefs, + EXPR_KIND_RPR_DEFINE); + + /* mark column origins */ + markTargetListOrigins(pstate, defineClause); + + /* mark all nodes in the DEFINE clause tree with collation information */ + assign_expr_collations(pstate, (Node *)defineClause); + + return defineClause; +} + +/* + * transformPatternClause + * Process PATTERN clause and return PATTERN clause in the raw parse tree + */ +static void +transformPatternClause(ParseState *pstate, WindowClause *wc, WindowDef *windef) +{ + ListCell *lc; + + /* + * Row Pattern Common Syntax clause exists? + */ + if (windef->rpCommonSyntax == NULL) + return; + + wc->patternVariable = NIL; + wc->patternRegexp = NIL; + foreach(lc, windef->rpCommonSyntax->rpPatterns) + { + A_Expr *a; + char *name; + char *regexp; + + if (!IsA(lfirst(lc), A_Expr)) + ereport(ERROR, + errmsg("node type is not A_Expr")); + + a = (A_Expr *)lfirst(lc); + name = strVal(a->lexpr); + + wc->patternVariable = lappend(wc->patternVariable, makeString(pstrdup(name))); + regexp = strVal(lfirst(list_head(a->name))); + wc->patternRegexp = lappend(wc->patternRegexp, makeString(pstrdup(regexp))); + } +} + +/* + * transformMeasureClause + * Process MEASURE clause + * XXX MEASURE clause is not supported yet + */ +static List * +transformMeasureClause(ParseState *pstate, WindowClause *wc, WindowDef *windef) +{ + if (windef->rowPatternMeasures == NIL) + return NIL; + + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("%s","MEASURE clause is not supported yet"), + parser_errposition(pstate, exprLocation((Node *)windef->rowPatternMeasures)))); + return NIL; +} diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 64c582c344..18b58ac263 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -557,6 +557,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_COPY_WHERE: case EXPR_KIND_GENERATED_COLUMN: case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_RPR_DEFINE: /* okay */ break; @@ -1770,6 +1771,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_VALUES: case EXPR_KIND_VALUES_SINGLE: case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_RPR_DEFINE: /* okay */ break; case EXPR_KIND_CHECK_CONSTRAINT: @@ -3149,6 +3151,8 @@ ParseExprKindName(ParseExprKind exprKind) return "GENERATED AS"; case EXPR_KIND_CYCLE_MARK: return "CYCLE"; + case EXPR_KIND_RPR_DEFINE: + return "DEFINE"; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 6c29471bb3..086431f91b 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2656,6 +2656,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_RPR_DEFINE: + errkind = true; + break; /* * There is intentionally no default: case here, so that the -- 2.25.1 ----Next_Part(Wed_Nov__8_16_37_05_2023_872)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v11-0003-Row-pattern-recognition-patch-planner.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v9 5/5] Change RELATION and DATABASE stats to anytime flush @ 2026-01-19 06:27 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-01-19 06:27 UTC (permalink / raw) This commit allows mixing fields with different transaction behavior within the same RELATION or DATABASE statistics kind: some fields are transactional (e.g., tuple inserts/updates/deletes) while others are non-transactional (e.g., sequential scans, blocks read). It modifies the relation flush callback to handle the anytime_only parameter introduced in commit <nnnn>. Implementation details: - Change RELATION from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Change DATABASE from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Add a is_partial parameter to flush_pending_cb() to be able to distinguish partial flushes in pgstat_flush_pending_entries() - Modify pgstat_relation_flush_cb() to handle anytime_only parameter: when true, then flush only non-transactional stats and when false, then flush all the stats. When set to true, it clears flushed fields from pending stats to prevent double-counting at transaction boundary DATABASE stats inherit the anytime flush behavior so that relation-derived stats (tuples_returned, tuples_fetched, blocks_fetched, blocks_hit) are visible while transactions are in progress. Tests are added to verify the anytime flush behavior for mixed fields. --- doc/src/sgml/monitoring.sgml | 37 ++++++- src/backend/utils/activity/pgstat.c | 15 +-- src/backend/utils/activity/pgstat_database.c | 6 +- src/backend/utils/activity/pgstat_function.c | 6 +- src/backend/utils/activity/pgstat_relation.c | 92 ++++++++++++---- .../utils/activity/pgstat_subscription.c | 6 +- src/include/pgstat.h | 27 ++++- src/include/utils/pgstat_internal.h | 16 ++- src/test/isolation/expected/stats.out | 102 ++++++++++++++++++ src/test/isolation/expected/stats_1.out | 102 ++++++++++++++++++ src/test/isolation/specs/stats.spec | 27 ++++- .../test_custom_stats/test_custom_var_stats.c | 9 +- 12 files changed, 404 insertions(+), 41 deletions(-) 11.7% doc/src/sgml/ 26.8% src/backend/utils/activity/ 4.2% src/include/utils/ 5.4% src/include/ 45.1% src/test/isolation/expected/ 4.7% src/test/isolation/specs/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..f2321b631b0 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"> @@ -3956,8 +3969,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last sequential scan on this table, based on the - most recent transaction stop time + The approximate time of the last sequential scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -3984,8 +3997,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last index scan on this table, based on the - most recent transaction stop time + The approximate time of the last index scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -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 578c575dbfb..273258a965a 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -297,7 +297,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, @@ -315,7 +315,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, .shared_size = sizeof(PgStatShared_Relation), .shared_data_off = offsetof(PgStatShared_Relation, stats), @@ -1353,7 +1353,8 @@ 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 entries. For entries + * that support it, the callback may flush only non-transactional fields. * This is safe to call inside transactions. * * If anytime_only is false, flushes all entries. @@ -1384,6 +1385,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); @@ -1404,7 +1406,8 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) } /* flush the stats, if possible */ - did_flush = kind_info->flush_pending_cb(entry_ref, nowait, anytime_only); + did_flush = kind_info->flush_pending_cb(entry_ref, nowait, + anytime_only, &is_partial_flush); Assert(did_flush || nowait); @@ -1414,8 +1417,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_database.c b/src/backend/utils/activity/pgstat_database.c index 8e86df60461..59dd0790fd7 100644 --- a/src/backend/utils/activity/pgstat_database.c +++ b/src/backend/utils/activity/pgstat_database.c @@ -435,7 +435,8 @@ pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStatShared_Database *sharedent; PgStat_StatDBEntry *pendingent; @@ -443,6 +444,9 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o pendingent = (PgStat_StatDBEntry *) entry_ref->pending; sharedent = (PgStatShared_Database *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; diff --git a/src/backend/utils/activity/pgstat_function.c b/src/backend/utils/activity/pgstat_function.c index 5ba4958382f..44193c93fc7 100644 --- a/src/backend/utils/activity/pgstat_function.c +++ b/src/backend/utils/activity/pgstat_function.c @@ -190,7 +190,8 @@ pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_FunctionCounts *localent; PgStatShared_Function *shfuncent; @@ -200,6 +201,9 @@ pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o localent = (PgStat_FunctionCounts *) entry_ref->pending; shfuncent = (PgStatShared_Function *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index ae2952cae89..62363dacfe1 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -47,7 +47,19 @@ static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_lev static void ensure_tabstat_xact_level(PgStat_TableStatus *pgstat_info); static void save_truncdrop_counters(PgStat_TableXactStatus *trans, bool is_drop); static void restore_truncdrop_counters(PgStat_TableXactStatus *trans); +static void flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, + PgStat_TableCounts *counts, bool anytime_only); +/* + * Update database statistics with non-transactional stats. + */ +#define UPDATE_DATABASE_ANYTIME_STATS(dbentry, counts) \ + do { \ + (dbentry)->tuples_returned += (counts)->tuples_returned; \ + (dbentry)->tuples_fetched += (counts)->tuples_fetched; \ + (dbentry)->blocks_fetched += (counts)->blocks_fetched; \ + (dbentry)->blocks_hit += (counts)->blocks_hit; \ + } while (0) /* * Copy stats between relations. This is used for things like REINDEX @@ -789,6 +801,29 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, rec->tuples_inserted + rec->tuples_updated; } +/* + * Helper function to flush non-transactional statistics. + */ +static void +flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, PgStat_TableCounts *counts, + bool anytime_only) +{ + TimestampTz t; + + tabentry->numscans += counts->numscans; + if (counts->numscans) + { + t = anytime_only ? GetCurrentTimestamp() : GetCurrentTransactionStopTimestamp(); + if (t > tabentry->lastscan) + tabentry->lastscan = t; + } + + tabentry->tuples_returned += counts->tuples_returned; + tabentry->tuples_fetched += counts->tuples_fetched; + tabentry->blocks_fetched += counts->blocks_fetched; + tabentry->blocks_hit += counts->blocks_hit; +} + /* * Flush out pending stats for the entry * @@ -797,9 +832,17 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, * * Some of the stats are copied to the corresponding pending database stats * entry when successfully flushing. + * + * If anytime_only is true, only non-transactional fields are flushed + * (numscans, tuples_returned, tuples_fetched, blocks_fetched, blocks_hit). + * Transactional fields remain pending until transaction boundary. + * + * Some of the stats are copied to the corresponding pending database stats + * entry when successfully flushing. */ bool -pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { Oid dboid; PgStat_TableStatus *lstats; /* pending stats entry */ @@ -807,12 +850,13 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o PgStat_StatTabEntry *tabentry; /* table entry of shared stats */ PgStat_StatDBEntry *dbentry; /* pending database entry */ - Assert(!anytime_only); - dboid = entry_ref->shared_entry->key.dboid; lstats = (PgStat_TableStatus *) entry_ref->pending; shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats; + /* this is a partial flush if in anytime only mode */ + *is_partial = anytime_only; + /* * Ignore entries that didn't accumulate any actual counts, such as * indexes that were opened by the planner but not used. @@ -824,19 +868,36 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o if (!pgstat_lock_entry(entry_ref, nowait)) return false; - /* add the values to the shared entry. */ tabentry = &shtabstats->stats; - tabentry->numscans += lstats->counts.numscans; - if (lstats->counts.numscans) + if (anytime_only) { - TimestampTz t = GetCurrentTransactionStopTimestamp(); - if (t > tabentry->lastscan) - tabentry->lastscan = t; + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, true); + + pgstat_unlock_entry(entry_ref); + + /* Also update the corresponding fields in database stats */ + dbentry = pgstat_prep_database_pending(dboid); + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + + /* + * Clear the flushed fields from pending stats to prevent + * double-counting when we flush all fields 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; } - tabentry->tuples_returned += lstats->counts.tuples_returned; - tabentry->tuples_fetched += lstats->counts.tuples_fetched; + + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, false); + tabentry->tuples_inserted += lstats->counts.tuples_inserted; tabentry->tuples_updated += lstats->counts.tuples_updated; tabentry->tuples_deleted += lstats->counts.tuples_deleted; @@ -866,9 +927,6 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o */ tabentry->ins_since_vacuum += lstats->counts.tuples_inserted; - tabentry->blocks_fetched += lstats->counts.blocks_fetched; - tabentry->blocks_hit += lstats->counts.blocks_hit; - /* Clamp live_tuples in case of negative delta_live_tuples */ tabentry->live_tuples = Max(tabentry->live_tuples, 0); /* Likewise for dead_tuples */ @@ -878,13 +936,11 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o /* The entry was successfully flushed, add the same to database stats */ dbentry = pgstat_prep_database_pending(dboid); - dbentry->tuples_returned += lstats->counts.tuples_returned; - dbentry->tuples_fetched += lstats->counts.tuples_fetched; + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + dbentry->tuples_inserted += lstats->counts.tuples_inserted; dbentry->tuples_updated += lstats->counts.tuples_updated; dbentry->tuples_deleted += lstats->counts.tuples_deleted; - dbentry->blocks_fetched += lstats->counts.blocks_fetched; - dbentry->blocks_hit += lstats->counts.blocks_hit; return true; } diff --git a/src/backend/utils/activity/pgstat_subscription.c b/src/backend/utils/activity/pgstat_subscription.c index c4614817966..43fec86c635 100644 --- a/src/backend/utils/activity/pgstat_subscription.c +++ b/src/backend/utils/activity/pgstat_subscription.c @@ -116,7 +116,8 @@ pgstat_fetch_stat_subscription(Oid subid) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_BackendSubEntry *localent; PgStatShared_Subscription *shsubent; @@ -126,6 +127,9 @@ pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anyti localent = (PgStat_BackendSubEntry *) entry_ref->pending; shsubent = (PgStatShared_Subscription *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 7829c563316..87cbb539d7c 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -21,6 +21,7 @@ #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/pgstat_kind.h" #include "utils/relcache.h" +#include "utils/timeout.h" #include "utils/wait_event.h" /* for backward compatibility */ /* IWYU pragma: export */ @@ -537,10 +538,11 @@ extern void pgstat_report_anytime_stat(bool force); extern void pgstat_force_next_flush(void); /* - * Schedule the next anytime stats update timeout. + * Schedule the next anytime stats update timeout and mark that we have + * mixed anytime stats pending. * * This should be called whenever accumulating statistics that support - * FLUSH_ANYTIME flushing mode. + * FLUSH_ANYTIME or FLUSH_MIXED flushing modes. */ #define pgstat_schedule_anytime_update() \ do { \ @@ -706,37 +708,58 @@ extern void pgstat_report_analyze(Relation rel, #define pgstat_count_heap_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_getnext(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_fetch(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_tuples(rel, n) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned += (n); \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_read(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_hit(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_hit++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n); diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 607f4255268..1a2114aad8a 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -322,8 +322,10 @@ typedef struct PgStat_KindInfo * that cannot use PgStat_EntryRef->pending. * * The anytime_only parameter indicates whether this is an anytime flush. + * The is_partial parameter indicates whether this is a partial flush. */ - bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, bool anytime_only); + bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, + bool anytime_only, bool *is_partial); /* * For variable-numbered stats: delete pending stats. Optional. @@ -757,7 +759,8 @@ extern void AtEOXact_PgStat_Database(bool isCommit, bool parallel); extern PgStat_StatDBEntry *pgstat_prep_database_pending(Oid dboid); extern void pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts); -extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -765,7 +768,8 @@ extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, Time * Functions in pgstat_function.c */ -extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_function_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -790,7 +794,8 @@ extern void AtEOSubXact_PgStat_Relations(PgStat_SubXactStatus *xact_state, bool 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, bool anytime_only); +extern bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref); extern void pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -858,7 +863,8 @@ extern void pgstat_wal_snapshot_cb(void); * Functions in pgstat_subscription.c */ -extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_subscription_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..11e3e57806d 100644 --- a/src/test/isolation/expected/stats.out +++ b/src/test/isolation/expected/stats.out @@ -2245,6 +2245,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..aef582e7582 100644 --- a/src/test/isolation/expected/stats_1.out +++ b/src/test/isolation/expected/stats_1.out @@ -2253,6 +2253,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..47414eb6009 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'; } @@ -156,6 +162,8 @@ step s2_func_stats { } # Relation stats steps +step s2_track_counts_on { SET track_counts = on; } +step s2_track_counts_off { SET track_counts = off; } step s2_table_select { SELECT * FROM test_stat_tab ORDER BY key, value; } step s2_table_update_k1 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1';} @@ -435,6 +443,23 @@ 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_track_counts_off + s2_table_select + s1_sleep + s1_table_stats + s2_track_counts_on + 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 diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index 207e841911b..ffcda7b6c7a 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -84,7 +84,8 @@ static dsa_area *custom_stats_description_dsa = NULL; /* Flush callback: merge pending stats into shared memory */ static bool test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, - bool nowait, bool anytime_only); + bool nowait, bool anytime_only, + bool *is_partial); /* Serialization callback: write auxiliary entry data */ static void test_custom_stats_var_to_serialized_data(const PgStat_HashKey *key, @@ -152,7 +153,8 @@ _PG_init(void) * Returns false only if nowait=true and lock acquisition fails. */ static bool -test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_StatCustomVarEntry *pending_entry; PgStatShared_CustomVarEntry *shared_entry; @@ -160,6 +162,9 @@ test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; shared_entry = (PgStatShared_CustomVarEntry *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; -- 2.34.1 --aq/6bi8L6WORnI+9-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v10 5/5] Change RELATION and DATABASE stats to anytime flush @ 2026-01-19 06:27 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-01-19 06:27 UTC (permalink / raw) This commit allows mixing fields with different transaction behavior within the same RELATION or DATABASE statistics kind: some fields are transactional (e.g., tuple inserts/updates/deletes) while others are non-transactional (e.g., sequential scans, blocks read). It modifies the relation flush callback to handle the anytime_only parameter introduced in commit <nnnn>. Implementation details: - Change RELATION from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Change DATABASE from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Add a is_partial parameter to flush_pending_cb() to be able to distinguish partial flushes in pgstat_flush_pending_entries() - Modify pgstat_relation_flush_cb() to handle anytime_only parameter: when true, then flush only non-transactional stats and when false, then flush all the stats. When set to true, it clears flushed fields from pending stats to prevent double-counting at transaction boundary DATABASE stats inherit the anytime flush behavior so that relation-derived stats (tuples_returned, tuples_fetched, blocks_fetched, blocks_hit) are visible while transactions are in progress. Tests are added to verify the anytime flush behavior for mixed fields. --- doc/src/sgml/monitoring.sgml | 37 ++++++- src/backend/utils/activity/pgstat.c | 15 +-- src/backend/utils/activity/pgstat_database.c | 6 +- src/backend/utils/activity/pgstat_function.c | 6 +- src/backend/utils/activity/pgstat_relation.c | 92 ++++++++++++---- .../utils/activity/pgstat_subscription.c | 6 +- src/include/pgstat.h | 27 ++++- src/include/utils/pgstat_internal.h | 16 ++- src/test/isolation/expected/stats.out | 102 ++++++++++++++++++ src/test/isolation/expected/stats_1.out | 102 ++++++++++++++++++ src/test/isolation/specs/stats.spec | 27 ++++- .../test_custom_stats/test_custom_var_stats.c | 9 +- 12 files changed, 404 insertions(+), 41 deletions(-) 11.7% doc/src/sgml/ 26.8% src/backend/utils/activity/ 4.2% src/include/utils/ 5.4% src/include/ 45.1% src/test/isolation/expected/ 4.7% src/test/isolation/specs/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..f2321b631b0 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"> @@ -3956,8 +3969,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last sequential scan on this table, based on the - most recent transaction stop time + The approximate time of the last sequential scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -3984,8 +3997,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last index scan on this table, based on the - most recent transaction stop time + The approximate time of the last index scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -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 fd6ab0db16f..a8a905640d0 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -298,7 +298,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, @@ -316,7 +316,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, .shared_size = sizeof(PgStatShared_Relation), .shared_data_off = offsetof(PgStatShared_Relation, stats), @@ -1354,7 +1354,8 @@ 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 entries. For entries + * that support it, the callback may flush only non-transactional fields. * This is safe to call inside transactions. * * If anytime_only is false, flushes all entries. @@ -1385,6 +1386,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); @@ -1405,7 +1407,8 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) } /* flush the stats, if possible */ - did_flush = kind_info->flush_pending_cb(entry_ref, nowait, anytime_only); + did_flush = kind_info->flush_pending_cb(entry_ref, nowait, + anytime_only, &is_partial_flush); Assert(did_flush || nowait); @@ -1415,8 +1418,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_database.c b/src/backend/utils/activity/pgstat_database.c index 8e86df60461..59dd0790fd7 100644 --- a/src/backend/utils/activity/pgstat_database.c +++ b/src/backend/utils/activity/pgstat_database.c @@ -435,7 +435,8 @@ pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStatShared_Database *sharedent; PgStat_StatDBEntry *pendingent; @@ -443,6 +444,9 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o pendingent = (PgStat_StatDBEntry *) entry_ref->pending; sharedent = (PgStatShared_Database *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; diff --git a/src/backend/utils/activity/pgstat_function.c b/src/backend/utils/activity/pgstat_function.c index 5ba4958382f..44193c93fc7 100644 --- a/src/backend/utils/activity/pgstat_function.c +++ b/src/backend/utils/activity/pgstat_function.c @@ -190,7 +190,8 @@ pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_FunctionCounts *localent; PgStatShared_Function *shfuncent; @@ -200,6 +201,9 @@ pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o localent = (PgStat_FunctionCounts *) entry_ref->pending; shfuncent = (PgStatShared_Function *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index ae2952cae89..62363dacfe1 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -47,7 +47,19 @@ static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_lev static void ensure_tabstat_xact_level(PgStat_TableStatus *pgstat_info); static void save_truncdrop_counters(PgStat_TableXactStatus *trans, bool is_drop); static void restore_truncdrop_counters(PgStat_TableXactStatus *trans); +static void flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, + PgStat_TableCounts *counts, bool anytime_only); +/* + * Update database statistics with non-transactional stats. + */ +#define UPDATE_DATABASE_ANYTIME_STATS(dbentry, counts) \ + do { \ + (dbentry)->tuples_returned += (counts)->tuples_returned; \ + (dbentry)->tuples_fetched += (counts)->tuples_fetched; \ + (dbentry)->blocks_fetched += (counts)->blocks_fetched; \ + (dbentry)->blocks_hit += (counts)->blocks_hit; \ + } while (0) /* * Copy stats between relations. This is used for things like REINDEX @@ -789,6 +801,29 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, rec->tuples_inserted + rec->tuples_updated; } +/* + * Helper function to flush non-transactional statistics. + */ +static void +flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, PgStat_TableCounts *counts, + bool anytime_only) +{ + TimestampTz t; + + tabentry->numscans += counts->numscans; + if (counts->numscans) + { + t = anytime_only ? GetCurrentTimestamp() : GetCurrentTransactionStopTimestamp(); + if (t > tabentry->lastscan) + tabentry->lastscan = t; + } + + tabentry->tuples_returned += counts->tuples_returned; + tabentry->tuples_fetched += counts->tuples_fetched; + tabentry->blocks_fetched += counts->blocks_fetched; + tabentry->blocks_hit += counts->blocks_hit; +} + /* * Flush out pending stats for the entry * @@ -797,9 +832,17 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, * * Some of the stats are copied to the corresponding pending database stats * entry when successfully flushing. + * + * If anytime_only is true, only non-transactional fields are flushed + * (numscans, tuples_returned, tuples_fetched, blocks_fetched, blocks_hit). + * Transactional fields remain pending until transaction boundary. + * + * Some of the stats are copied to the corresponding pending database stats + * entry when successfully flushing. */ bool -pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { Oid dboid; PgStat_TableStatus *lstats; /* pending stats entry */ @@ -807,12 +850,13 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o PgStat_StatTabEntry *tabentry; /* table entry of shared stats */ PgStat_StatDBEntry *dbentry; /* pending database entry */ - Assert(!anytime_only); - dboid = entry_ref->shared_entry->key.dboid; lstats = (PgStat_TableStatus *) entry_ref->pending; shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats; + /* this is a partial flush if in anytime only mode */ + *is_partial = anytime_only; + /* * Ignore entries that didn't accumulate any actual counts, such as * indexes that were opened by the planner but not used. @@ -824,19 +868,36 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o if (!pgstat_lock_entry(entry_ref, nowait)) return false; - /* add the values to the shared entry. */ tabentry = &shtabstats->stats; - tabentry->numscans += lstats->counts.numscans; - if (lstats->counts.numscans) + if (anytime_only) { - TimestampTz t = GetCurrentTransactionStopTimestamp(); - if (t > tabentry->lastscan) - tabentry->lastscan = t; + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, true); + + pgstat_unlock_entry(entry_ref); + + /* Also update the corresponding fields in database stats */ + dbentry = pgstat_prep_database_pending(dboid); + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + + /* + * Clear the flushed fields from pending stats to prevent + * double-counting when we flush all fields 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; } - tabentry->tuples_returned += lstats->counts.tuples_returned; - tabentry->tuples_fetched += lstats->counts.tuples_fetched; + + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, false); + tabentry->tuples_inserted += lstats->counts.tuples_inserted; tabentry->tuples_updated += lstats->counts.tuples_updated; tabentry->tuples_deleted += lstats->counts.tuples_deleted; @@ -866,9 +927,6 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o */ tabentry->ins_since_vacuum += lstats->counts.tuples_inserted; - tabentry->blocks_fetched += lstats->counts.blocks_fetched; - tabentry->blocks_hit += lstats->counts.blocks_hit; - /* Clamp live_tuples in case of negative delta_live_tuples */ tabentry->live_tuples = Max(tabentry->live_tuples, 0); /* Likewise for dead_tuples */ @@ -878,13 +936,11 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o /* The entry was successfully flushed, add the same to database stats */ dbentry = pgstat_prep_database_pending(dboid); - dbentry->tuples_returned += lstats->counts.tuples_returned; - dbentry->tuples_fetched += lstats->counts.tuples_fetched; + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + dbentry->tuples_inserted += lstats->counts.tuples_inserted; dbentry->tuples_updated += lstats->counts.tuples_updated; dbentry->tuples_deleted += lstats->counts.tuples_deleted; - dbentry->blocks_fetched += lstats->counts.blocks_fetched; - dbentry->blocks_hit += lstats->counts.blocks_hit; return true; } diff --git a/src/backend/utils/activity/pgstat_subscription.c b/src/backend/utils/activity/pgstat_subscription.c index 6b6eec7578d..bb32782a9d3 100644 --- a/src/backend/utils/activity/pgstat_subscription.c +++ b/src/backend/utils/activity/pgstat_subscription.c @@ -117,7 +117,8 @@ pgstat_fetch_stat_subscription(Oid subid) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_BackendSubEntry *localent; PgStatShared_Subscription *shsubent; @@ -127,6 +128,9 @@ pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anyti localent = (PgStat_BackendSubEntry *) entry_ref->pending; shsubent = (PgStatShared_Subscription *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 90237c70829..d26ff26e3e3 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -20,6 +20,7 @@ #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/pgstat_kind.h" #include "utils/relcache.h" +#include "utils/timeout.h" #include "utils/wait_event.h" /* for backward compatibility */ /* IWYU pragma: export */ @@ -536,10 +537,11 @@ extern void pgstat_report_anytime_stat(bool force); extern void pgstat_force_next_flush(void); /* - * Schedule the next anytime stats update timeout. + * Schedule the next anytime stats update timeout and mark that we have + * mixed anytime stats pending. * * This should be called whenever accumulating statistics that support - * FLUSH_ANYTIME flushing mode. + * FLUSH_ANYTIME or FLUSH_MIXED flushing modes. */ #define pgstat_schedule_anytime_update() \ do { \ @@ -705,37 +707,58 @@ extern void pgstat_report_analyze(Relation rel, #define pgstat_count_heap_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_getnext(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_fetch(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_tuples(rel, n) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned += (n); \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_read(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_hit(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_hit++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n); diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 607f4255268..1a2114aad8a 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -322,8 +322,10 @@ typedef struct PgStat_KindInfo * that cannot use PgStat_EntryRef->pending. * * The anytime_only parameter indicates whether this is an anytime flush. + * The is_partial parameter indicates whether this is a partial flush. */ - bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, bool anytime_only); + bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, + bool anytime_only, bool *is_partial); /* * For variable-numbered stats: delete pending stats. Optional. @@ -757,7 +759,8 @@ extern void AtEOXact_PgStat_Database(bool isCommit, bool parallel); extern PgStat_StatDBEntry *pgstat_prep_database_pending(Oid dboid); extern void pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts); -extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -765,7 +768,8 @@ extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, Time * Functions in pgstat_function.c */ -extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_function_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -790,7 +794,8 @@ extern void AtEOSubXact_PgStat_Relations(PgStat_SubXactStatus *xact_state, bool 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, bool anytime_only); +extern bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref); extern void pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -858,7 +863,8 @@ extern void pgstat_wal_snapshot_cb(void); * Functions in pgstat_subscription.c */ -extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_subscription_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..11e3e57806d 100644 --- a/src/test/isolation/expected/stats.out +++ b/src/test/isolation/expected/stats.out @@ -2245,6 +2245,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..aef582e7582 100644 --- a/src/test/isolation/expected/stats_1.out +++ b/src/test/isolation/expected/stats_1.out @@ -2253,6 +2253,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..47414eb6009 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'; } @@ -156,6 +162,8 @@ step s2_func_stats { } # Relation stats steps +step s2_track_counts_on { SET track_counts = on; } +step s2_track_counts_off { SET track_counts = off; } step s2_table_select { SELECT * FROM test_stat_tab ORDER BY key, value; } step s2_table_update_k1 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1';} @@ -435,6 +443,23 @@ 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_track_counts_off + s2_table_select + s1_sleep + s1_table_stats + s2_track_counts_on + 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 diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index e9f1bda6b32..59f531df5f7 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -85,7 +85,8 @@ static dsa_area *custom_stats_description_dsa = NULL; /* Flush callback: merge pending stats into shared memory */ static bool test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, - bool nowait, bool anytime_only); + bool nowait, bool anytime_only, + bool *is_partial); /* Serialization callback: write auxiliary entry data */ static void test_custom_stats_var_to_serialized_data(const PgStat_HashKey *key, @@ -153,7 +154,8 @@ _PG_init(void) * Returns false only if nowait=true and lock acquisition fails. */ static bool -test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_StatCustomVarEntry *pending_entry; PgStatShared_CustomVarEntry *shared_entry; @@ -161,6 +163,9 @@ test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; shared_entry = (PgStatShared_CustomVarEntry *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; -- 2.34.1 --NVvBxFuyV/R+1/8/-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v11 5/5] Change RELATION and DATABASE stats to anytime flush @ 2026-01-19 06:27 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-01-19 06:27 UTC (permalink / raw) This commit allows mixing fields with different transaction behavior within the same RELATION or DATABASE statistics kind: some fields are transactional (e.g., tuple inserts/updates/deletes) while others are non-transactional (e.g., sequential scans, blocks read). It modifies the relation flush callback to handle the anytime_only parameter introduced in commit <nnnn>. Implementation details: - Change RELATION from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Change DATABASE from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Add a is_partial parameter to flush_pending_cb() to be able to distinguish partial flushes in pgstat_flush_pending_entries() - Modify pgstat_relation_flush_cb() to handle anytime_only parameter: when true, then flush only non-transactional stats and when false, then flush all the stats. When set to true, it clears flushed fields from pending stats to prevent double-counting at transaction boundary DATABASE stats inherit the anytime flush behavior so that relation-derived stats (tuples_returned, tuples_fetched, blocks_fetched, blocks_hit) are visible while transactions are in progress. Tests are added to verify the anytime flush behavior for mixed fields. --- doc/src/sgml/monitoring.sgml | 37 ++++++- src/backend/utils/activity/pgstat.c | 15 +-- src/backend/utils/activity/pgstat_database.c | 6 +- src/backend/utils/activity/pgstat_function.c | 6 +- src/backend/utils/activity/pgstat_relation.c | 92 ++++++++++++---- .../utils/activity/pgstat_subscription.c | 6 +- src/include/pgstat.h | 27 ++++- src/include/utils/pgstat_internal.h | 16 ++- src/test/isolation/expected/stats.out | 102 ++++++++++++++++++ src/test/isolation/expected/stats_1.out | 102 ++++++++++++++++++ src/test/isolation/specs/stats.spec | 27 ++++- .../test_custom_stats/test_custom_var_stats.c | 9 +- 12 files changed, 404 insertions(+), 41 deletions(-) 11.7% doc/src/sgml/ 26.8% src/backend/utils/activity/ 4.2% src/include/utils/ 5.4% src/include/ 45.1% src/test/isolation/expected/ 4.7% src/test/isolation/specs/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..f2321b631b0 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"> @@ -3956,8 +3969,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last sequential scan on this table, based on the - most recent transaction stop time + The approximate time of the last sequential scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -3984,8 +3997,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last index scan on this table, based on the - most recent transaction stop time + The approximate time of the last index scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -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 fd6ab0db16f..a8a905640d0 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -298,7 +298,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, @@ -316,7 +316,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, .shared_size = sizeof(PgStatShared_Relation), .shared_data_off = offsetof(PgStatShared_Relation, stats), @@ -1354,7 +1354,8 @@ 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 entries. For entries + * that support it, the callback may flush only non-transactional fields. * This is safe to call inside transactions. * * If anytime_only is false, flushes all entries. @@ -1385,6 +1386,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); @@ -1405,7 +1407,8 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) } /* flush the stats, if possible */ - did_flush = kind_info->flush_pending_cb(entry_ref, nowait, anytime_only); + did_flush = kind_info->flush_pending_cb(entry_ref, nowait, + anytime_only, &is_partial_flush); Assert(did_flush || nowait); @@ -1415,8 +1418,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_database.c b/src/backend/utils/activity/pgstat_database.c index 8e86df60461..59dd0790fd7 100644 --- a/src/backend/utils/activity/pgstat_database.c +++ b/src/backend/utils/activity/pgstat_database.c @@ -435,7 +435,8 @@ pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStatShared_Database *sharedent; PgStat_StatDBEntry *pendingent; @@ -443,6 +444,9 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o pendingent = (PgStat_StatDBEntry *) entry_ref->pending; sharedent = (PgStatShared_Database *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; diff --git a/src/backend/utils/activity/pgstat_function.c b/src/backend/utils/activity/pgstat_function.c index 5ba4958382f..44193c93fc7 100644 --- a/src/backend/utils/activity/pgstat_function.c +++ b/src/backend/utils/activity/pgstat_function.c @@ -190,7 +190,8 @@ pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_FunctionCounts *localent; PgStatShared_Function *shfuncent; @@ -200,6 +201,9 @@ pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o localent = (PgStat_FunctionCounts *) entry_ref->pending; shfuncent = (PgStatShared_Function *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index ae2952cae89..62363dacfe1 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -47,7 +47,19 @@ static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_lev static void ensure_tabstat_xact_level(PgStat_TableStatus *pgstat_info); static void save_truncdrop_counters(PgStat_TableXactStatus *trans, bool is_drop); static void restore_truncdrop_counters(PgStat_TableXactStatus *trans); +static void flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, + PgStat_TableCounts *counts, bool anytime_only); +/* + * Update database statistics with non-transactional stats. + */ +#define UPDATE_DATABASE_ANYTIME_STATS(dbentry, counts) \ + do { \ + (dbentry)->tuples_returned += (counts)->tuples_returned; \ + (dbentry)->tuples_fetched += (counts)->tuples_fetched; \ + (dbentry)->blocks_fetched += (counts)->blocks_fetched; \ + (dbentry)->blocks_hit += (counts)->blocks_hit; \ + } while (0) /* * Copy stats between relations. This is used for things like REINDEX @@ -789,6 +801,29 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, rec->tuples_inserted + rec->tuples_updated; } +/* + * Helper function to flush non-transactional statistics. + */ +static void +flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, PgStat_TableCounts *counts, + bool anytime_only) +{ + TimestampTz t; + + tabentry->numscans += counts->numscans; + if (counts->numscans) + { + t = anytime_only ? GetCurrentTimestamp() : GetCurrentTransactionStopTimestamp(); + if (t > tabentry->lastscan) + tabentry->lastscan = t; + } + + tabentry->tuples_returned += counts->tuples_returned; + tabentry->tuples_fetched += counts->tuples_fetched; + tabentry->blocks_fetched += counts->blocks_fetched; + tabentry->blocks_hit += counts->blocks_hit; +} + /* * Flush out pending stats for the entry * @@ -797,9 +832,17 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, * * Some of the stats are copied to the corresponding pending database stats * entry when successfully flushing. + * + * If anytime_only is true, only non-transactional fields are flushed + * (numscans, tuples_returned, tuples_fetched, blocks_fetched, blocks_hit). + * Transactional fields remain pending until transaction boundary. + * + * Some of the stats are copied to the corresponding pending database stats + * entry when successfully flushing. */ bool -pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { Oid dboid; PgStat_TableStatus *lstats; /* pending stats entry */ @@ -807,12 +850,13 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o PgStat_StatTabEntry *tabentry; /* table entry of shared stats */ PgStat_StatDBEntry *dbentry; /* pending database entry */ - Assert(!anytime_only); - dboid = entry_ref->shared_entry->key.dboid; lstats = (PgStat_TableStatus *) entry_ref->pending; shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats; + /* this is a partial flush if in anytime only mode */ + *is_partial = anytime_only; + /* * Ignore entries that didn't accumulate any actual counts, such as * indexes that were opened by the planner but not used. @@ -824,19 +868,36 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o if (!pgstat_lock_entry(entry_ref, nowait)) return false; - /* add the values to the shared entry. */ tabentry = &shtabstats->stats; - tabentry->numscans += lstats->counts.numscans; - if (lstats->counts.numscans) + if (anytime_only) { - TimestampTz t = GetCurrentTransactionStopTimestamp(); - if (t > tabentry->lastscan) - tabentry->lastscan = t; + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, true); + + pgstat_unlock_entry(entry_ref); + + /* Also update the corresponding fields in database stats */ + dbentry = pgstat_prep_database_pending(dboid); + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + + /* + * Clear the flushed fields from pending stats to prevent + * double-counting when we flush all fields 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; } - tabentry->tuples_returned += lstats->counts.tuples_returned; - tabentry->tuples_fetched += lstats->counts.tuples_fetched; + + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, false); + tabentry->tuples_inserted += lstats->counts.tuples_inserted; tabentry->tuples_updated += lstats->counts.tuples_updated; tabentry->tuples_deleted += lstats->counts.tuples_deleted; @@ -866,9 +927,6 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o */ tabentry->ins_since_vacuum += lstats->counts.tuples_inserted; - tabentry->blocks_fetched += lstats->counts.blocks_fetched; - tabentry->blocks_hit += lstats->counts.blocks_hit; - /* Clamp live_tuples in case of negative delta_live_tuples */ tabentry->live_tuples = Max(tabentry->live_tuples, 0); /* Likewise for dead_tuples */ @@ -878,13 +936,11 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o /* The entry was successfully flushed, add the same to database stats */ dbentry = pgstat_prep_database_pending(dboid); - dbentry->tuples_returned += lstats->counts.tuples_returned; - dbentry->tuples_fetched += lstats->counts.tuples_fetched; + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + dbentry->tuples_inserted += lstats->counts.tuples_inserted; dbentry->tuples_updated += lstats->counts.tuples_updated; dbentry->tuples_deleted += lstats->counts.tuples_deleted; - dbentry->blocks_fetched += lstats->counts.blocks_fetched; - dbentry->blocks_hit += lstats->counts.blocks_hit; return true; } diff --git a/src/backend/utils/activity/pgstat_subscription.c b/src/backend/utils/activity/pgstat_subscription.c index 6b6eec7578d..bb32782a9d3 100644 --- a/src/backend/utils/activity/pgstat_subscription.c +++ b/src/backend/utils/activity/pgstat_subscription.c @@ -117,7 +117,8 @@ pgstat_fetch_stat_subscription(Oid subid) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_BackendSubEntry *localent; PgStatShared_Subscription *shsubent; @@ -127,6 +128,9 @@ pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anyti localent = (PgStat_BackendSubEntry *) entry_ref->pending; shsubent = (PgStatShared_Subscription *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 90237c70829..d26ff26e3e3 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -20,6 +20,7 @@ #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/pgstat_kind.h" #include "utils/relcache.h" +#include "utils/timeout.h" #include "utils/wait_event.h" /* for backward compatibility */ /* IWYU pragma: export */ @@ -536,10 +537,11 @@ extern void pgstat_report_anytime_stat(bool force); extern void pgstat_force_next_flush(void); /* - * Schedule the next anytime stats update timeout. + * Schedule the next anytime stats update timeout and mark that we have + * mixed anytime stats pending. * * This should be called whenever accumulating statistics that support - * FLUSH_ANYTIME flushing mode. + * FLUSH_ANYTIME or FLUSH_MIXED flushing modes. */ #define pgstat_schedule_anytime_update() \ do { \ @@ -705,37 +707,58 @@ extern void pgstat_report_analyze(Relation rel, #define pgstat_count_heap_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_getnext(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_fetch(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_tuples(rel, n) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned += (n); \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_read(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_hit(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_hit++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n); diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 607f4255268..1a2114aad8a 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -322,8 +322,10 @@ typedef struct PgStat_KindInfo * that cannot use PgStat_EntryRef->pending. * * The anytime_only parameter indicates whether this is an anytime flush. + * The is_partial parameter indicates whether this is a partial flush. */ - bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, bool anytime_only); + bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, + bool anytime_only, bool *is_partial); /* * For variable-numbered stats: delete pending stats. Optional. @@ -757,7 +759,8 @@ extern void AtEOXact_PgStat_Database(bool isCommit, bool parallel); extern PgStat_StatDBEntry *pgstat_prep_database_pending(Oid dboid); extern void pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts); -extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -765,7 +768,8 @@ extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, Time * Functions in pgstat_function.c */ -extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_function_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -790,7 +794,8 @@ extern void AtEOSubXact_PgStat_Relations(PgStat_SubXactStatus *xact_state, bool 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, bool anytime_only); +extern bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref); extern void pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -858,7 +863,8 @@ extern void pgstat_wal_snapshot_cb(void); * Functions in pgstat_subscription.c */ -extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_subscription_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..11e3e57806d 100644 --- a/src/test/isolation/expected/stats.out +++ b/src/test/isolation/expected/stats.out @@ -2245,6 +2245,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..aef582e7582 100644 --- a/src/test/isolation/expected/stats_1.out +++ b/src/test/isolation/expected/stats_1.out @@ -2253,6 +2253,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..47414eb6009 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'; } @@ -156,6 +162,8 @@ step s2_func_stats { } # Relation stats steps +step s2_track_counts_on { SET track_counts = on; } +step s2_track_counts_off { SET track_counts = off; } step s2_table_select { SELECT * FROM test_stat_tab ORDER BY key, value; } step s2_table_update_k1 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1';} @@ -435,6 +443,23 @@ 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_track_counts_off + s2_table_select + s1_sleep + s1_table_stats + s2_track_counts_on + 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 diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index e9f1bda6b32..59f531df5f7 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -85,7 +85,8 @@ static dsa_area *custom_stats_description_dsa = NULL; /* Flush callback: merge pending stats into shared memory */ static bool test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, - bool nowait, bool anytime_only); + bool nowait, bool anytime_only, + bool *is_partial); /* Serialization callback: write auxiliary entry data */ static void test_custom_stats_var_to_serialized_data(const PgStat_HashKey *key, @@ -153,7 +154,8 @@ _PG_init(void) * Returns false only if nowait=true and lock acquisition fails. */ static bool -test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_StatCustomVarEntry *pending_entry; PgStatShared_CustomVarEntry *shared_entry; @@ -161,6 +163,9 @@ test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; shared_entry = (PgStatShared_CustomVarEntry *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; -- 2.34.1 --2MEBAGW8+kohXisi-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v7 5/5] Change RELATION and DATABASE stats to anytime flush @ 2026-01-19 06:27 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-01-19 06:27 UTC (permalink / raw) This commit allows mixing fields with different transaction behavior within the same RELATION or DATABASE statistics kind: some fields are transactional (e.g., tuple inserts/updates/deletes) while others are non-transactional (e.g., sequential scans, blocks read). It modifies the relation flush callback to handle the anytime_only parameter introduced in commit <nnnn>. Implementation details: - Change RELATION from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Change DATABASE from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Add a is_partial parameter to flush_pending_cb() to be able to distinguish partial flushes in pgstat_flush_pending_entries() - Modify pgstat_relation_flush_cb() to handle anytime_only parameter: when true, then flush only non-transactional stats and when false, then flush all the stats. When set to true, it clears flushed fields from pending stats to prevent double-counting at transaction boundary DATABASE stats inherit the anytime flush behavior so that relation-derived stats (tuples_returned, tuples_fetched, blocks_fetched, blocks_hit) are visible while transactions are in progress. Tests are added to verify the anytime flush behavior for mixed fields. --- doc/src/sgml/monitoring.sgml | 37 ++++++- src/backend/utils/activity/pgstat.c | 15 +-- src/backend/utils/activity/pgstat_database.c | 6 +- src/backend/utils/activity/pgstat_function.c | 6 +- src/backend/utils/activity/pgstat_relation.c | 92 ++++++++++++---- .../utils/activity/pgstat_subscription.c | 6 +- src/include/pgstat.h | 27 ++++- src/include/utils/pgstat_internal.h | 16 ++- src/test/isolation/expected/stats.out | 102 ++++++++++++++++++ src/test/isolation/expected/stats_1.out | 102 ++++++++++++++++++ src/test/isolation/specs/stats.spec | 27 ++++- .../test_custom_stats/test_custom_var_stats.c | 9 +- 12 files changed, 404 insertions(+), 41 deletions(-) 11.7% doc/src/sgml/ 26.8% src/backend/utils/activity/ 4.2% src/include/utils/ 5.4% src/include/ 45.1% src/test/isolation/expected/ 4.7% src/test/isolation/specs/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..f2321b631b0 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"> @@ -3956,8 +3969,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last sequential scan on this table, based on the - most recent transaction stop time + The approximate time of the last sequential scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -3984,8 +3997,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last index scan on this table, based on the - most recent transaction stop time + The approximate time of the last index scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -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 dd85a27c52f..a20a87709c6 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -291,7 +291,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, @@ -309,7 +309,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, .shared_size = sizeof(PgStatShared_Relation), .shared_data_off = offsetof(PgStatShared_Relation, stats), @@ -1347,7 +1347,8 @@ 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 entries. For entries + * that support it, the callback may flush only non-transactional fields. * This is safe to call inside transactions. * * If anytime_only is false, flushes all entries. @@ -1378,6 +1379,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); @@ -1398,7 +1400,8 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) } /* flush the stats, if possible */ - did_flush = kind_info->flush_pending_cb(entry_ref, nowait, anytime_only); + did_flush = kind_info->flush_pending_cb(entry_ref, nowait, + anytime_only, &is_partial_flush); Assert(did_flush || nowait); @@ -1408,8 +1411,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_database.c b/src/backend/utils/activity/pgstat_database.c index 8e86df60461..59dd0790fd7 100644 --- a/src/backend/utils/activity/pgstat_database.c +++ b/src/backend/utils/activity/pgstat_database.c @@ -435,7 +435,8 @@ pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStatShared_Database *sharedent; PgStat_StatDBEntry *pendingent; @@ -443,6 +444,9 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o pendingent = (PgStat_StatDBEntry *) entry_ref->pending; sharedent = (PgStatShared_Database *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; diff --git a/src/backend/utils/activity/pgstat_function.c b/src/backend/utils/activity/pgstat_function.c index 5ba4958382f..44193c93fc7 100644 --- a/src/backend/utils/activity/pgstat_function.c +++ b/src/backend/utils/activity/pgstat_function.c @@ -190,7 +190,8 @@ pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_FunctionCounts *localent; PgStatShared_Function *shfuncent; @@ -200,6 +201,9 @@ pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o localent = (PgStat_FunctionCounts *) entry_ref->pending; shfuncent = (PgStatShared_Function *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index ae2952cae89..62363dacfe1 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -47,7 +47,19 @@ static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_lev static void ensure_tabstat_xact_level(PgStat_TableStatus *pgstat_info); static void save_truncdrop_counters(PgStat_TableXactStatus *trans, bool is_drop); static void restore_truncdrop_counters(PgStat_TableXactStatus *trans); +static void flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, + PgStat_TableCounts *counts, bool anytime_only); +/* + * Update database statistics with non-transactional stats. + */ +#define UPDATE_DATABASE_ANYTIME_STATS(dbentry, counts) \ + do { \ + (dbentry)->tuples_returned += (counts)->tuples_returned; \ + (dbentry)->tuples_fetched += (counts)->tuples_fetched; \ + (dbentry)->blocks_fetched += (counts)->blocks_fetched; \ + (dbentry)->blocks_hit += (counts)->blocks_hit; \ + } while (0) /* * Copy stats between relations. This is used for things like REINDEX @@ -789,6 +801,29 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, rec->tuples_inserted + rec->tuples_updated; } +/* + * Helper function to flush non-transactional statistics. + */ +static void +flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, PgStat_TableCounts *counts, + bool anytime_only) +{ + TimestampTz t; + + tabentry->numscans += counts->numscans; + if (counts->numscans) + { + t = anytime_only ? GetCurrentTimestamp() : GetCurrentTransactionStopTimestamp(); + if (t > tabentry->lastscan) + tabentry->lastscan = t; + } + + tabentry->tuples_returned += counts->tuples_returned; + tabentry->tuples_fetched += counts->tuples_fetched; + tabentry->blocks_fetched += counts->blocks_fetched; + tabentry->blocks_hit += counts->blocks_hit; +} + /* * Flush out pending stats for the entry * @@ -797,9 +832,17 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, * * Some of the stats are copied to the corresponding pending database stats * entry when successfully flushing. + * + * If anytime_only is true, only non-transactional fields are flushed + * (numscans, tuples_returned, tuples_fetched, blocks_fetched, blocks_hit). + * Transactional fields remain pending until transaction boundary. + * + * Some of the stats are copied to the corresponding pending database stats + * entry when successfully flushing. */ bool -pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { Oid dboid; PgStat_TableStatus *lstats; /* pending stats entry */ @@ -807,12 +850,13 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o PgStat_StatTabEntry *tabentry; /* table entry of shared stats */ PgStat_StatDBEntry *dbentry; /* pending database entry */ - Assert(!anytime_only); - dboid = entry_ref->shared_entry->key.dboid; lstats = (PgStat_TableStatus *) entry_ref->pending; shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats; + /* this is a partial flush if in anytime only mode */ + *is_partial = anytime_only; + /* * Ignore entries that didn't accumulate any actual counts, such as * indexes that were opened by the planner but not used. @@ -824,19 +868,36 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o if (!pgstat_lock_entry(entry_ref, nowait)) return false; - /* add the values to the shared entry. */ tabentry = &shtabstats->stats; - tabentry->numscans += lstats->counts.numscans; - if (lstats->counts.numscans) + if (anytime_only) { - TimestampTz t = GetCurrentTransactionStopTimestamp(); - if (t > tabentry->lastscan) - tabentry->lastscan = t; + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, true); + + pgstat_unlock_entry(entry_ref); + + /* Also update the corresponding fields in database stats */ + dbentry = pgstat_prep_database_pending(dboid); + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + + /* + * Clear the flushed fields from pending stats to prevent + * double-counting when we flush all fields 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; } - tabentry->tuples_returned += lstats->counts.tuples_returned; - tabentry->tuples_fetched += lstats->counts.tuples_fetched; + + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, false); + tabentry->tuples_inserted += lstats->counts.tuples_inserted; tabentry->tuples_updated += lstats->counts.tuples_updated; tabentry->tuples_deleted += lstats->counts.tuples_deleted; @@ -866,9 +927,6 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o */ tabentry->ins_since_vacuum += lstats->counts.tuples_inserted; - tabentry->blocks_fetched += lstats->counts.blocks_fetched; - tabentry->blocks_hit += lstats->counts.blocks_hit; - /* Clamp live_tuples in case of negative delta_live_tuples */ tabentry->live_tuples = Max(tabentry->live_tuples, 0); /* Likewise for dead_tuples */ @@ -878,13 +936,11 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o /* The entry was successfully flushed, add the same to database stats */ dbentry = pgstat_prep_database_pending(dboid); - dbentry->tuples_returned += lstats->counts.tuples_returned; - dbentry->tuples_fetched += lstats->counts.tuples_fetched; + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + dbentry->tuples_inserted += lstats->counts.tuples_inserted; dbentry->tuples_updated += lstats->counts.tuples_updated; dbentry->tuples_deleted += lstats->counts.tuples_deleted; - dbentry->blocks_fetched += lstats->counts.blocks_fetched; - dbentry->blocks_hit += lstats->counts.blocks_hit; return true; } diff --git a/src/backend/utils/activity/pgstat_subscription.c b/src/backend/utils/activity/pgstat_subscription.c index c4614817966..43fec86c635 100644 --- a/src/backend/utils/activity/pgstat_subscription.c +++ b/src/backend/utils/activity/pgstat_subscription.c @@ -116,7 +116,8 @@ pgstat_fetch_stat_subscription(Oid subid) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_BackendSubEntry *localent; PgStatShared_Subscription *shsubent; @@ -126,6 +127,9 @@ pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anyti localent = (PgStat_BackendSubEntry *) entry_ref->pending; shsubent = (PgStatShared_Subscription *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/include/pgstat.h b/src/include/pgstat.h index ef856dbf55b..06639198f28 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -21,6 +21,7 @@ #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/pgstat_kind.h" #include "utils/relcache.h" +#include "utils/timeout.h" #include "utils/wait_event.h" /* for backward compatibility */ /* IWYU pragma: export */ @@ -537,10 +538,11 @@ extern void pgstat_report_anytime_stat(bool force); extern void pgstat_force_next_flush(void); /* - * Schedule the next anytime stats update timeout. + * Schedule the next anytime stats update timeout and mark that we have + * mixed anytime stats pending. * * This should be called whenever accumulating statistics that support - * FLUSH_ANYTIME flushing mode. + * FLUSH_ANYTIME or FLUSH_MIXED flushing modes. */ #define pgstat_schedule_anytime_update() \ do { \ @@ -703,37 +705,58 @@ extern void pgstat_report_analyze(Relation rel, #define pgstat_count_heap_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_getnext(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_fetch(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_tuples(rel, n) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned += (n); \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_read(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_hit(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_hit++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n); diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 607f4255268..1a2114aad8a 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -322,8 +322,10 @@ typedef struct PgStat_KindInfo * that cannot use PgStat_EntryRef->pending. * * The anytime_only parameter indicates whether this is an anytime flush. + * The is_partial parameter indicates whether this is a partial flush. */ - bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, bool anytime_only); + bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, + bool anytime_only, bool *is_partial); /* * For variable-numbered stats: delete pending stats. Optional. @@ -757,7 +759,8 @@ extern void AtEOXact_PgStat_Database(bool isCommit, bool parallel); extern PgStat_StatDBEntry *pgstat_prep_database_pending(Oid dboid); extern void pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts); -extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -765,7 +768,8 @@ extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, Time * Functions in pgstat_function.c */ -extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_function_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -790,7 +794,8 @@ extern void AtEOSubXact_PgStat_Relations(PgStat_SubXactStatus *xact_state, bool 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, bool anytime_only); +extern bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref); extern void pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -858,7 +863,8 @@ extern void pgstat_wal_snapshot_cb(void); * Functions in pgstat_subscription.c */ -extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_subscription_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..11e3e57806d 100644 --- a/src/test/isolation/expected/stats.out +++ b/src/test/isolation/expected/stats.out @@ -2245,6 +2245,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..aef582e7582 100644 --- a/src/test/isolation/expected/stats_1.out +++ b/src/test/isolation/expected/stats_1.out @@ -2253,6 +2253,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..47414eb6009 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'; } @@ -156,6 +162,8 @@ step s2_func_stats { } # Relation stats steps +step s2_track_counts_on { SET track_counts = on; } +step s2_track_counts_off { SET track_counts = off; } step s2_table_select { SELECT * FROM test_stat_tab ORDER BY key, value; } step s2_table_update_k1 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1';} @@ -435,6 +443,23 @@ 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_track_counts_off + s2_table_select + s1_sleep + s1_table_stats + s2_track_counts_on + 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 diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index 207e841911b..ffcda7b6c7a 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -84,7 +84,8 @@ static dsa_area *custom_stats_description_dsa = NULL; /* Flush callback: merge pending stats into shared memory */ static bool test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, - bool nowait, bool anytime_only); + bool nowait, bool anytime_only, + bool *is_partial); /* Serialization callback: write auxiliary entry data */ static void test_custom_stats_var_to_serialized_data(const PgStat_HashKey *key, @@ -152,7 +153,8 @@ _PG_init(void) * Returns false only if nowait=true and lock acquisition fails. */ static bool -test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_StatCustomVarEntry *pending_entry; PgStatShared_CustomVarEntry *shared_entry; @@ -160,6 +162,9 @@ test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; shared_entry = (PgStatShared_CustomVarEntry *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; -- 2.34.1 --C2xzVbxFmVrP7k6O-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v6 5/5] Change RELATION and DATABASE stats to anytime flush @ 2026-01-19 06:27 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-01-19 06:27 UTC (permalink / raw) This commit allows mixing fields with different transaction behavior within the same RELATION or DATABASE statistics kind: some fields are transactional (e.g., tuple inserts/updates/deletes) while others are non-transactional (e.g., sequential scans, blocks read). It modifies the relation flush callback to handle the anytime_only parameter introduced in commit <nnnn>. Implementation details: - Change RELATION from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Change DATABASE from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Modify pgstat_relation_flush_cb() to handle anytime_only parameter: when true, then flush only non-transactional stats and when false, then flush all the stats. When set to true, it clears flushed fields from pending stats to prevent double-counting at transaction boundary DATABASE stats inherit the anytime flush behavior so that relation-derived stats (tuples_returned, tuples_fetched, blocks_fetched, blocks_hit) are visible while transactions are in progress. Tests are added to verify the anytime flush behavior for mixed fields. --- doc/src/sgml/monitoring.sgml | 37 ++++++- src/backend/utils/activity/pgstat.c | 17 ++-- src/backend/utils/activity/pgstat_relation.c | 86 ++++++++++++---- src/include/pgstat.h | 27 ++++- src/test/isolation/expected/stats.out | 102 +++++++++++++++++++ src/test/isolation/expected/stats_1.out | 102 +++++++++++++++++++ src/test/isolation/specs/stats.spec | 27 ++++- 7 files changed, 368 insertions(+), 30 deletions(-) 13.0% doc/src/sgml/ 25.9% src/backend/utils/activity/ 6.0% src/include/ 49.8% src/test/isolation/expected/ 5.1% src/test/isolation/specs/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..aa7bd2e2e2a 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"> @@ -3956,8 +3969,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last sequential scan on this table, based on the - most recent transaction stop time + The approximate time of the last sequential scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -3984,8 +3997,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last index scan on this table, based on the - most recent transaction stop time + The approximate time of the last index scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -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 79eb59b5625..9234185de64 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -291,7 +291,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, @@ -309,7 +309,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, .shared_size = sizeof(PgStatShared_Relation), .shared_data_off = offsetof(PgStatShared_Relation, stats), @@ -1344,10 +1344,12 @@ pgstat_delete_pending_entry(PgStat_EntryRef *entry_ref) dlist_delete(&entry_ref->pending_node); } + /* * 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 entries. For entries + * that support it, the callback may flush only non-transactional fields. * This is safe to call inside transactions. * * If anytime_only is false, flushes all entries. @@ -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,19 +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, anytime_only); Assert(did_flush || nowait); + /* Partial flush only happens in anytime mode for FLUSH_ANYTIME stats */ + is_partial_flush = (anytime_only && kind_info->flush_mode == FLUSH_ANYTIME); + /* determine next entry, before deleting the pending entry */ if (dlist_has_next(&pgStatPending, cur)) next = dlist_next_node(&pgStatPending, cur); 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 ae2952cae89..10ab9e4cc76 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -47,7 +47,19 @@ static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_lev static void ensure_tabstat_xact_level(PgStat_TableStatus *pgstat_info); static void save_truncdrop_counters(PgStat_TableXactStatus *trans, bool is_drop); static void restore_truncdrop_counters(PgStat_TableXactStatus *trans); +static void flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, + PgStat_TableCounts *counts, bool anytime_only); +/* + * Update database statistics with non-transactional stats. + */ +#define UPDATE_DATABASE_ANYTIME_STATS(dbentry, counts) \ + do { \ + (dbentry)->tuples_returned += (counts)->tuples_returned; \ + (dbentry)->tuples_fetched += (counts)->tuples_fetched; \ + (dbentry)->blocks_fetched += (counts)->blocks_fetched; \ + (dbentry)->blocks_hit += (counts)->blocks_hit; \ + } while (0) /* * Copy stats between relations. This is used for things like REINDEX @@ -789,6 +801,29 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, rec->tuples_inserted + rec->tuples_updated; } +/* + * Helper function to flush non-transactional statistics. + */ +static void +flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, PgStat_TableCounts *counts, + bool anytime_only) +{ + TimestampTz t; + + tabentry->numscans += counts->numscans; + if (counts->numscans) + { + t = anytime_only ? GetCurrentTimestamp() : GetCurrentTransactionStopTimestamp(); + if (t > tabentry->lastscan) + tabentry->lastscan = t; + } + + tabentry->tuples_returned += counts->tuples_returned; + tabentry->tuples_fetched += counts->tuples_fetched; + tabentry->blocks_fetched += counts->blocks_fetched; + tabentry->blocks_hit += counts->blocks_hit; +} + /* * Flush out pending stats for the entry * @@ -797,6 +832,13 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, * * Some of the stats are copied to the corresponding pending database stats * entry when successfully flushing. + * + * If anytime_only is true, only non-transactional fields are flushed + * (numscans, tuples_returned, tuples_fetched, blocks_fetched, blocks_hit). + * Transactional fields remain pending until transaction boundary. + * + * Some of the stats are copied to the corresponding pending database stats + * entry when successfully flushing. */ bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) @@ -807,8 +849,6 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o PgStat_StatTabEntry *tabentry; /* table entry of shared stats */ PgStat_StatDBEntry *dbentry; /* pending database entry */ - Assert(!anytime_only); - dboid = entry_ref->shared_entry->key.dboid; lstats = (PgStat_TableStatus *) entry_ref->pending; shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats; @@ -824,19 +864,36 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o if (!pgstat_lock_entry(entry_ref, nowait)) return false; - /* add the values to the shared entry. */ tabentry = &shtabstats->stats; - tabentry->numscans += lstats->counts.numscans; - if (lstats->counts.numscans) + if (anytime_only) { - TimestampTz t = GetCurrentTransactionStopTimestamp(); - if (t > tabentry->lastscan) - tabentry->lastscan = t; + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, true); + + pgstat_unlock_entry(entry_ref); + + /* Also update the corresponding fields in database stats */ + dbentry = pgstat_prep_database_pending(dboid); + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + + /* + * Clear the flushed fields from pending stats to prevent + * double-counting when we flush all fields 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; } - tabentry->tuples_returned += lstats->counts.tuples_returned; - tabentry->tuples_fetched += lstats->counts.tuples_fetched; + + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, false); + tabentry->tuples_inserted += lstats->counts.tuples_inserted; tabentry->tuples_updated += lstats->counts.tuples_updated; tabentry->tuples_deleted += lstats->counts.tuples_deleted; @@ -866,9 +923,6 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o */ tabentry->ins_since_vacuum += lstats->counts.tuples_inserted; - tabentry->blocks_fetched += lstats->counts.blocks_fetched; - tabentry->blocks_hit += lstats->counts.blocks_hit; - /* Clamp live_tuples in case of negative delta_live_tuples */ tabentry->live_tuples = Max(tabentry->live_tuples, 0); /* Likewise for dead_tuples */ @@ -878,13 +932,11 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o /* The entry was successfully flushed, add the same to database stats */ dbentry = pgstat_prep_database_pending(dboid); - dbentry->tuples_returned += lstats->counts.tuples_returned; - dbentry->tuples_fetched += lstats->counts.tuples_fetched; + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + dbentry->tuples_inserted += lstats->counts.tuples_inserted; dbentry->tuples_updated += lstats->counts.tuples_updated; dbentry->tuples_deleted += lstats->counts.tuples_deleted; - dbentry->blocks_fetched += lstats->counts.blocks_fetched; - dbentry->blocks_hit += lstats->counts.blocks_hit; return true; } diff --git a/src/include/pgstat.h b/src/include/pgstat.h index ef856dbf55b..06639198f28 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -21,6 +21,7 @@ #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/pgstat_kind.h" #include "utils/relcache.h" +#include "utils/timeout.h" #include "utils/wait_event.h" /* for backward compatibility */ /* IWYU pragma: export */ @@ -537,10 +538,11 @@ extern void pgstat_report_anytime_stat(bool force); extern void pgstat_force_next_flush(void); /* - * Schedule the next anytime stats update timeout. + * Schedule the next anytime stats update timeout and mark that we have + * mixed anytime stats pending. * * This should be called whenever accumulating statistics that support - * FLUSH_ANYTIME flushing mode. + * FLUSH_ANYTIME or FLUSH_MIXED flushing modes. */ #define pgstat_schedule_anytime_update() \ do { \ @@ -703,37 +705,58 @@ extern void pgstat_report_analyze(Relation rel, #define pgstat_count_heap_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_getnext(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_fetch(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_tuples(rel, n) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned += (n); \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_read(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_hit(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_hit++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n); diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out index cfad309ccf3..11e3e57806d 100644 --- a/src/test/isolation/expected/stats.out +++ b/src/test/isolation/expected/stats.out @@ -2245,6 +2245,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..aef582e7582 100644 --- a/src/test/isolation/expected/stats_1.out +++ b/src/test/isolation/expected/stats_1.out @@ -2253,6 +2253,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..47414eb6009 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'; } @@ -156,6 +162,8 @@ step s2_func_stats { } # Relation stats steps +step s2_track_counts_on { SET track_counts = on; } +step s2_track_counts_off { SET track_counts = off; } step s2_table_select { SELECT * FROM test_stat_tab ORDER BY key, value; } step s2_table_update_k1 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1';} @@ -435,6 +443,23 @@ 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_track_counts_off + s2_table_select + s1_sleep + s1_table_stats + s2_track_counts_on + 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 --DxqFthphbM+ha9Dy-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v8 5/5] Change RELATION and DATABASE stats to anytime flush @ 2026-01-19 06:27 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2026-01-19 06:27 UTC (permalink / raw) This commit allows mixing fields with different transaction behavior within the same RELATION or DATABASE statistics kind: some fields are transactional (e.g., tuple inserts/updates/deletes) while others are non-transactional (e.g., sequential scans, blocks read). It modifies the relation flush callback to handle the anytime_only parameter introduced in commit <nnnn>. Implementation details: - Change RELATION from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Change DATABASE from FLUSH_AT_TXN_BOUNDARY to FLUSH_ANYTIME - Add a is_partial parameter to flush_pending_cb() to be able to distinguish partial flushes in pgstat_flush_pending_entries() - Modify pgstat_relation_flush_cb() to handle anytime_only parameter: when true, then flush only non-transactional stats and when false, then flush all the stats. When set to true, it clears flushed fields from pending stats to prevent double-counting at transaction boundary DATABASE stats inherit the anytime flush behavior so that relation-derived stats (tuples_returned, tuples_fetched, blocks_fetched, blocks_hit) are visible while transactions are in progress. Tests are added to verify the anytime flush behavior for mixed fields. --- doc/src/sgml/monitoring.sgml | 37 ++++++- src/backend/utils/activity/pgstat.c | 15 +-- src/backend/utils/activity/pgstat_database.c | 6 +- src/backend/utils/activity/pgstat_function.c | 6 +- src/backend/utils/activity/pgstat_relation.c | 92 ++++++++++++---- .../utils/activity/pgstat_subscription.c | 6 +- src/include/pgstat.h | 27 ++++- src/include/utils/pgstat_internal.h | 16 ++- src/test/isolation/expected/stats.out | 102 ++++++++++++++++++ src/test/isolation/expected/stats_1.out | 102 ++++++++++++++++++ src/test/isolation/specs/stats.spec | 27 ++++- .../test_custom_stats/test_custom_var_stats.c | 9 +- 12 files changed, 404 insertions(+), 41 deletions(-) 11.7% doc/src/sgml/ 26.8% src/backend/utils/activity/ 4.2% src/include/utils/ 5.4% src/include/ 45.1% src/test/isolation/expected/ 4.7% src/test/isolation/specs/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..f2321b631b0 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"> @@ -3956,8 +3969,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_seq_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last sequential scan on this table, based on the - most recent transaction stop time + The approximate time of the last sequential scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -3984,8 +3997,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>last_idx_scan</structfield> <type>timestamp with time zone</type> </para> <para> - The time of the last index scan on this table, based on the - most recent transaction stop time + The approximate time of the last index scan on this table, updated + at least every <varname>stats_flush_interval</varname> </para></entry> </row> @@ -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 dd85a27c52f..a20a87709c6 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -291,7 +291,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, @@ -309,7 +309,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, .shared_size = sizeof(PgStatShared_Relation), .shared_data_off = offsetof(PgStatShared_Relation, stats), @@ -1347,7 +1347,8 @@ 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 entries. For entries + * that support it, the callback may flush only non-transactional fields. * This is safe to call inside transactions. * * If anytime_only is false, flushes all entries. @@ -1378,6 +1379,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); @@ -1398,7 +1400,8 @@ pgstat_flush_pending_entries(bool nowait, bool anytime_only) } /* flush the stats, if possible */ - did_flush = kind_info->flush_pending_cb(entry_ref, nowait, anytime_only); + did_flush = kind_info->flush_pending_cb(entry_ref, nowait, + anytime_only, &is_partial_flush); Assert(did_flush || nowait); @@ -1408,8 +1411,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_database.c b/src/backend/utils/activity/pgstat_database.c index 8e86df60461..59dd0790fd7 100644 --- a/src/backend/utils/activity/pgstat_database.c +++ b/src/backend/utils/activity/pgstat_database.c @@ -435,7 +435,8 @@ pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStatShared_Database *sharedent; PgStat_StatDBEntry *pendingent; @@ -443,6 +444,9 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o pendingent = (PgStat_StatDBEntry *) entry_ref->pending; sharedent = (PgStatShared_Database *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; diff --git a/src/backend/utils/activity/pgstat_function.c b/src/backend/utils/activity/pgstat_function.c index 5ba4958382f..44193c93fc7 100644 --- a/src/backend/utils/activity/pgstat_function.c +++ b/src/backend/utils/activity/pgstat_function.c @@ -190,7 +190,8 @@ pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_FunctionCounts *localent; PgStatShared_Function *shfuncent; @@ -200,6 +201,9 @@ pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o localent = (PgStat_FunctionCounts *) entry_ref->pending; shfuncent = (PgStatShared_Function *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index ae2952cae89..62363dacfe1 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -47,7 +47,19 @@ static void add_tabstat_xact_level(PgStat_TableStatus *pgstat_info, int nest_lev static void ensure_tabstat_xact_level(PgStat_TableStatus *pgstat_info); static void save_truncdrop_counters(PgStat_TableXactStatus *trans, bool is_drop); static void restore_truncdrop_counters(PgStat_TableXactStatus *trans); +static void flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, + PgStat_TableCounts *counts, bool anytime_only); +/* + * Update database statistics with non-transactional stats. + */ +#define UPDATE_DATABASE_ANYTIME_STATS(dbentry, counts) \ + do { \ + (dbentry)->tuples_returned += (counts)->tuples_returned; \ + (dbentry)->tuples_fetched += (counts)->tuples_fetched; \ + (dbentry)->blocks_fetched += (counts)->blocks_fetched; \ + (dbentry)->blocks_hit += (counts)->blocks_hit; \ + } while (0) /* * Copy stats between relations. This is used for things like REINDEX @@ -789,6 +801,29 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, rec->tuples_inserted + rec->tuples_updated; } +/* + * Helper function to flush non-transactional statistics. + */ +static void +flush_relation_anytime_stats(PgStat_StatTabEntry *tabentry, PgStat_TableCounts *counts, + bool anytime_only) +{ + TimestampTz t; + + tabentry->numscans += counts->numscans; + if (counts->numscans) + { + t = anytime_only ? GetCurrentTimestamp() : GetCurrentTransactionStopTimestamp(); + if (t > tabentry->lastscan) + tabentry->lastscan = t; + } + + tabentry->tuples_returned += counts->tuples_returned; + tabentry->tuples_fetched += counts->tuples_fetched; + tabentry->blocks_fetched += counts->blocks_fetched; + tabentry->blocks_hit += counts->blocks_hit; +} + /* * Flush out pending stats for the entry * @@ -797,9 +832,17 @@ pgstat_twophase_postabort(FullTransactionId fxid, uint16 info, * * Some of the stats are copied to the corresponding pending database stats * entry when successfully flushing. + * + * If anytime_only is true, only non-transactional fields are flushed + * (numscans, tuples_returned, tuples_fetched, blocks_fetched, blocks_hit). + * Transactional fields remain pending until transaction boundary. + * + * Some of the stats are copied to the corresponding pending database stats + * entry when successfully flushing. */ bool -pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { Oid dboid; PgStat_TableStatus *lstats; /* pending stats entry */ @@ -807,12 +850,13 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o PgStat_StatTabEntry *tabentry; /* table entry of shared stats */ PgStat_StatDBEntry *dbentry; /* pending database entry */ - Assert(!anytime_only); - dboid = entry_ref->shared_entry->key.dboid; lstats = (PgStat_TableStatus *) entry_ref->pending; shtabstats = (PgStatShared_Relation *) entry_ref->shared_stats; + /* this is a partial flush if in anytime only mode */ + *is_partial = anytime_only; + /* * Ignore entries that didn't accumulate any actual counts, such as * indexes that were opened by the planner but not used. @@ -824,19 +868,36 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o if (!pgstat_lock_entry(entry_ref, nowait)) return false; - /* add the values to the shared entry. */ tabentry = &shtabstats->stats; - tabentry->numscans += lstats->counts.numscans; - if (lstats->counts.numscans) + if (anytime_only) { - TimestampTz t = GetCurrentTransactionStopTimestamp(); - if (t > tabentry->lastscan) - tabentry->lastscan = t; + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, true); + + pgstat_unlock_entry(entry_ref); + + /* Also update the corresponding fields in database stats */ + dbentry = pgstat_prep_database_pending(dboid); + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + + /* + * Clear the flushed fields from pending stats to prevent + * double-counting when we flush all fields 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; } - tabentry->tuples_returned += lstats->counts.tuples_returned; - tabentry->tuples_fetched += lstats->counts.tuples_fetched; + + /* Flush non-transactional statistics */ + flush_relation_anytime_stats(tabentry, &lstats->counts, false); + tabentry->tuples_inserted += lstats->counts.tuples_inserted; tabentry->tuples_updated += lstats->counts.tuples_updated; tabentry->tuples_deleted += lstats->counts.tuples_deleted; @@ -866,9 +927,6 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o */ tabentry->ins_since_vacuum += lstats->counts.tuples_inserted; - tabentry->blocks_fetched += lstats->counts.blocks_fetched; - tabentry->blocks_hit += lstats->counts.blocks_hit; - /* Clamp live_tuples in case of negative delta_live_tuples */ tabentry->live_tuples = Max(tabentry->live_tuples, 0); /* Likewise for dead_tuples */ @@ -878,13 +936,11 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_o /* The entry was successfully flushed, add the same to database stats */ dbentry = pgstat_prep_database_pending(dboid); - dbentry->tuples_returned += lstats->counts.tuples_returned; - dbentry->tuples_fetched += lstats->counts.tuples_fetched; + UPDATE_DATABASE_ANYTIME_STATS(dbentry, &lstats->counts); + dbentry->tuples_inserted += lstats->counts.tuples_inserted; dbentry->tuples_updated += lstats->counts.tuples_updated; dbentry->tuples_deleted += lstats->counts.tuples_deleted; - dbentry->blocks_fetched += lstats->counts.blocks_fetched; - dbentry->blocks_hit += lstats->counts.blocks_hit; return true; } diff --git a/src/backend/utils/activity/pgstat_subscription.c b/src/backend/utils/activity/pgstat_subscription.c index c4614817966..43fec86c635 100644 --- a/src/backend/utils/activity/pgstat_subscription.c +++ b/src/backend/utils/activity/pgstat_subscription.c @@ -116,7 +116,8 @@ pgstat_fetch_stat_subscription(Oid subid) * false without flushing the entry. Otherwise returns true. */ bool -pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_BackendSubEntry *localent; PgStatShared_Subscription *shsubent; @@ -126,6 +127,9 @@ pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anyti localent = (PgStat_BackendSubEntry *) entry_ref->pending; shsubent = (PgStatShared_Subscription *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + /* localent always has non-zero content */ if (!pgstat_lock_entry(entry_ref, nowait)) diff --git a/src/include/pgstat.h b/src/include/pgstat.h index ef856dbf55b..06639198f28 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -21,6 +21,7 @@ #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/pgstat_kind.h" #include "utils/relcache.h" +#include "utils/timeout.h" #include "utils/wait_event.h" /* for backward compatibility */ /* IWYU pragma: export */ @@ -537,10 +538,11 @@ extern void pgstat_report_anytime_stat(bool force); extern void pgstat_force_next_flush(void); /* - * Schedule the next anytime stats update timeout. + * Schedule the next anytime stats update timeout and mark that we have + * mixed anytime stats pending. * * This should be called whenever accumulating statistics that support - * FLUSH_ANYTIME flushing mode. + * FLUSH_ANYTIME or FLUSH_MIXED flushing modes. */ #define pgstat_schedule_anytime_update() \ do { \ @@ -703,37 +705,58 @@ extern void pgstat_report_analyze(Relation rel, #define pgstat_count_heap_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_getnext(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_heap_fetch(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_scan(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.numscans++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_index_tuples(rel, n) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.tuples_returned += (n); \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_read(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_fetched++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) #define pgstat_count_buffer_hit(rel) \ do { \ if (pgstat_should_count_relation(rel)) \ + { \ (rel)->pgstat_info->counts.blocks_hit++; \ + pgstat_schedule_anytime_update(); \ + } \ } while (0) extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n); diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 607f4255268..1a2114aad8a 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -322,8 +322,10 @@ typedef struct PgStat_KindInfo * that cannot use PgStat_EntryRef->pending. * * The anytime_only parameter indicates whether this is an anytime flush. + * The is_partial parameter indicates whether this is a partial flush. */ - bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, bool anytime_only); + bool (*flush_pending_cb) (PgStat_EntryRef *sr, bool nowait, + bool anytime_only, bool *is_partial); /* * For variable-numbered stats: delete pending stats. Optional. @@ -757,7 +759,8 @@ extern void AtEOXact_PgStat_Database(bool isCommit, bool parallel); extern PgStat_StatDBEntry *pgstat_prep_database_pending(Oid dboid); extern void pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts); -extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -765,7 +768,8 @@ extern void pgstat_database_reset_timestamp_cb(PgStatShared_Common *header, Time * Functions in pgstat_function.c */ -extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_function_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -790,7 +794,8 @@ extern void AtEOSubXact_PgStat_Relations(PgStat_SubXactStatus *xact_state, bool 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, bool anytime_only); +extern bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref); extern void pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); @@ -858,7 +863,8 @@ extern void pgstat_wal_snapshot_cb(void); * Functions in pgstat_subscription.c */ -extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only); +extern bool pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial); extern void pgstat_subscription_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..11e3e57806d 100644 --- a/src/test/isolation/expected/stats.out +++ b/src/test/isolation/expected/stats.out @@ -2245,6 +2245,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..aef582e7582 100644 --- a/src/test/isolation/expected/stats_1.out +++ b/src/test/isolation/expected/stats_1.out @@ -2253,6 +2253,108 @@ 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_track_counts_off s2_table_select s1_sleep s1_table_stats s2_track_counts_on 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_track_counts_off: SET track_counts = off; +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_track_counts_on: SET track_counts = on; +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 +--------+------------+---------+---------+---------+----------+----------+------------ + 2| 2| 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..47414eb6009 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'; } @@ -156,6 +162,8 @@ step s2_func_stats { } # Relation stats steps +step s2_track_counts_on { SET track_counts = on; } +step s2_track_counts_off { SET track_counts = off; } step s2_table_select { SELECT * FROM test_stat_tab ORDER BY key, value; } step s2_table_update_k1 { UPDATE test_stat_tab SET value = value + 1 WHERE key = 'k1';} @@ -435,6 +443,23 @@ 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_track_counts_off + s2_table_select + s1_sleep + s1_table_stats + s2_track_counts_on + 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 diff --git a/src/test/modules/test_custom_stats/test_custom_var_stats.c b/src/test/modules/test_custom_stats/test_custom_var_stats.c index 207e841911b..ffcda7b6c7a 100644 --- a/src/test/modules/test_custom_stats/test_custom_var_stats.c +++ b/src/test/modules/test_custom_stats/test_custom_var_stats.c @@ -84,7 +84,8 @@ static dsa_area *custom_stats_description_dsa = NULL; /* Flush callback: merge pending stats into shared memory */ static bool test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, - bool nowait, bool anytime_only); + bool nowait, bool anytime_only, + bool *is_partial); /* Serialization callback: write auxiliary entry data */ static void test_custom_stats_var_to_serialized_data(const PgStat_HashKey *key, @@ -152,7 +153,8 @@ _PG_init(void) * Returns false only if nowait=true and lock acquisition fails. */ static bool -test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, bool anytime_only) +test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, + bool anytime_only, bool *is_partial) { PgStat_StatCustomVarEntry *pending_entry; PgStatShared_CustomVarEntry *shared_entry; @@ -160,6 +162,9 @@ test_custom_stats_var_flush_pending_cb(PgStat_EntryRef *entry_ref, bool nowait, pending_entry = (PgStat_StatCustomVarEntry *) entry_ref->pending; shared_entry = (PgStatShared_CustomVarEntry *) entry_ref->shared_stats; + /* this is not a partial flush */ + *is_partial = false; + if (!pgstat_lock_entry(entry_ref, nowait)) return false; -- 2.34.1 --OI5irqZsxWxBW9nT-- ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-01-19 06:27 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-11-08 06:57 [PATCH v11 2/7] Row pattern recognition patch (parse/analysis). Tatsuo Ishii <[email protected]> 2026-01-19 06:27 [PATCH v9 5/5] Change RELATION and DATABASE stats to anytime flush Bertrand Drouvot <[email protected]> 2026-01-19 06:27 [PATCH v10 5/5] Change RELATION and DATABASE stats to anytime flush Bertrand Drouvot <[email protected]> 2026-01-19 06:27 [PATCH v11 5/5] Change RELATION and DATABASE stats to anytime flush Bertrand Drouvot <[email protected]> 2026-01-19 06:27 [PATCH v7 5/5] Change RELATION and DATABASE stats to anytime flush Bertrand Drouvot <[email protected]> 2026-01-19 06:27 [PATCH v6 5/5] Change RELATION and DATABASE stats to anytime flush Bertrand Drouvot <[email protected]> 2026-01-19 06:27 [PATCH v8 5/5] Change RELATION and DATABASE stats to anytime flush Bertrand Drouvot <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox