PostgreSQL数据库内核（一）：增加系统表pg_test_catalog

精选原创

Ace@yzg 2024-08-13 10:07:20 ©著作权

文章标签 postgresql 数据库 sql CL PostgreSQL 文章分类 Html/CSS 前端开发

©著作权归作者所有：来自51CTO博客作者Ace@yzg的原创作品，请联系作者获取转载授权，否则将追究法律责任

编译环境准备

gdb调试

CLion配置

增加系统表pg_test_catalog

编译环境准备

使用PostgreSQL14.5源码版本编译，操作系统CentOS，本地windos系统+CLion代码工具，首先下载pg源码，上传CentOS系统：

more /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"

下载必要的系统环境依赖包：

yum install -y openssl-devel readline-devel ]
yum install -y zlib-devel bison flex 
yum install -y libxml2-devel libxslt-devel 
yum install -y perl gdb gdbsever

configure校验源码（debug模式），make编译后安装到系统，创建postgres用户/组后把编译文件目录都赋权给postgres，通常数据库跑在普通用户下的：

# 清理make
make clean
# 检查环境,生成debug模式的makefile,-O0的目的是允许深度调试
CFLOAGS= -O0 /home/yzg/postgresql-14.5/configure --prefix=/usr/local/pgsql --enable-debug
# 构建编译
make
# 可选项,跑源码自带的测试例
make check
# 编译安装系统上
make install
# 新建postgres用户和组
groupadd postgres
useradd -r -g postgres -d /home/postgres -s /bin/bash -m postgres
# copy编译目录到home，赋权
cp -R /usr/local/pgsql /home/postgres/
chown -R postgres:postgres /home/postgres/pgsql
# 切换用户
su - postgres
cd ~/pgsql/bin
# 初始化postgresql并启动
./initdb -D ~/data
# 初始化成功
Success. You can now start the database server using:

# 启动
./pg_ctl start -D /home/postgres/data  

waiting for server to start.... done
server started
[postgres@iZ8vb5e8o9vgrwc6estwgjZ bin]$ ps -ef |grep postgres
root     21038  8375  0 10:23 pts/0    00:00:00 su - postgres
postgres 21039 21038  0 10:23 pts/0    00:00:00 -bash
postgres 21192     1  0 10:25 ?        00:00:00 /home/postgres/pgsql/bin/postgres -D /home/postgres/data
postgres 21194 21192  0 10:25 ?        00:00:00 postgres: checkpointer
postgres 21195 21192  0 10:25 ?        00:00:00 postgres: background writer
postgres 21196 21192  0 10:25 ?        00:00:00 postgres: walwriter
postgres 21197 21192  0 10:25 ?        00:00:00 postgres: autovacuum launcher
postgres 21198 21192  0 10:25 ?        00:00:00 postgres: stats collector
postgres 21199 21192  0 10:25 ?        00:00:00 postgres: logical replication launcher
postgres 21209 21039  0 10:25 pts/0    00:00:00 ps -ef
postgres 21210 21039  0 10:25 pts/0    00:00:00 grep --color=auto postgres

gdb调试

psql测试并打断点进行源码调试，这里以select 1的查询路径为例，其调用函数exec_simple_query在postgres.c文件的955行：

# 增加bin目录环境变量,不然psql需要全路径
export PATH=/home/postgres/pgsql/bin/:$PATH
# psql测试
whereis psql
psql: /home/postgres/pgsql/bin/psql

psql
psql (14.5)
Type "help" for help.

postgres=# select 1;
 ?column?
----------
        1
(1 row)

# gdb调试指定psql的pid和源码目录
gdb -p 13326 -d /home/yzg/postgresql-14.5/

# gdb打断点到xact.c的988行
(gdb) b xact.c:899

可以到代码级别的调试：

PostgreSQL数据库内核（一）：增加系统表pg_test_catalog_数据库

CLion配置

CLion是JetBrains 的类似于idea的工具，其界面/快捷键与idea一致，但其使用cmake作为项目管理和扫描默认工具，pg源码用configure+makefile实现编译的，好比maven和gradle的区别，这里我们本地只保证其能够代码转跳并且能够提示代码规范就行，创建CMakeList.txt文件让CLion能够识别代码结构：

cmake_minimum_required(VERSION 3.10)
project(PostgreSQL VERSION 14.5)

# Set the build type (Debug or Release)
set(CMAKE_C_STANDARD  99)

# Add the PostgreSQL source directories
set(SOURCE_DIR "${CMAKE_CURRENT_SOURCE_DIR}/src")
set(INCLUDE_DIRS "${SOURCE_DIR}/include")

# Specify the PostgreSQL libraries and headers location
set(LIBRARY_DIRS "${SOURCE_DIR}/backend")

