Re: Fujii Masao
> utility.c:946:4: warning: label followed by a declaration is a C23 extension
> [-Wc23-extensions]
Thanks, my compiler didn't throw that. { } block added in v3.
> RequestCheckpoint(CHECKPOINT_WAIT |
> + (immediate ?
> CHECKPOINT_IMMEDIATE : 0) |
> + (flush_all ?
> CHECKPOINT_FLUSH_ALL : 0) |
>
> Some users might want to trigger a spread checkpoint but not wait for
> it to finish, since it could take a long time? If that's a valid use case,
> maybe we should add a WAIT option to let users choose whether to wait for
> the checkpoint to complete or not?
Do we want that? The checkpoint is only effective when it's finished,
and running `psql -c "checkpoint (wait false)"` might make people
shoot themselves into the foot.
Christoph
>From 2f94017b467b930038a5e67cf5a59743c8bc8275 Mon Sep 17 00:00:00 2001
From: Christoph Berg <m...@debian.org>
Date: Fri, 30 May 2025 17:58:35 +0200
Subject: [PATCH v3] Add mode and flush_all options to checkpoint
Field reports indicate that some users are running CHECKPOINT just
before shutting down to reduce the amount of data that the shutdown
checkpoint has to write out, making restarts faster.
That works well unless big unlogged tables are in play; a regular
CHECKPOINT does not flush these. Hence, add a CHECKPOINT option to force
flushing of all relations. To control the write load during these
checkpoints, add an MODE option to choose between IMMEDIATE and SPREAD.
IMMEDIATE is the spelling used by the log_checkpoints message; FAST is
accepted as alternative spelling, it is used by pg_basebackup
--checkpoint=fast.
---
doc/src/sgml/ref/checkpoint.sgml | 76 ++++++++++++++++++++++++-----
src/backend/parser/gram.y | 8 +++
src/backend/tcop/utility.c | 60 ++++++++++++++++++-----
src/bin/psql/tab-complete.in.c | 7 +++
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/stats.out | 6 +--
src/test/regress/sql/stats.sql | 6 +--
7 files changed, 136 insertions(+), 28 deletions(-)
diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml
index db011a47d04..3014f90f9cd 100644
--- a/doc/src/sgml/ref/checkpoint.sgml
+++ b/doc/src/sgml/ref/checkpoint.sgml
@@ -21,7 +21,12 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CHECKPOINT
+CHECKPOINT [ ( option [, ...] ) ]
+
+<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
+
+ MODE { IMMEDIATE | FAST | SPREAD }
+ FLUSH_ALL [ <replaceable class="parameter">boolean</replaceable> ]
</synopsis>
</refsynopsisdiv>
@@ -31,20 +36,11 @@ CHECKPOINT
<para>
A checkpoint is a point in the write-ahead log sequence at which
all data files have been updated to reflect the information in the
- log. All data files will be flushed to disk. Refer to
+ log. All data files will be flushed to disk, except for relations marked <literal>UNLOGGED</literal>. Refer to
<xref linkend="wal-configuration"/> for more details about what happens
during a checkpoint.
</para>
- <para>
- The <command>CHECKPOINT</command> command forces an immediate
- checkpoint when the command is issued, without waiting for a
- regular checkpoint scheduled by the system (controlled by the settings in
- <xref linkend="runtime-config-wal-checkpoints"/>).
- <command>CHECKPOINT</command> is not intended for use during normal
- operation.
- </para>
-
<para>
If executed during recovery, the <command>CHECKPOINT</command> command
will force a restartpoint (see <xref linkend="wal-configuration"/>)
@@ -56,6 +52,64 @@ CHECKPOINT
the <xref linkend="predefined-role-pg-checkpoint"/>
role can call <command>CHECKPOINT</command>.
</para>
+
+ <para>
+ Running <command>CHECKPOINT</command> is not required during normal
+ operation; the system schedules checkpoints automatically (controlled by
+ the settings in <xref linkend="runtime-config-wal-checkpoints"/>).
+ <command>CHECKPOINT</command> can be beneficial to run before operations
+ such as binary backups or shutting down <productname>PostgreSQL</productname>
+ as this can reduce the amount of data to be flushed for the next checkpoint
+ within these operations. Specifically, <literal>UNLOGGED</literal> table
+ data is otherwise only flushed by the shutdown checkpoint.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>MODE</literal></term>
+ <listitem>
+ <para>
+ The <command>CHECKPOINT</command> command forces an immediate
+ checkpoint by default when the command is issued, without waiting for a
+ regular checkpoint scheduled by the system. <literal>FAST</literal> is a
+ synonym for <literal>IMMEDIATE</literal>.
+ </para>
+ <para>
+ A <literal>SPREAD</literal> checkpoint will instead spread out the write load
+ as determined by the <xref linkend="guc-checkpoint-completion-target"/>
+ setting, like the system-scheduled checkpoints.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FLUSH_ALL</literal></term>
+ <listitem>
+ <para>
+ Requests the checkpoint to also flush data of <literal>UNLOGGED</literal>
+ relations. Defaults to off.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">boolean</replaceable></term>
+ <listitem>
+ <para>
+ Specifies whether the selected option should be turned on or off.
+ You can write <literal>TRUE</literal>, <literal>ON</literal>, or
+ <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+ <literal>OFF</literal>, or <literal>0</literal> to disable it. The
+ <replaceable class="parameter">boolean</replaceable> value can also
+ be omitted, in which case <literal>TRUE</literal> is assumed.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</refsect1>
<refsect1>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0b5652071d1..731d844231a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2034,6 +2034,14 @@ CheckPointStmt:
CheckPointStmt *n = makeNode(CheckPointStmt);
$$ = (Node *) n;
+ n->options = NULL;
+ }
+ | CHECKPOINT '(' utility_option_list ')'
+ {
+ CheckPointStmt *n = makeNode(CheckPointStmt);
+
+ $$ = (Node *) n;
+ n->options = $3;
}
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 25fe3d58016..8bfdff76fac 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -943,17 +943,55 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case T_CheckPointStmt:
- if (!has_privs_of_role(GetUserId(), ROLE_PG_CHECKPOINT))
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- /* translator: %s is name of a SQL command, eg CHECKPOINT */
- errmsg("permission denied to execute %s command",
- "CHECKPOINT"),
- errdetail("Only roles with privileges of the \"%s\" role may execute this command.",
- "pg_checkpoint")));
-
- RequestCheckpoint(CHECKPOINT_IMMEDIATE | CHECKPOINT_WAIT |
- (RecoveryInProgress() ? 0 : CHECKPOINT_FORCE));
+ {
+ CheckPointStmt *stmt = (CheckPointStmt *) parsetree;
+ ListCell *lc;
+ bool immediate = true;
+ bool flush_all = false;
+
+ if (!has_privs_of_role(GetUserId(), ROLE_PG_CHECKPOINT))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ /* translator: %s is name of a SQL command, eg CHECKPOINT */
+ errmsg("permission denied to execute %s command",
+ "CHECKPOINT"),
+ errdetail("Only roles with privileges of the \"%s\" role may execute this command.",
+ "pg_checkpoint")));
+
+ /* Parse options list */
+ foreach(lc, stmt->options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "mode") == 0)
+ {
+ char *mode = defGetString(opt);
+ if (strcmp(mode, "immediate") == 0 || strcmp(mode, "fast") == 0)
+ immediate = true;
+ else if (strcmp(mode, "spread") == 0)
+ immediate = false;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("CHECKPOINT option \"%s\" argument \"%s\" is invalid", opt->defname, mode),
+ errhint("valid arguments are \"IMMEDIATE\" (or \"FAST\") and \"SPREAD\""),
+ parser_errposition(pstate, opt->location)));
+ }
+ else if (strcmp(opt->defname, "flush_all") == 0)
+ flush_all = defGetBoolean(opt);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unrecognized CHECKPOINT option \"%s\"", opt->defname),
+ errhint("valid options are \"IMMEDIATE\" and \"FLUSH_ALL\""),
+ parser_errposition(pstate, opt->location)));
+ }
+
+ RequestCheckpoint(CHECKPOINT_WAIT |
+ (immediate ? CHECKPOINT_IMMEDIATE : 0) |
+ (flush_all ? CHECKPOINT_FLUSH_ALL : 0) |
+ (RecoveryInProgress() ? 0 : CHECKPOINT_FORCE));
+ }
break;
/*
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index ec65ab79fec..7757d576a64 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3125,6 +3125,13 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_procedures);
else if (Matches("CALL", MatchAny))
COMPLETE_WITH("(");
+/* CHECKPOINT */
+ else if (Matches("CHECKPOINT"))
+ COMPLETE_WITH("(");
+ else if (Matches("CHECKPOINT", "("))
+ COMPLETE_WITH("MODE", "FLUSH_ALL");
+ else if (Matches("CHECKPOINT", "(", "MODE"))
+ COMPLETE_WITH("IMMEDIATE", "FAST", "SPREAD");
/* CLOSE */
else if (Matches("CLOSE"))
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_cursors,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index dd00ab420b8..3553b49e53c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4015,6 +4015,7 @@ typedef struct RefreshMatViewStmt
typedef struct CheckPointStmt
{
NodeTag type;
+ List *options;
} CheckPointStmt;
/* ----------------------
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 776f1ad0e53..d8b4bfa666e 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -925,9 +925,9 @@ CREATE TEMP TABLE test_stats_temp AS SELECT 17;
DROP TABLE test_stats_temp;
-- Checkpoint twice: The checkpointer reports stats after reporting completion
-- of the checkpoint. But after a second checkpoint we'll see at least the
--- results of the first.
-CHECKPOINT;
-CHECKPOINT;
+-- results of the first. And while at it, test checkpoint options.
+CHECKPOINT (mode immediate);
+CHECKPOINT (mode spread, flush_all);
SELECT num_requested > :rqst_ckpts_before FROM pg_stat_checkpointer;
?column?
----------
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 232ab8db8fa..6141c6a69a9 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -438,9 +438,9 @@ DROP TABLE test_stats_temp;
-- Checkpoint twice: The checkpointer reports stats after reporting completion
-- of the checkpoint. But after a second checkpoint we'll see at least the
--- results of the first.
-CHECKPOINT;
-CHECKPOINT;
+-- results of the first. And while at it, test checkpoint options.
+CHECKPOINT (mode immediate);
+CHECKPOINT (mode spread, flush_all);
SELECT num_requested > :rqst_ckpts_before FROM pg_stat_checkpointer;
SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
--
2.47.2
