PostgreSQL的学习心得和知识总结（一百四十）|深入理解PostgreSQL数据库 psql工具 \set 变量内部及HOOK机制

目录结构

注：提前言明本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、参考书籍：《PostgreSQL数据库内核分析》
2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》
3、PostgreSQL数据库仓库链接，点击前往
4、日本著名PostgreSQL数据库专家铃木启修网站主页，点击前往
5、参考书籍：《PostgreSQL中文手册》
6、参考书籍：《PostgreSQL指南：内幕探索》，点击前往
7、参考书籍：《事务处理概念与技术》

1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享抛砖引玉一起学习
3、本文不提供任何资源不存在任何交易与任何组织和机构无关
4、大家可以根据需要自行复制粘贴以及作为其他个人用途，但是不允许转载不允许商用（写作不易，还请见谅 💖）
5、本文内容基于PostgreSQL master源码开发而成

深入理解PostgreSQL数据库 psql工具 \set 变量内部及HOOK机制

文章快速说明索引
功能使用背景说明
- 背景
功能使用源码解析
- ECHO_HIDDEN
- HIDE_TABLEAM
- HOOK 机制
- 设置新的变量

文章快速说明索引

学习目标：

做数据库内核开发久了就会有一种少年得志，年少轻狂的错觉，然鹅细细一品觉得自己其实不算特别优秀远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间将着重于学习分享Postgres的基础知识和实践内幕。

学习内容：（详见目录）

1、PostgreSQL数据库 psql工具 \set 变量内部及HOOK机制

学习时间：

2024年04月29日 21:24:00

学习产出：

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>

功能使用背景说明

背景

首先看一下示例1(ECHO_HIDDEN)，如下：

[postgres@localhost:~/test/bin]$ ./psql 
psql (17devel)
Type "help" for help.

postgres=# \echo :ECHO_HIDDEN
off
postgres=# \set ECHO_HIDDEN on
postgres=# 
postgres=# \d
/******** QUERY *********/
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
/************************/

        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

postgres=# \d+
/******** QUERY *********/
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
  am.amname as "Access method",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
/************************/

                                     List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |    Size    | Description 
--------+------+-------+----------+-------------+---------------+------------+-------------
 public | t1   | table | postgres | permanent   | heap          | 8192 bytes | 
(1 row)

postgres=#

同样是这个会话，接着执行示例2(HIDE_TABLEAM)，如下：

postgres=# \echo :HIDE_TABLEAM
off
postgres=# \set HIDE_TABLEAM on
postgres=# 
postgres=# \d+
/******** QUERY *********/
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
/************************/

                             List of relations
 Schema | Name | Type  |  Owner   | Persistence |    Size    | Description 
--------+------+-------+----------+-------------+------------+-------------
 public | t1   | table | postgres | permanent   | 8192 bytes | 
(1 row)

postgres=#

以及示例3，如下：

[postgres@localhost:~/test/bin]$ ./psql 
psql (17devel)
Type "help" for help.

postgres=# \echo :me
:me
postgres=# 
postgres=# \set me songbaobao
postgres=# 
postgres=# \echo :me
songbaobao
postgres=#

功能使用源码解析

ECHO_HIDDEN

首先，我们先看一下示例1的内部实现，通常下元命令执行，如下：

[postgres@localhost:~/test/bin]$ ./psql 
psql (17devel)
Type "help" for help.

postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

postgres=#

其内部解析之后，就是如下执行：

[postgres@localhost:~/test/bin]$ ./psql 
psql (17devel)
Type "help" for help.

postgres=# SELECT n.nspname as "Schema",
postgres-#   c.relname as "Name",
postgres-#   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
postgres-#   pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
postgres-# FROM pg_catalog.pg_class c
postgres-#      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
postgres-#      LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
postgres-# WHERE c.relkind IN ('r','p','v','m','S','f','')
postgres-#       AND n.nspname <> 'pg_catalog'
postgres-#       AND n.nspname !~ '^pg_toast'
postgres-#       AND n.nspname <> 'information_schema'
postgres-#   AND pg_catalog.pg_table_is_visible(c.oid)
postgres-# ORDER BY 1,2;
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

