Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uNTvJ-002w1p-Nq for pgsql-hackers@arkaria.postgresql.org; Fri, 06 Jun 2025 10:03:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uNTvH-007OVq-RE for pgsql-hackers@arkaria.postgresql.org; Fri, 06 Jun 2025 10:03:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uNTvH-007ORM-Dw for pgsql-hackers@lists.postgresql.org; Fri, 06 Jun 2025 10:03:40 +0000 Received: from mout-p-101.mailbox.org ([80.241.56.151]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uNTvF-000YcY-1h for pgsql-hackers@lists.postgresql.org; Fri, 06 Jun 2025 10:03:39 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [10.196.197.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-p-101.mailbox.org (Postfix) with ESMTPS id 4bDH2T4p3Rz9t5w; Fri, 6 Jun 2025 12:03:29 +0200 (CEST) Date: Fri, 6 Jun 2025 12:03:28 +0200 From: Christoph Berg To: Andres Freund Cc: Nathan Bossart , PostgreSQL Hackers Subject: Re: CHECKPOINT unlogged data Message-ID: References: <7x6gsmev36zzh6lfzydkddbhpnxqgu2k2l2ci2k6foxhrstvj2@bn55g54wjrl4> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="oBvuCbiiE69d8VIG" Content-Disposition: inline In-Reply-To: <7x6gsmev36zzh6lfzydkddbhpnxqgu2k2l2ci2k6foxhrstvj2@bn55g54wjrl4> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --oBvuCbiiE69d8VIG Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Re: Andres Freund > I'd add a 'mode' that can be set to an arbitrary string, which then can be > validated in C code. That seems more future proof. Changed in the attached v2, thanks. Christoph --oBvuCbiiE69d8VIG Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0001-Add-mode-and-flush_all-options-to-checkpoint.patch" From c8975cbd1dbe5e5cae18414ea211117bc3f2c0e8 Mon Sep 17 00:00:00 2001 From: Christoph Berg Date: Fri, 30 May 2025 17:58:35 +0200 Subject: [PATCH v2] 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 | 38 ++++++++++++++- 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, 124 insertions(+), 18 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 -CHECKPOINT +CHECKPOINT [ ( option [, ...] ) ] + +where option can be one of: + + MODE { IMMEDIATE | FAST | SPREAD } + FLUSH_ALL [ boolean ] @@ -31,20 +36,11 @@ 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 + log. All data files will be flushed to disk, except for relations marked UNLOGGED. Refer to for more details about what happens during a checkpoint. - - The CHECKPOINT 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 - ). - CHECKPOINT is not intended for use during normal - operation. - - If executed during recovery, the CHECKPOINT command will force a restartpoint (see ) @@ -56,6 +52,64 @@ CHECKPOINT the role can call CHECKPOINT. + + + 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. + + + + + Parameters + + + + MODE + + + The CHECKPOINT command forces an immediate + checkpoint by default when the command is issued, without waiting for a + regular checkpoint scheduled by the system. FAST is a + synonym for IMMEDIATE. + + + A SPREAD checkpoint will instead spread out the write load + as determined by the + setting, like the system-scheduled checkpoints. + + + + + + FLUSH_ALL + + + Requests the checkpoint to also flush data of UNLOGGED + relations. Defaults to 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. + + + + 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..3a08c54b46e 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -943,6 +943,11 @@ standard_ProcessUtility(PlannedStmt *pstmt, break; case T_CheckPointStmt: + 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), @@ -952,7 +957,38 @@ standard_ProcessUtility(PlannedStmt *pstmt, errdetail("Only roles with privileges of the \"%s\" role may execute this command.", "pg_checkpoint"))); - RequestCheckpoint(CHECKPOINT_IMMEDIATE | CHECKPOINT_WAIT | + /* 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 --oBvuCbiiE69d8VIG--