agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v21 3/8] Row pattern recognition patch (rewriter). 9+ messages / 2 participants [nested] [flat]
* [PATCH v21 3/8] Row pattern recognition patch (rewriter). @ 2024-08-26 04:32 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Tatsuo Ishii @ 2024-08-26 04:32 UTC (permalink / raw) --- src/backend/utils/adt/ruleutils.c | 103 ++++++++++++++++++++++++++++++ 1 file changed, 103 insertions(+) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 00eda1b34c..dff7e169e7 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -426,6 +426,10 @@ static void get_rule_groupingset(GroupingSet *gset, List *targetlist, bool omit_parens, deparse_context *context); static void get_rule_orderby(List *orderList, List *targetList, bool force_colno, deparse_context *context); +static void get_rule_pattern(List *patternVariable, List *patternRegexp, + bool force_colno, deparse_context *context); +static void get_rule_define(List *defineClause, List *patternVariables, + bool force_colno, deparse_context *context); static void get_rule_windowclause(Query *query, deparse_context *context); static void get_rule_windowspec(WindowClause *wc, List *targetList, deparse_context *context); @@ -6460,6 +6464,67 @@ get_rule_orderby(List *orderList, List *targetList, } } +/* + * Display a PATTERN clause. + */ +static void +get_rule_pattern(List *patternVariable, List *patternRegexp, + bool force_colno, deparse_context *context) +{ + StringInfo buf = context->buf; + const char *sep; + ListCell *lc_var, + *lc_reg = list_head(patternRegexp); + + sep = ""; + appendStringInfoChar(buf, '('); + foreach(lc_var, patternVariable) + { + char *variable = strVal((String *) lfirst(lc_var)); + char *regexp = NULL; + + if (lc_reg != NULL) + { + regexp = strVal((String *) lfirst(lc_reg)); + + lc_reg = lnext(patternRegexp, lc_reg); + } + + appendStringInfo(buf, "%s%s", sep, variable); + if (regexp !=NULL) + appendStringInfoString(buf, regexp); + + sep = " "; + } + appendStringInfoChar(buf, ')'); +} + +/* + * Display a DEFINE clause. + */ +static void +get_rule_define(List *defineClause, List *patternVariables, + bool force_colno, deparse_context *context) +{ + StringInfo buf = context->buf; + const char *sep; + ListCell *lc_var, + *lc_def; + + sep = " "; + Assert(list_length(patternVariables) == list_length(defineClause)); + + forboth(lc_var, patternVariables, lc_def, defineClause) + { + char *varName = strVal(lfirst(lc_var)); + TargetEntry *te = (TargetEntry *) lfirst(lc_def); + + appendStringInfo(buf, "%s%s AS ", sep, varName); + get_rule_expr((Node *) te->expr, context, false); + sep = ",\n "; + } +} + /* * Display a WINDOW clause. * @@ -6597,6 +6662,44 @@ get_rule_windowspec(WindowClause *wc, List *targetList, appendStringInfoString(buf, "EXCLUDE GROUP "); else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES) appendStringInfoString(buf, "EXCLUDE TIES "); + /* RPR */ + if (wc->rpSkipTo == ST_NEXT_ROW) + appendStringInfoString(buf, + "\n AFTER MATCH SKIP TO NEXT ROW "); + else if (wc->rpSkipTo == ST_PAST_LAST_ROW) + appendStringInfoString(buf, + "\n AFTER MATCH SKIP PAST LAST ROW "); + else if (wc->rpSkipTo == ST_FIRST_VARIABLE) + appendStringInfo(buf, + "\n AFTER MATCH SKIP TO FIRST %s ", + wc->rpSkipVariable); + else if (wc->rpSkipTo == ST_LAST_VARIABLE) + appendStringInfo(buf, + "\n AFTER MATCH SKIP TO LAST %s ", + wc->rpSkipVariable); + else if (wc->rpSkipTo == ST_VARIABLE) + appendStringInfo(buf, + "\n AFTER MATCH SKIP TO %s ", + wc->rpSkipVariable); + + if (wc->initial) + appendStringInfoString(buf, "\n INITIAL"); + + if (wc->patternVariable) + { + appendStringInfoString(buf, "\n PATTERN "); + get_rule_pattern(wc->patternVariable, wc->patternRegexp, + false, context); + } + + if (wc->defineClause) + { + appendStringInfoString(buf, "\n DEFINE\n"); + get_rule_define(wc->defineClause, wc->patternVariable, + false, context); + appendStringInfoChar(buf, ' '); + } + /* we will now have a trailing space; remove it */ buf->len--; } -- 2.25.1 ----Next_Part(Mon_Aug_26_13_39_47_2024_878)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v21-0004-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 -- 2024-08-26 04:32 [PATCH v21 3/8] Row pattern recognition patch (rewriter). Tatsuo Ishii <[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 v5 2/4] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[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 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