postgres=#

而内部打印的逻辑，如下：

在这里插入图片描述

HIDE_TABLEAM

该参数默认情况下是关闭的，如下：

[postgres@localhost:~/test/bin]$ ./psql 
psql (17devel)
Type "help" for help.

postgres=# \echo :HIDE_TABLEAM
off
postgres=# \d+
                                     List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |    Size    | Description 
--------+------+-------+----------+-------------+---------------+------------+-------------
 public | t1   | table | postgres | permanent   | heap          | 8192 bytes | 
(1 row)

postgres=#

在上面示例2中可以发现，HIDE_TABLEAM = on 的情况下，将不再查询输出Access method，如下：

在这里插入图片描述

两者的差异，如下：

在这里插入图片描述

HOOK 机制

元命令\set变量内部也有一套自己的HOOK机制，如下：

// src/bin/psql/variables.h

/*
 * Data structure representing one variable.
 * 代表一个变量的数据结构
 *
 * Note: if value == NULL then the variable is logically unset, but we are
 * keeping the struct around so as not to forget about its hook function(s).
 * 注意：如果 value == NULL 则该变量在逻辑上未设置，但我们保留该结构以免忘记其钩子函数
 */
struct _variable
{
	char	   *name;
	char	   *value;
	VariableSubstituteHook substitute_hook;
	VariableAssignHook assign_hook;
	struct _variable *next;
};

下面分别看一下这两个Hook，如下：

/*
 * Variables can be given "assign hook" functions.  The assign hook can
 * prevent invalid values from being assigned, and can update internal C
 * variables to keep them in sync with the variable's current value.
 * 变量可以被赋予“分配钩子”函数
 * 分配挂钩可以防止分配无效值，并且可以更新内部 C 变量以使其与变量的当前值保持同步
 *
 * An assign hook function is called before any attempted assignment, with the
 * proposed new value of the variable (or with NULL, if an \unset is being
 * attempted).  If it returns false, the assignment doesn't occur --- it
 * should print an error message with pg_log_error() to tell the user why.
 * 在任何尝试分配之前都会调用分配钩子函数，并使用建议的变量新值（或者如果正在尝试 \unset，则使用 NULL）
 * 如果它返回 false，则分配不会发生 --- 它应该使用 pg_log_error() 打印一条错误消息来告诉用户原因
 *
 * When an assign hook function is installed with SetVariableHooks(), it is
 * called with the variable's current value (or with NULL, if it wasn't set
 * yet).  But its return value is ignored in this case.  The hook should be
 * set before any possibly-invalid value can be assigned.
 * 当使用 SetVariableHooks() 安装分配挂钩函数时，将使用变量的当前值（如果尚未设置，则使用 NULL）调用它
 * 但在这种情况下它的返回值被忽略
 * 应在分配任何可能无效的值之前设置挂钩
 */
typedef bool (*VariableAssignHook) (const char *newval);

/*
 * Variables can also be given "substitute hook" functions.  The substitute
 * hook can replace values (including NULL) with other values, allowing
 * normalization of variable contents.  For example, for a boolean variable,
 * we wish to interpret "\unset FOO" as "\set FOO off", and we can do that
 * by installing a substitute hook.  (We can use the same substitute hook
 * for all bool or nearly-bool variables, which is why this responsibility
 * isn't part of the assign hook.)
 * 变量也可以被赋予“替代钩子”函数
 * 替代钩子可以用其他值替换值（包括 NULL），从而允许变量内容规范化
 * 例如，对于布尔变量，我们希望将“\unset FOO”解释为“\set FOO off”，我们可以通过安装替代钩子来做到这一点
 * （我们可以对所有布尔或近布尔变量使用相同的替代钩子，这就是为什么此责任不是分配钩子的一部分）
 *
 * The substitute hook is called before any attempted assignment, and before
 * the assign hook if any, passing the proposed new value of the variable as a
 * malloc'd string (or NULL, if an \unset is being attempted).  It can return
 * the same value, or a different malloc'd string, or modify the string
 * in-place.  It should free the passed-in value if it's not returning it.
 * The substitute hook generally should not complain about erroneous values;
 * that's a job for the assign hook.
 * 在任何尝试分配之前以及分配钩子（如果有）之前调用替代钩子，将变量的建议新值作为 malloc 字符串传递（或 NULL，如果正在尝试 \unset）
 * 它可以返回相同的值，或不同的 malloc 字符串，或就地修改字符串
 * 如果它不返回传入的值，它应该释放它
 * 替代钩子通常不应该抱怨错误的值；这是分配钩子的工作
 *
 * When a substitute hook is installed with SetVariableHooks(), it is applied
 * to the variable's current value (typically NULL, if it wasn't set yet).
 * That also happens before applying the assign hook.
 * 当使用 SetVariableHooks() 安装替代钩子时，它将应用于变量的当前值（通常为 NULL，如果尚未设置）
 * 这也会发生在应用分配挂钩之前
 */
