agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v12 2/7] Row pattern recognition patch (parse/analysis). 9+ messages / 2 participants [nested] [flat]
* [PATCH v12 2/7] Row pattern recognition patch (parse/analysis). @ 2023-12-04 11:23 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Tatsuo Ishii @ 2023-12-04 11:23 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(Fri_Dec__8_10_16_13_2023_489)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v12-0003-Row-pattern-recognition-patch-planner.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v5 2/4] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/access/transam/xlog.c | 4 +-- src/backend/utils/activity/pgstat.c | 16 +++++++++- src/backend/utils/activity/pgstat_backend.c | 4 +-- src/backend/utils/activity/pgstat_io.c | 2 +- src/backend/utils/activity/pgstat_slru.c | 2 +- src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 1 + src/include/utils/guc_hooks.h | 1 + 10 files changed, 66 insertions(+), 7 deletions(-) 45.9% doc/src/sgml/ 6.5% src/backend/access/transam/ 31.8% src/backend/utils/activity/ 12.3% src/backend/utils/misc/ 3.2% src/include/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5560b95ee60..3136816a933 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8834,6 +8834,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which non-transactional statistics are made visible + during running transactions. Non-transactional statistics include, for + example, WAL activity and I/O operations. + They become visible at that interval in monitoring views such as + <link linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link> + and <link linkend="monitoring-pg-stat-wal-view"> <structname>pg_stat_wal</structname></link> + during running transactions. + If this value is specified without units, it is taken as milliseconds. + The default is 10 seconds (<literal>10s</literal>), which is probably + about the smallest value you would want in practice for long running + transactions. + </para> + <note> + <para> + This parameter does not affect transactional statistics such as + <structname>pg_stat_all_tables</structname> columns (like + <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + <structfield>n_tup_del</structfield>), which are always flushed at transaction + boundaries to maintain consistency. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 9503aea5b4d..31523dea923 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -1087,7 +1087,7 @@ XLogInsertRecord(XLogRecData *rdata, /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); /* Required for the flush of pending stats WAL data */ pgstat_report_fixed = true; @@ -2073,7 +2073,7 @@ AdvanceXLInsertBuffer(XLogRecPtr upto, TimeLineID tli, bool opportunistic) /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, - PGSTAT_MIN_INTERVAL); + pgstat_flush_interval); /* * Required for the flush of pending stats WAL data, per diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 2c9454677e9..dd174129403 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -203,6 +203,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -1304,7 +1305,7 @@ pgstat_prep_pending_entry(PgStat_Kind kind, Oid dboid, uint64 objid, bool *creat /* Schedule next anytime stats update timeout */ if (kind_info->flush_mode == FLUSH_ANYTIME && IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); } return entry_ref; @@ -2172,6 +2173,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index 9dcb24db975..f5b8c7b039c 100644 --- a/src/backend/utils/activity/pgstat_backend.c +++ b/src/backend/utils/activity/pgstat_backend.c @@ -69,7 +69,7 @@ pgstat_count_backend_io_op_time(IOObject io_object, IOContext io_context, /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); backend_has_iostats = true; pgstat_report_fixed = true; @@ -89,7 +89,7 @@ pgstat_count_backend_io_op(IOObject io_object, IOContext io_context, /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); backend_has_iostats = true; pgstat_report_fixed = true; diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c index 53dbf2a514b..b69a1e26f7d 100644 --- a/src/backend/utils/activity/pgstat_io.c +++ b/src/backend/utils/activity/pgstat_io.c @@ -82,7 +82,7 @@ pgstat_count_io_op(IOObject io_object, IOContext io_context, IOOp io_op, /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); have_iostats = true; pgstat_report_fixed = true; diff --git a/src/backend/utils/activity/pgstat_slru.c b/src/backend/utils/activity/pgstat_slru.c index 1d16cde1889..36231ee874b 100644 --- a/src/backend/utils/activity/pgstat_slru.c +++ b/src/backend/utils/activity/pgstat_slru.c @@ -226,7 +226,7 @@ get_slru_entry(int slru_idx) /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); have_slrustats = true; pgstat_report_fixed = true; diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index f0260e6e412..3bb43362e51 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2782,6 +2782,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index c4f92fcdac8..6ce5a250170 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -669,6 +669,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 1651f16f966..e0f222695bf 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -816,6 +816,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index b6ecb0e769f..3a2ae6c41cd 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, -- 2.34.1 --WLtTrIUEfQDmERxy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0003-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v6 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 34 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 16 +++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + 6 files changed, 64 insertions(+), 4 deletions(-) 59.1% doc/src/sgml/ 14.2% src/backend/utils/activity/ 14.5% src/backend/utils/misc/ 12.0% src/include/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index f1af1505cf3..20666679f90 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8871,6 +8871,40 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which statistics that can be updated while a + transaction is still running are made visible. These include, for example, + WAL activity and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link> + and + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link>. + Other statistics are only made visible at transaction end and are not + affected by this setting. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 411b65aae3e..79eb59b5625 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -123,6 +123,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -203,6 +205,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2164,6 +2167,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index c1f1603cd39..fc0e4259b36 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2789,6 +2789,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 1ae594af843..3f998a0bea0 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -673,6 +673,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b340a680614..ef856dbf55b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,9 +35,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); #define pgstat_schedule_anytime_update() \ do { \ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ } while (0) extern void pgstat_reset_counters(void); @@ -828,6 +825,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index b6ecb0e769f..3a2ae6c41cd 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, -- 2.34.1 --DxqFthphbM+ha9Dy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v6-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v7 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 16 ++++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 7 files changed, 66 insertions(+), 6 deletions(-) 51.8% doc/src/sgml/ 13.3% src/backend/utils/activity/ 13.6% src/backend/utils/misc/ 11.3% src/include/ 9.8% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 6bc2690ce07..383bbe3a132 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8923,6 +8923,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index a4ff64dc5ce..dd85a27c52f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -123,6 +123,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -203,6 +205,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2164,6 +2167,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 271c033952e..d2734caafea 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b340a680614..ef856dbf55b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,9 +35,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); #define pgstat_schedule_anytime_update() \ do { \ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ } while (0) extern void pgstat_reset_counters(void); @@ -828,6 +825,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 7be1b281776..22e2a75dcb9 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,10 +164,11 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -183,11 +184,12 @@ like($result, qr/^anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select * from test_custom_stats_var_report('entry2'); -- 2.34.1 --C2xzVbxFmVrP7k6O Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v7-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v8 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 16 ++++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 7 files changed, 66 insertions(+), 6 deletions(-) 51.8% doc/src/sgml/ 13.3% src/backend/utils/activity/ 13.6% src/backend/utils/misc/ 11.3% src/include/ 9.8% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index faf0bdb62aa..03875b490b7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8932,6 +8932,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index a4ff64dc5ce..dd85a27c52f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -123,6 +123,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -203,6 +205,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2164,6 +2167,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 271c033952e..d2734caafea 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b340a680614..ef856dbf55b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,9 +35,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); #define pgstat_schedule_anytime_update() \ do { \ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ } while (0) extern void pgstat_reset_counters(void); @@ -828,6 +825,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 7be1b281776..22e2a75dcb9 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,10 +164,11 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -183,11 +184,12 @@ like($result, qr/^anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select * from test_custom_stats_var_report('entry2'); -- 2.34.1 --OI5irqZsxWxBW9nT Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v8-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v9 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 13 ++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/backend/utils/misc/timeout.c | 6 ++++ src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + src/include/utils/timeout.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 9 files changed, 70 insertions(+), 6 deletions(-) 51.0% doc/src/sgml/ 10.6% src/backend/utils/activity/ 15.9% src/backend/utils/misc/ 3.6% src/include/utils/ 9.0% src/include/ 9.6% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index faf0bdb62aa..03875b490b7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8932,6 +8932,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 419dc512d9b..578c575dbfb 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -123,6 +123,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -203,6 +205,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2170,6 +2173,16 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_all_timeouts_initialized()) + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 271c033952e..d2734caafea 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/backend/utils/misc/timeout.c b/src/backend/utils/misc/timeout.c index ddba5dc607c..85c4260d1db 100644 --- a/src/backend/utils/misc/timeout.c +++ b/src/backend/utils/misc/timeout.c @@ -828,3 +828,9 @@ get_timeout_finish_time(TimeoutId id) { return all_timeouts[id].fin_time; } + +bool +get_all_timeouts_initialized(void) +{ + return all_timeouts_initialized; +} diff --git a/src/include/pgstat.h b/src/include/pgstat.h index f0f546d419a..7829c563316 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,9 +35,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -549,7 +546,7 @@ extern void pgstat_force_next_flush(void); do { \ if (IsUnderPostmaster && !pgstat_pending_anytime) \ { \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ pgstat_pending_anytime = true; \ } \ } while (0) @@ -833,6 +830,7 @@ extern PGDLLIMPORT bool pgstat_pending_anytime; extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index 10723bb664c..fe7327de209 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -93,5 +93,6 @@ extern bool get_timeout_active(TimeoutId id); extern bool get_timeout_indicator(TimeoutId id, bool reset_indicator); extern TimestampTz get_timeout_start_time(TimeoutId id); extern TimestampTz get_timeout_finish_time(TimeoutId id); +extern bool get_all_timeouts_initialized(void); #endif /* TIMEOUT_H */ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 7be1b281776..22e2a75dcb9 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,10 +164,11 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -183,11 +184,12 @@ like($result, qr/^anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select * from test_custom_stats_var_report('entry2'); -- 2.34.1 --aq/6bi8L6WORnI+9 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v9-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v10 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 13 ++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/backend/utils/misc/timeout.c | 6 ++++ src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + src/include/utils/timeout.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 9 files changed, 70 insertions(+), 6 deletions(-) 51.0% doc/src/sgml/ 10.6% src/backend/utils/activity/ 15.9% src/backend/utils/misc/ 3.6% src/include/utils/ 9.0% src/include/ 9.6% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 20dbcaeb3ee..1eed71007a7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8929,6 +8929,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index ddd331e2c81..fd6ab0db16f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -124,6 +124,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -204,6 +206,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2171,6 +2174,16 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_all_timeouts_initialized()) + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 9507778415d..073e08c7892 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/backend/utils/misc/timeout.c b/src/backend/utils/misc/timeout.c index ddba5dc607c..85c4260d1db 100644 --- a/src/backend/utils/misc/timeout.c +++ b/src/backend/utils/misc/timeout.c @@ -828,3 +828,9 @@ get_timeout_finish_time(TimeoutId id) { return all_timeouts[id].fin_time; } + +bool +get_all_timeouts_initialized(void) +{ + return all_timeouts_initialized; +} diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b011a315679..90237c70829 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -34,9 +34,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); do { \ if (IsUnderPostmaster && !pgstat_pending_anytime) \ { \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ pgstat_pending_anytime = true; \ } \ } while (0) @@ -831,6 +828,7 @@ extern PGDLLIMPORT bool pgstat_pending_anytime; extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index 10723bb664c..fe7327de209 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -93,5 +93,6 @@ extern bool get_timeout_active(TimeoutId id); extern bool get_timeout_indicator(TimeoutId id, bool reset_indicator); extern TimestampTz get_timeout_start_time(TimeoutId id); extern TimestampTz get_timeout_finish_time(TimeoutId id); +extern bool get_all_timeouts_initialized(void); #endif /* TIMEOUT_H */ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 7be1b281776..22e2a75dcb9 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,10 +164,11 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -183,11 +184,12 @@ like($result, qr/^anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select * from test_custom_stats_var_report('entry2'); -- 2.34.1 --NVvBxFuyV/R+1/8/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v10-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v11 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 13 ++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/backend/utils/misc/timeout.c | 6 ++++ src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + src/include/utils/timeout.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 9 files changed, 70 insertions(+), 6 deletions(-) 51.0% doc/src/sgml/ 10.6% src/backend/utils/activity/ 15.9% src/backend/utils/misc/ 3.6% src/include/utils/ 9.0% src/include/ 9.6% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 20dbcaeb3ee..1eed71007a7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8929,6 +8929,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index ddd331e2c81..fd6ab0db16f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -124,6 +124,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -204,6 +206,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2171,6 +2174,16 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_all_timeouts_initialized()) + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 9507778415d..073e08c7892 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/backend/utils/misc/timeout.c b/src/backend/utils/misc/timeout.c index ddba5dc607c..85c4260d1db 100644 --- a/src/backend/utils/misc/timeout.c +++ b/src/backend/utils/misc/timeout.c @@ -828,3 +828,9 @@ get_timeout_finish_time(TimeoutId id) { return all_timeouts[id].fin_time; } + +bool +get_all_timeouts_initialized(void) +{ + return all_timeouts_initialized; +} diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b011a315679..90237c70829 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -34,9 +34,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); do { \ if (IsUnderPostmaster && !pgstat_pending_anytime) \ { \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ pgstat_pending_anytime = true; \ } \ } while (0) @@ -831,6 +828,7 @@ extern PGDLLIMPORT bool pgstat_pending_anytime; extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index 10723bb664c..fe7327de209 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -93,5 +93,6 @@ extern bool get_timeout_active(TimeoutId id); extern bool get_timeout_indicator(TimeoutId id, bool reset_indicator); extern TimestampTz get_timeout_start_time(TimeoutId id); extern TimestampTz get_timeout_finish_time(TimeoutId id); +extern bool get_all_timeouts_initialized(void); #endif /* TIMEOUT_H */ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 6ba4022418f..920443487c0 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,11 +164,12 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; SET LOCAL stats_fetch_consistency = none; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'fixed_anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -184,12 +185,13 @@ like($result, qr/^fixed_anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; SET LOCAL stats_fetch_consistency = none; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'var_anytime:'||calls from test_custom_stats_var_report('entry2'); -- 2.34.1 --2MEBAGW8+kohXisi Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v11-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v4 2/4] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/storage/lmgr/proc.c | 2 +- src/backend/tcop/postgres.c | 2 +- src/backend/utils/activity/pgstat.c | 15 +++++++++ src/backend/utils/init/postinit.c | 2 +- src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 1 + src/include/utils/guc_hooks.h | 1 + 9 files changed, 63 insertions(+), 3 deletions(-) 55.5% doc/src/sgml/ 5.3% src/backend/storage/lmgr/ 12.6% src/backend/utils/activity/ 5.3% src/backend/utils/init/ 14.9% src/backend/utils/misc/ 3.9% src/include/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5560b95ee60..3136816a933 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8834,6 +8834,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which non-transactional statistics are made visible + during running transactions. Non-transactional statistics include, for + example, WAL activity and I/O operations. + They become visible at that interval in monitoring views such as + <link linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link> + and <link linkend="monitoring-pg-stat-wal-view"> <structname>pg_stat_wal</structname></link> + during running transactions. + If this value is specified without units, it is taken as milliseconds. + The default is 10 seconds (<literal>10s</literal>), which is probably + about the smallest value you would want in practice for long running + transactions. + </para> + <note> + <para> + This parameter does not affect transactional statistics such as + <structname>pg_stat_all_tables</structname> columns (like + <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + <structfield>n_tup_del</structfield>), which are always flushed at transaction + boundaries to maintain consistency. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index 012705a2ee6..caa6eecca88 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -1669,7 +1669,7 @@ ProcSleep(LOCALLOCK *locallock) } while (myWaitStatus == PROC_WAIT_STATUS_WAITING); if (anytime_timeout_was_active) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); /* * Disable the timers, if they are still running. As in LockErrorCleanup, diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 132fae61423..c0e81cb13d0 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -3543,7 +3543,7 @@ ProcessInterrupts(void) /* Schedule next timeout */ enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, - PGSTAT_MIN_INTERVAL); + pgstat_flush_interval); } if (ProcSignalBarrierPending) diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index ab4d9088a9a..ca08dd49cd7 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -113,6 +113,7 @@ #include "utils/memutils.h" #include "utils/pgstat_internal.h" #include "utils/timestamp.h" +#include "utils/timeout.h" /* ---------- @@ -202,6 +203,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2165,6 +2167,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 6076f531c4a..c7c0d618671 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -768,7 +768,7 @@ InitPostgres(const char *in_dbname, Oid dboid, IdleStatsUpdateTimeoutHandler); RegisterTimeout(ANYTIME_STATS_UPDATE_TIMEOUT, AnytimeStatsUpdateTimeoutHandler); - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); } /* diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index f0260e6e412..3bb43362e51 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2782,6 +2782,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index c4f92fcdac8..6ce5a250170 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -669,6 +669,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 1651f16f966..e0f222695bf 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -816,6 +816,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index b6ecb0e769f..3a2ae6c41cd 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, -- 2.34.1 --wR/mWGXukst4NraF Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0003-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-01-28 07:53 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-12-04 11:23 [PATCH v12 2/7] Row pattern recognition patch (parse/analysis). Tatsuo Ishii <[email protected]> 2026-01-28 07:53 [PATCH v10 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v4 2/4] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v5 2/4] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v7 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v8 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v9 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v11 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v6 3/5] Add GUC to specify non-transactional statistics flush interval 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