# Include directories for the compiler to search for header files
include_directories(${CMAKE_CURRENT_SOURCE_DIR}/src/include)
include_directories(${CMAKE_CURRENT_SOURCE_DIR}/src/include/*)
include_directories(${CMAKE_CURRENT_SOURCE_DIR}/src/include/*/*)
file(GLOB_RECURSE POSTGRESQL_SRC_FILES
        src/*/*.c
        src/*/*.h
        src/*.c
        src/*.h
        src/*/*.c
        contrib/*/*.c)
# Set the compiler flags if necessary
set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -Wall -Wextra")
set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -Wall -Wextra")


# Add subdirectories to build
#[[add_subdirectory("${SOURCE_DIR}/backend")
add_subdirectory("${SOURCE_DIR}/interfaces/libpq")
add_subdirectory("${SOURCE_DIR}/contrib")]]

# Add any custom targets here
add_executable(postgres_server ${POSTGRESQL_SRC_FILES} )
target_link_libraries(postgres_server ${LIBRARIES})

# You may need to add more subdirectories and targets based on your needs.

增加系统表pg_test_catalog

配置好编译调试环境以及代码阅读环境后开始增加系统表，首先查看pg_database这个系统表有哪些信息可以借鉴的，参考源码看：pg_database有若干字段，其在代码中位置主要是：src/backend/catalog和src/include/catalog目录，两者区别如下：

src/include/catalog：这个目录存放所有系统表的头文件
src/backend/catalog：这个目录存放处理系统表的后端实现代码，包括用于操作系统表的函数，例如创建、读取、更新和删除，这些函数通常会在执行 SQL 语句时被调用，以便与系统目录进行交互。

在src/include/catalog目录下有这2个文件：

PostgreSQL数据库内核（一）：增加系统表pg_test_catalog_PostgreSQL_02

仿照创建pg_test_catalog文件：

PostgreSQL数据库内核（一）：增加系统表pg_test_catalog_CL_03

pg_test_catalog.dat文件用于插入初始化数据：

#----------------------------------------------------------------------
#
# pg_database.dat
#    Initial contents of the pg_database system catalog.
#
# Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
# Portions Copyright (c) 1994, Regents of the University of California
#
# src/include/catalog/pg_test_catalog.dat
#
#----------------------------------------------------------------------

[

{ oid => '6668', oid_symbol => 'TEST_CATALOG_OID_S',
  descr => 'test catalog yzg',
  name => 'yzg', sex => 'boy'},

]

pg_test_catalog.h是头文件，全局可以使用：

/*-------------------------------------------------------------------------
 *
 * pg_test_catalog.h
 *	  definition of the "database" system catalog (pg_test_catalog)
 *
 *
 * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
 * Portions Copyright (c) 1994, Regents of the University of California
 *
 * src/include/catalog/pg_test_catalog.h
 *
 * NOTES
 *	  The Catalog.pm module reads this file and derives schema
 *	  information.
 *
 *-------------------------------------------------------------------------
 */
#ifndef PG_TEST_CATALOG_H
#define PG_TEST_CATALOG_H

#include "catalog/genbki.h"
#include "catalog/pg_test_catalog_d.h"

/* ----------------
 *		pg_test_catalog definition.  cpp turns this into
 *		typedef struct FormData_pg_test_catalog
 * ----------------
 */
CATALOG(pg_test_catalog,6666,TestCatalogRelationId)
{
	Oid			oid;
	char        name;
	char	    sex;
} FormData_pg_test_catalog;


typedef FormData_pg_test_catalog *Form_pg_test_catalog;

DECLARE_UNIQUE_INDEX_PKEY(pg_test_catalog_oid_index, 6667, on pg_test_catalog using btree(oid oid_ops));
#define TestCatalogOidIndexId	6667

#endif							/* pg_test_catalog_H */

修改src/backend/catalog/Makefile，添加刚才新增的文件：

PostgreSQL数据库内核（一）：增加系统表pg_test_catalog_sql_04

重新编译后会发现pg_test_catalog系统表在编译目录下的share/postgres.bki文件中，这个文件是模板文件在inidb时候生成别的库：

# 编译后postgres.bki模板文件有对应的pg_test_catalog表
find / -name postgres.bki
/usr/local/pgsql/share/postgres.bki
# postgres.bki模板文件中表的定义方式
vi /usr/local/pgsql/share/postgres.bki
create pg_test_catalog 6666
 (
 oid = oid ,
 name = char ,
 sex = char
 )
open pg_test_catalog
insert ( 6668 yzg boy )
close pg_test_catalog

initdb后启动数据库发现postgres库中有这个系统表，postgres库本身就是从这个模板文件生成的：

postgres=# select * from pg_test_catalog;
 oid  | name | sex
------+------+-----
 6668 | y    | b
(1 row)

postgres=# \d pg_test_catalog;
        Table "pg_catalog.pg_test_catalog"
 Column |  Type  | Collation | Nullable | Default
--------+--------+-----------+----------+---------
 oid    | oid    |           | not null |
 name   | "char" |           | not null |
 sex    | "char" |           | not null |
Indexes:
    "pg_test_catalog_oid_index" PRIMARY KEY, btree (oid)

以上就完成了增加系统表流程，这里系统表并没有一些逻辑交互，如果涉及复杂的逻辑交互需要修改backend里面的代码或者调用；