typedef char *(*VariableSubstituteHook) (char *newval);

下面我们来看一个简单的例子，如下：

// src/bin/psql/startup.c

static void
EstablishVariableSpace(void)
{
	pset.vars = CreateVariableSpace();

	SetVariableHooks(pset.vars, "AUTOCOMMIT",
					 bool_substitute_hook,
					 autocommit_hook);
	...
	SetVariableHooks(pset.vars, "HIDE_TABLEAM",
					 bool_substitute_hook,
					 hide_tableam_hook);
}

/*
 * Substitute hooks and assign hooks for psql variables.
 * 用钩子替换 psql 变量并为 psql 变量分配钩子
 *
 * This isn't an amazingly good place for them, but neither is anywhere else.
 * 这对他们来说不是一个令人惊奇的好地方，但其他地方也不是
 *
 * By policy, every special variable that controls any psql behavior should
 * have one or both hooks, even if they're just no-ops.  This ensures that
 * the variable will remain present in variables.c's list even when unset,
 * which ensures that it's known to tab completion.
 * 根据策略，控制任何 psql 行为的每个特殊变量都应该有一个或两个钩子，即使它们只是无操作
 * 这确保了即使未设置，变量也将保留在 Variables.c 的列表中，从而确保知道它可以完成 Tab 键补全
 */

static char *
bool_substitute_hook(char *newval)
{
	if (newval == NULL)
	{
		/* "\unset FOO" becomes "\set FOO off" */
		newval = pg_strdup("off");
	}
	else if (newval[0] == '\0')
	{
		/* "\set FOO" becomes "\set FOO on" */
		pg_free(newval);
		newval = pg_strdup("on");
	}
	return newval;
}

static bool
autocommit_hook(const char *newval)
{
	return ParseVariableBool(newval, "AUTOCOMMIT", &pset.autocommit);
}

...

static bool
hide_tableam_hook(const char *newval)
{
	return ParseVariableBool(newval, "HIDE_TABLEAM", &pset.hide_tableam);
}

在这里插入图片描述

设置新的变量

在这里插入图片描述

此时的函数堆栈，如下：

SetVariable(VariableSpace space, const char * name, const char * value) (\home\postgres\postgres\src\bin\psql\variables.c:289)
exec_command_set(PsqlScanState scan_state, _Bool active_branch) (\home\postgres\postgres\src\bin\psql\command.c:2406)
exec_command(const char * cmd, PsqlScanState scan_state, ConditionalStack cstack, PQExpBuffer query_buf, PQExpBuffer previous_buf) (\home\postgres\postgres\src\bin\psql\command.c:395)
HandleSlashCmds(PsqlScanState scan_state, ConditionalStack cstack, PQExpBuffer query_buf, PQExpBuffer previous_buf) (\home\postgres\postgres\src\bin\psql\command.c:231)
MainLoop(FILE * source) (\home\postgres\postgres\src\bin\psql\mainloop.c:496)
main(int argc, char ** argv) (\home\postgres\postgres\src\bin\psql\startup.c:462)