注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
文章快速说明索引
学习目标：
做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。
学习内容：（详见目录）
1、深入理解PostgreSQL数据库GUC参数 allow_alter_system 的使用和原理
学习时间：
2024年04月21日 14:44:16
学习产出：
1、PostgreSQL数据库基础知识回顾 1个
2、CSDN 技术博客 1篇
3、PostgreSQL数据库内核深入学习
注：下面我们所有的学习环境是Centos8+PostgreSQL master +Oracle19C+MySQL8.0
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)
postgres=#
#-----------------------------------------------------------------------------#
SQL> select * from v$version;
BANNER Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
BANNER_FULL Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
CON_ID 0
#-----------------------------------------------------------------------------#
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.06 sec)
mysql>
参数使用背景说明
背景
2024/03/29日，PostgreSQL新增一个GUC参数，如下：
官方文档的解释，如下：
使用
接下来看一个简单的使用，示例1如下：
[postgres@localhost:~/test/bin]$ ./psql
psql (17devel)
Type "help" for help.
postgres=# show timezone;
TimeZone
---------------------
America/Los_Angeles
(1 row)
postgres=# set timezone = 'PRC';
SET
postgres=# show timezone;
TimeZone
----------
PRC
(1 row)
postgres=# select now();
now
-------------------------------
2024-04-18 15:35:49.411729+08
(1 row)
postgres=# reset timezone;
RESET
postgres=# show timezone;
TimeZone
---------------------
America/Los_Angeles
(1 row)
postgres=# select now();
now
-------------------------------
2024-04-18 00:36:06.505543-07
(1 row)
postgres=# alter system set timezone = 'PRC';
2024-04-18 00:36:23.026 PDT [30697] ERROR: ALTER SYSTEM is not allowed in this environment
2024-04-18 00:36:23.026 PDT [30697] STATEMENT: alter system set timezone = 'PRC';
ERROR: ALTER SYSTEM is not allowed in this environment
postgres=#
postgres=# show timezone;
TimeZone
---------------------
America/Los_Angeles
(1 row)
postgres=# show allow_alter_system;
allow_alter_system
--------------------
off
(1 row)
postgres=# alter system set allow_alter_system = on;
2024-04-18 00:40:03.694 PDT [30697] ERROR: ALTER SYSTEM is not allowed in this environment
2024-04-18 00:40:03.694 PDT [30697] STATEMENT: alter system set allow_alter_system = on;
ERROR: ALTER SYSTEM is not allowed in this environment
postgres=#
[postgres@localhost:~/test/bin]$ cat test/postgresql.conf | grep allow_alter_system
allow_alter_system = off
[postgres@localhost:~/test/bin]$
示例2，如下：
[postgres@localhost:~/test/bin]$ cat test/postgresql.conf | grep allow_alter_system
allow_alter_system = off
[postgres@localhost:~/test/bin]$
[postgres@localhost:~/test/bin]$ vim test/postgresql.auto.conf
[postgres@localhost:~/test/bin]$
[postgres@localhost:~/test/bin]$ cat test/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
allow_alter_system = on
[postgres@localhost:~/test/bin]$
[postgres@localhost:~/test/bin]$ ./pg_ctl restart -D test/
waiting for server to shut down....2024-04-24 23:05:34.217 PDT [30683] LOG: received fast shutdown request
2024-04-24 23:05:34.219 PDT [30683] LOG: aborting any active transactions
2024-04-24 23:05:34.220 PDT [30683] LOG: background worker "logical replication launcher" (PID 30689) exited with exit code 1
2024-04-24 23:05:34.221 PDT [30684] LOG: shutting down
2024-04-24 23:05:34.240 PDT [30683] LOG: database system is shut down
done
server stopped
waiting for server to start....2024-04-24 23:05:34.361 PDT [50234] LOG: starting PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
2024-04-24 23:05:34.361 PDT [50234] LOG: listening on IPv6 address "::1", port 5432
2024-04-24 23:05:34.361 PDT [50234] LOG: listening on IPv4 address "127.0.0.1", port 5432
2024-04-24 23:05:34.363 PDT [50234] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-04-24 23:05:34.366 PDT [50237] LOG: database system was shut down at 2024-04-24 23:05:34 PDT
2024-04-24 23:05:34.370 PDT [50234] LOG: database system is ready to accept connections
done
server started
[postgres@localhost:~/test/bin]$ ./psql
psql (17devel)
Type "help" for help.
postgres=# show allow_alter_system;
allow_alter_system
--------------------
on
(1 row)
postgres=# show timezone;
TimeZone
---------------------
America/Los_Angeles
(1 row)
postgres=# alter system set timezone = 'PRC';
ALTER SYSTEM
postgres=# alter system set allow_alter_system = off;
2024-04-24 23:06:26.063 PDT [50248] ERROR: parameter "allow_alter_system" cannot be changed
2024-04-24 23:06:26.063 PDT [50248] STATEMENT: alter system set allow_alter_system = off;
ERROR: parameter "allow_alter_system" cannot be changed
postgres=#
参数实现源码解析
guc定义，如下：
// src\backend\utils\misc\guc_tables.c
{
/*
* This setting itself cannot be set by ALTER SYSTEM to avoid an
* operator turning this setting off by using ALTER SYSTEM, without a
* way to turn it back on.
*
* 此设置本身无法通过 ALTER SYSTEM 设置，以避免操作员使用 ALTER SYSTEM 关闭此设置
* 而无法将其重新打开
*/
{"allow_alter_system", PGC_SIGHUP, COMPAT_OPTIONS_OTHER,
gettext_noop("Allows running the ALTER SYSTEM command."),
gettext_noop("Can be set to off for environments where global configuration "
"changes should be made using a different method."),
GUC_DISALLOW_IN_AUTO_FILE
},
&AllowAlterSystem,
true,
NULL, NULL, NULL
},
该参数真实使用的逻辑，如下：
// src\backend\utils\misc\guc.c
/*
* Execute ALTER SYSTEM statement.
*
* Read the old PG_AUTOCONF_FILENAME file, merge in the new variable value,
* and write out an updated file. If the command is ALTER SYSTEM RESET ALL,
* we can skip reading the old file and just write an empty file.
* 读取旧的 PG_AUTOCONF_FILENAME 文件，合并新的变量值，并写出更新的文件
* 如果命令是 ALTER SYSTEM RESET ALL，我们可以跳过读取旧文件，只写入一个空文件
*
* An LWLock is used to serialize updates of the configuration file.
* LWLock 用于序列化配置文件的更新
*
* In case of an error, we leave the original automatic
* configuration file (PG_AUTOCONF_FILENAME) intact.
* 如果出现错误，我们会保留原始自动配置文件（PG_AUTOCONF_FILENAME）不变
*/
void
AlterSystemSetConfigFile(AlterSystemStmt *altersysstmt)
{
char *name;
char *value;
bool resetall = false;
ConfigVariable *head = NULL;
ConfigVariable *tail = NULL;
volatile int Tmpfd;
char AutoConfFileName[MAXPGPATH];
char AutoConfTmpFileName[MAXPGPATH];
/*
* Extract statement arguments
*/
name = altersysstmt->setstmt->name;
if (!AllowAlterSystem)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("ALTER SYSTEM is not allowed in this environment")));
switch (altersysstmt->setstmt->kind)
{
case VAR_SET_VALUE:
value = ExtractSetVariableArgs(altersysstmt->setstmt);
break;
case VAR_SET_DEFAULT:
case VAR_RESET:
value = NULL;
break;
case VAR_RESET_ALL:
value = NULL;
resetall = true;
break;
default:
elog(ERROR, "unrecognized alter system stmt type: %d",
altersysstmt->setstmt->kind);
break;
}
/*
* Check permission to run ALTER SYSTEM on the target variable
*/
if (!superuser())
{
if (resetall)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied to perform ALTER SYSTEM RESET ALL")));
else
{
AclResult aclresult;
aclresult = pg_parameter_aclcheck(name, GetUserId(),
ACL_ALTER_SYSTEM);
if (aclresult != ACLCHECK_OK)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied to set parameter \"%s\"",
name)));
}
}
/*
* Unless it's RESET_ALL, validate the target variable and value
*/
if (!resetall)
{
struct config_generic *record;
/* We don't want to create a placeholder if there's not one already */
record = find_option(name, false, true, DEBUG5);
if (record != NULL)
{
/*
* Don't allow parameters that can't be set in configuration files
* to be set in PG_AUTOCONF_FILENAME file.
*/
if ((record->context == PGC_INTERNAL) ||
(record->flags & GUC_DISALLOW_IN_FILE) ||
(record->flags & GUC_DISALLOW_IN_AUTO_FILE))
ereport(ERROR,
(errcode(ERRCODE_CANT_CHANGE_RUNTIME_PARAM),
errmsg("parameter \"%s\" cannot be changed",
name)));
/*
* If a value is specified, verify that it's sane.
*/
if (value)
{
union config_var_val newval;
void *newextra = NULL;
if (!parse_and_validate_value(record, name, value,
PGC_S_FILE, ERROR,
&newval, &newextra))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid value for parameter \"%s\": \"%s\"",
name, value)));
if (record->vartype == PGC_STRING && newval.stringval != NULL)
guc_free(newval.stringval);
guc_free(newextra);
}
}
else
{
/*
* Variable not known; check we'd be allowed to create it. (We
* cannot validate the value, but that's fine. A non-core GUC in
* the config file cannot cause postmaster start to fail, so we
* don't have to be too tense about possibly installing a bad
* value.)
*/
(void) assignable_custom_variable_name(name, false, ERROR);
}
/*
* We must also reject values containing newlines, because the grammar
* for config files doesn't support embedded newlines in string
* literals.
*/
if (value && strchr(value, '\n'))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("parameter value for ALTER SYSTEM must not contain a newline")));
}
/*
* PG_AUTOCONF_FILENAME and its corresponding temporary file are always in
* the data directory, so we can reference them by simple relative paths.
*/
snprintf(AutoConfFileName, sizeof(AutoConfFileName), "%s",
PG_AUTOCONF_FILENAME);
snprintf(AutoConfTmpFileName, sizeof(AutoConfTmpFileName), "%s.%s",
AutoConfFileName,
"tmp");
/*
* Only one backend is allowed to operate on PG_AUTOCONF_FILENAME at a
* time. Use AutoFileLock to ensure that. We must hold the lock while
* reading the old file contents.
*/
LWLockAcquire(AutoFileLock, LW_EXCLUSIVE);
/*
* If we're going to reset everything, then no need to open or parse the
* old file. We'll just write out an empty list.
*/
if (!resetall)
{
struct stat st;
if (stat(AutoConfFileName, &st) == 0)
{
/* open old file PG_AUTOCONF_FILENAME */
FILE *infile;
infile = AllocateFile(AutoConfFileName, "r");
if (infile == NULL)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m",
AutoConfFileName)));
/* parse it */
if (!ParseConfigFp(infile, AutoConfFileName, CONF_FILE_START_DEPTH,
LOG, &head, &tail))
ereport(ERROR,
(errcode(ERRCODE_CONFIG_FILE_ERROR),
errmsg("could not parse contents of file \"%s\"",
AutoConfFileName)));
FreeFile(infile);
}
/*
* Now, replace any existing entry with the new value, or add it if
* not present.
*/
replace_auto_config_value(&head, &tail, name, value);
}
/*
* Invoke the post-alter hook for setting this GUC variable. GUCs
* typically do not have corresponding entries in pg_parameter_acl, so we
* call the hook using the name rather than a potentially-non-existent
* OID. Nonetheless, we pass ParameterAclRelationId so that this call
* context can be distinguished from others. (Note that "name" will be
* NULL in the RESET ALL case.)
*
* We do this here rather than at the end, because ALTER SYSTEM is not
* transactional. If the hook aborts our transaction, it will be cleaner
* to do so before we touch any files.
*/
InvokeObjectPostAlterHookArgStr(ParameterAclRelationId, name,
ACL_ALTER_SYSTEM,
altersysstmt->setstmt->kind,
false);
/*
* To ensure crash safety, first write the new file data to a temp file,
* then atomically rename it into place.
*
* If there is a temp file left over due to a previous crash, it's okay to
* truncate and reuse it.
*/
Tmpfd = BasicOpenFile(AutoConfTmpFileName,
O_CREAT | O_RDWR | O_TRUNC);
if (Tmpfd < 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m",
AutoConfTmpFileName)));
/*
* Use a TRY block to clean up the file if we fail. Since we need a TRY
* block anyway, OK to use BasicOpenFile rather than OpenTransientFile.
*/
PG_TRY();
{
/* Write and sync the new contents to the temporary file */
write_auto_conf_file(Tmpfd, AutoConfTmpFileName, head);
/* Close before renaming; may be required on some platforms */
close(Tmpfd);
Tmpfd = -1;
/*
* As the rename is atomic operation, if any problem occurs after this
* at worst it can lose the parameters set by last ALTER SYSTEM
* command.
*/
durable_rename(AutoConfTmpFileName, AutoConfFileName, ERROR);
}
PG_CATCH();
{
/* Close file first, else unlink might fail on some platforms */
if (Tmpfd >= 0)
close(Tmpfd);
/* Unlink, but ignore any error */
(void) unlink(AutoConfTmpFileName);
PG_RE_THROW();
}
PG_END_TRY();
FreeConfigVariables(head);
LWLockRelease(AutoFileLock);
}