From 71e4253f2c383d92639fdbeb6983c9343825e8c6 Mon Sep 17 00:00:00 2001 From: Christoph Berg Date: Fri, 30 May 2025 17:58:35 +0200 Subject: [PATCH v4 2/2] Add mode and flush_unlogged 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 FAST and SPREAD. --- doc/src/sgml/ref/checkpoint.sgml | 81 +++++++++++++++++++++++++---- 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, 141 insertions(+), 28 deletions(-) diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml index 10a433e4757..66277a97f36 100644 --- a/doc/src/sgml/ref/checkpoint.sgml +++ b/doc/src/sgml/ref/checkpoint.sgml @@ -21,7 +21,12 @@ PostgreSQL documentation -CHECKPOINT +CHECKPOINT [ ( option [, ...] ) ] + +where option can be one of: + + MODE { FAST | SPREAD } + FLUSH_UNLOGGED [ boolean ] @@ -31,24 +36,30 @@ CHECKPOINT 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 - for more details about what happens + log. Should the system crash, recovery will start at the last checkpoint. + Refer to for more details about what happens during a checkpoint. - The CHECKPOINT command forces a fast - checkpoint when the command is issued, without waiting for a - regular checkpoint scheduled by the system (controlled by the settings in - ). - CHECKPOINT is not intended for use during normal - operation. + Running CHECKPOINT is not required during normal + operation; the system schedules checkpoints automatically (controlled by + the settings in ). + CHECKPOINT can be beneficial to run before operations + such as binary backups or shutting down PostgreSQL + as this can reduce the amount of data to be flushed for the next checkpoint + within these operations. Specifically, UNLOGGED table + data is otherwise only flushed by the shutdown checkpoint. If executed during recovery, the CHECKPOINT command - will force a restartpoint (see ) - rather than writing a new checkpoint. + will force a restartpoint rather than writing a new checkpoint. (The + operation will be a no-op if there is no corresponding checkpoint in the + write-ahead log.) If a checkpoint is already being written when a + CHECKPOINT is issued, the running checkpoint is upgraded + with this command's MODE and + FLUSH_UNLOGGED options. @@ -58,6 +69,54 @@ CHECKPOINT + + Parameters + + + + MODE + + + The CHECKPOINT command forces a fast checkpoint by + default when the command is issued, without waiting for a regular + checkpoint scheduled by the system. + + + A SPREAD checkpoint will instead spread out the write + load as determined by the + setting, like the + system-scheduled checkpoints. + + + + + + FLUSH_UNLOGGED + + + All data files except for those marked as UNLOGGED + are flushed to disk during a checkpoint. Enabling this option will also + flush UNLOGGED relations; the default is off. + + + + + + boolean + + + Specifies whether the selected option should be turned on or off. + You can write TRUE, ON, or + 1 to enable the option, and FALSE, + OFF, or 0 to disable it. The + boolean value can also + be omitted, in which case TRUE is assumed. + + + + + + Compatibility 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 cda86ad44b0..73184d6bf88 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_FAST | CHECKPOINT_WAIT | - (RecoveryInProgress() ? 0 : CHECKPOINT_FORCE)); + { + CheckPointStmt *stmt = (CheckPointStmt *) parsetree; + ListCell *lc; + bool fast = true; + bool flush_unlogged = 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, "fast") == 0) + fast = true; + else if (strcmp(mode, "spread") == 0) + fast = false; + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("CHECKPOINT option \"%s\" argument \"%s\" is invalid", opt->defname, mode), + errhint("valid arguments are \"FAST\" and \"SPREAD\""), + parser_errposition(pstate, opt->location))); + } + else if (strcmp(opt->defname, "flush_unlogged") == 0) + flush_unlogged = defGetBoolean(opt); + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("unrecognized CHECKPOINT option \"%s\"", opt->defname), + errhint("valid options are \"MODE\" and \"FLUSH_UNLOGGED\""), + parser_errposition(pstate, opt->location))); + } + + RequestCheckpoint(CHECKPOINT_WAIT | + (fast ? CHECKPOINT_FAST : 0) | + (flush_unlogged ? CHECKPOINT_FLUSH_UNLOGGED : 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 620830feb9d..eea09b62615 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_UNLOGGED"); + else if (Matches("CHECKPOINT", "(", "MODE")) + COMPLETE_WITH("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..e1203b78ff5 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 fast); +CHECKPOINT (mode spread, flush_unlogged); 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..79c0e51c19c 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 fast); +CHECKPOINT (mode spread, flush_unlogged); SELECT num_requested > :rqst_ckpts_before FROM pg_stat_checkpointer; SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal; -- 2.47.2