PostgreSQL

1 概述 1.1 总接口 main PostmasterMain ServerLoop for (;;) select # 接收TCP连接 BackendStartup fork_process # fork postgres进程，用于处理SQL # postgres子进程执行： BackendRun # postgres子进程 BackendRun PostgresMain for (;;) ReadCommand exec_simple_query pg_parse_query # 语法解析 pg_analyze_and_rewrite # 语义分析 查询重写 pg_plan_queries # 计划生成 CreatePortal PortalDefineQuery PortalStart PortalRun PortalRunSelect ExecutorRun # 计划执行 standard_ExecutorRun ExecutePlan for (;;) ExecProcNode ExecSeqScan # SELECT 计划执行 SeqNext heap_beginscan heap_getnext PortalRunMulti # DDL执行 PortalRunUtility ProcessUtility standard_ProcessUtility ExecDropStmt # DROP TABLE ProcessUtilitySlow DefineRelation # CREATE TABLE ProcessQuery ExecutorStart ExecutorRun # 计划执行 standard_ExecutorRun ExecutePlan for (;;) ExecProcNode ExecModifyTable ExecInsert # INSERT 计划执行 heap_insert ExecUpdate # UPDATE 计划执行 heap_update ExecDelete # DELETE 计划执行 heap_delete ExecutorFinish ExecutorEnd PortalDrop exec_simple_query pg_plan_queries pg_plan_query planner standard_planner subquery_planner pull_up_subqueries preprocess_qual_conditions grouping_planner query_planner make_one_rel set_base_rel_pathlists # 查找所有Scan类型 set_rel_pathlist set_plain_rel_pathlist create_seqscan_path create_index_paths get_index_paths generate_bitmap_or_paths create_tidscan_paths get_cheapest_fractional_path create_plan create_plan_recurse create_scan_plan 1.2 算子类型 TupleTableSlot *ExecProcNode(PlanState *node) /* 控制算子 */ ExecResult((ResultState *) node); ExecModifyTable((ModifyTableState *) node); ExecAppend((AppendState *) node); ExecMergeAppend((MergeAppendState *) node); ExecRecursiveUnion((RecursiveUnionState *) node); /* 扫描算子 */ ExecSeqScan((SeqScanState *) node); ExecSampleScan((SampleScanState *) node); ExecIndexScan((IndexScanState *) node); ExecIndexOnlyScan((IndexOnlyScanState *) node); ExecBitmapHeapScan((BitmapHeapScanState *) node); ExecTidScan((TidScanState *) node); ExecSubqueryScan((SubqueryScanState *) node); ExecFunctionScan((FunctionScanState *) node); ExecValuesScan((ValuesScanState *) node); ExecCteScan((CteScanState *) node); ExecWorkTableScan((WorkTableScanState *) node); ExecForeignScan((ForeignScanState *) node); ExecCustomScan((CustomScanState *) node); /* 连接算子 */ ExecNestLoop((NestLoopState *) node); ExecMergeJoin((MergeJoinState *) node); ExecHashJoin((HashJoinState *) node); /* 物化算子 */ ExecMaterial((MaterialState *) node); ExecSort((SortState *) node); ExecGroup((GroupState *) node) ExecAgg((AggState *) node); ExecWindowAgg((WindowAggState *) node) ExecUnique((UniqueState *) node); ExecHash((HashState *) node); ExecSetOp((SetOpState *) node) ExecLockRows((LockRowsState *) node); ExecLimit((LimitState *) node); 2 算子 2.1 ExecSort /* 主要流程 */ TupleTableSlot *ExecSort(SortState *node) Tuplesortstate *tuplesortstate = node->tuplesortstate if !node->sort_Done: PlanState *outerNode = outerPlanState(node) tuplesortstate = tuplesort_begin_heap(ExecGetResultType(outerNode), numCols, sortColIdx, sortOperators) for (;;) /* 子节点扫描tup */ slot = ExecProcNode(outerNode) tuplesort_puttupleslot(tuplesortstate, slot) tuplesort_performsort(tuplesortstate) slot = node->ss.ps.ps_ResultTupleSlot tuplesort_gettupleslot(tuplesortstate, ScanDirectionIsForward(dir), slot) return slot /* 1 准备 */ Tuplesortstate *tuplesort_begin_heap(TupleDesc tupDesc, int nkeys, AttrNumber *attNums, Oid *sortOperators, Oid *sortCollations, bool *nullsFirstFlags) Tuplesortstate *state; state->comparetup = comparetup_heap state->copytup = copytup_heap; state->writetup = writetup_heap state->readtup = readtup_heap state->sortKeys = palloc0(nkeys * sizeof(SortSupportData)) for i in range(nkeys): SortSupport sortKey = state->sortKeys + i sortKey->ssup_attno = attNums[i] /* 2 扫描存储 */ void tuplesort_puttupleslot(Tuplesortstate *state, TupleTableSlot *slot) SortTuple stup COPYTUP(state, &stup, (void *) slot) puttuple_common(state, &stup) { switch state->status: case TSS_INITIAL: if state->memtupcount >= state->memtupsize: grow_memtuples(state) state->memtuples[state->memtupcount++] = *tuple if state->bounded: make_bounded_heap(state) inittapes(state) { int maxTapes = tuplesort_merge_order(state->allowedMem) + 1 tapeSpace = (int64) maxTapes *TAPE_BUFFER_OVERHEAD /* 创建临时文件 */ PrepareTempTablespaces() state->tapeset = LogicalTapeSetCreate(maxTapes) state->mergenext = palloc0(maxTapes * sizeof(int)) state->mergeavailslots = palloc0(maxTapes * sizeof(int)) ... ntuples = state->memtupcount state->memtupcount = 0 for j = 0; j < ntuples; j++ SortTuple stup = state->memtuples[j] tuplesort_heap_insert(state, &stup, 0, false) ... state->status = TSS_BUILDRUNS } dumptuples(state, false) { while state->memtupcount >= state->memtupsize: /* = writetup_heap */ WRITETUP(state, state->tp_tapenum[state->destTape], &state->memtuples[0]) tuplesort_heap_siftup(state, true) if state->memtupcount == 0 || state->currentRun != state->memtuples[0].tupindex: markrunend(state, state->tp_tapenum[state->destTape]) state->currentRun++ if state->memtupcount == 0: break selectnewtape(state) } case TSS_BOUNDED: if COMPARETUP(state, tuple, &state->memtuples[0]) <= 0: free_sort_tuple(state, tuple) else: free_sort_tuple(state, &state->memtuples[0]) tuplesort_heap_siftup(state, false) tuplesort_heap_insert(state, tuple, 0, false) case TSS_BUILDRUNS: if COMPARETUP(state, tuple, &state->memtuples[0]) >= 0: tuplesort_heap_insert(state, tuple, state->currentRun, true) else: tuplesort_heap_insert(state, tuple, state->currentRun + 1, true) dumptuples(state, false) } /* 3 排序 */ void tuplesort_performsort(Tuplesortstate *state) switch state->status: case TSS_INITIAL: /* 可在内存存放所有tup */ if state->memtupcount > 1: if state->onlyKey != NULL: qsort_ssup(state->memtuples, state->memtupcount, state->onlyKey) else: qsort_tuple(state->memtuples, state->memtupcount, state->comparetup, state) state->current = 0; state->status = TSS_SORTEDINMEM case TSS_BOUNDED: /* 使用堆排消除多余tup*/ sort_bounded_heap(state) state->status = TSS_SORTEDINMEM case TSS_BUILDRUNS: dumptuples(state, true) /* 将内存中所有元组写到持久化存储tape */ mergeruns(state) /* 合并执行 */ 2.2 tup读写 writetup_heap(Tuplestorestate *state, void *tup) MinimalTuple tuple = (MinimalTuple) tup

1 常用系统表与视图 1.1 系统表 1.2 视图 堆内存（区分线程） SELECT * FROM gs_thread_memory_context ORDER BY totalsize DESC; 堆内存（不区分线程） SELECT * FROM gs_shared_memory_detail ORDER BY totalsize DESC; 会话 SELECT * FROM pg_stat_activity; 1 新增系统表 获取空闲的oid cd src/include/catalog/ chmod +x unused_oids ./unused_oids 2 读取系统表 创建表时涉及的系统表操作： heap_create_with_catalog() Relation pg_class_desc = heap_open(RelationRelationId) Oid old_type_oid = GetSysCacheOid2(TYPENAMENSP) Oid relid = GetNewRelFileNode(pg_class_desc) Relation new_rel_desc = heap_create(relname) ObjectAddress new_type_addr = AddNewRelationType(relname) AddNewRelationTuple(pg_class_desc, new_rel_desc) AddNewAttributeTuples(relid, new_rel_desc->rd_att) ObjectAddress myself, referenced; myself.objectId = relid referenced.objectId = relnamespace recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL) recordDependencyOnOwner(RelationRelationId, relid) recordDependencyOnNewAcl(RelationRelationId, relid) recordDependencyOnCurrentExtension(&myself) StoreConstraints(new_rel_desc, cooked_constraints) 打开系统表 Relation heap_open(Oid relationId, LOCKMODE lockmode) Relation r = relation_open(relationId, lockmode) LockRelationOid(relationId, lockmode) Relation r = RelationIdGetRelation(relationId) Relation rd RelationIdCacheLookup(relationId, rd) RelIdCacheEnt hentry = hash_search(RelationIdCache, relationId) rd = hentry->reldesc RelationIncrementReferenceCount(rd) /* find cache, return */ if !rd->rd_isvalid: if (rd->rd_rel->relkind == RELKIND_INDEX) RelationReloadIndexInfo(rd); else: RelationClearRelation(rd) return rd; /* no cache, so build */ rd = RelationBuildDesc(relationId) HeapTuple pg_class_tuple = ScanPgRelation(targetRelId) relid = HeapTupleGetOid(pg_class_tuple) Form_pg_class relp = (Form_pg_class) GETSTRUCT(pg_class_tuple) relation = AllocateRelationDesc(relp) RelationGetRelid(relation) = relid RelationBuildTupleDesc(relation) if ..: RelationBuildRuleLock(relation) if ..: RelationBuildTriggers(relation) if ..: RelationBuildRowSecurity(relation) RelationParseRelOptions(relation, pg_class_tuple) RelationInitLockInfo(relation) RelationInitPhysicalAddr(relation) heap_freetuple(pg_class_tuple) RelationIncrementReferenceCount(rd) pgstat_initstats(r) typedef struct { Oid reloid; Relation reldesc; } RelIdCacheEnt; 扫描系统表 ...

SQL.别名 摘要 本文以实际使用为例，介绍SQL语法中别名的使用约束 目录 SQL.别名 1 介绍 2 示例 3 复杂场景 1 介绍 什么是别名： 别名语法: 表名 | 列名 | 表名.列名 [AS] 别名 2 示例 0 建表 CREATE TABLE t1 ( c1 INT, c2 INT, c3 INT ); INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2); 1 [列] 别名： SELECT c1 AS col1 FROM t1; 2 [表] 别名： SELECT c1 FROM t1 AS tbl1; 3 [表.列] 别名： ...

1 介绍JOIN 分类 INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN SELF JOIN CROSS JOIN 2 使用JOIN CREATE TABLE t1 (a1 INT, b1 TEXT); INSERT INTO t1 VALUES (1, 'u1'), (2, 'u2'), (3, 'u3'); CREATE TABLE t2 (b2 TEXT, c2 TEXT); INSERT INTO t2 VALUES ('u1', 'u1-1'), ('u1', 'u1-2'), ('u2', 'u2-1'), ('u4', 'u4-1'); -- INNER JOIN SELECT a1, b1, b2, c2 FROM t1 INNER JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 2 | u2 | u2 | u2-1 -- LEFT JOIN SELECT a1, b1, b2, c2 FROM t1 LEFT JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 2 | u2 | u2 | u2-1 3 | u3 | | -- RIGHT JOIN SELECT a1, b1, b2, c2 FROM t1 RIGHT JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 2 | u2 | u2 | u2-1 | | u4 | u4-1 -- FULL JOIN SELECT a1, b1, b2, c2 FROM t1 FULL JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 2 | u2 | u2 | u2-1 3 | u3 | | | | u4 | u4-1 -- SELF JOIN SELECT a1, b1, b2, c2 FROM t1 SELF JOIN t2 ON b1 = b2 ORDER BY a1, b1, c2; -- CROSS JOIN SELECT a1, b1, b2, c2 FROM t1 CROSS JOIN t2 -- can't use 'ON' ORDER BY a1, b1, c2; a1 | b1 | b2 | c2 ----+----+----+------ 1 | u1 | u1 | u1-1 1 | u1 | u1 | u1-2 1 | u1 | u2 | u2-1 1 | u1 | u4 | u4-1 2 | u2 | u1 | u1-1 2 | u2 | u1 | u1-2 2 | u2 | u2 | u2-1 2 | u2 | u4 | u4-1 3 | u3 | u1 | u1-1 3 | u3 | u1 | u1-2 3 | u3 | u2 | u2-1 3 | u3 | u4 | u4-1

@vb GaussDBThteadMain BackendRun PostgresMain PostgresInitializer::InitBackendWorker ::InitSession ::InitSysCache # 1 初始化缓存 RelationCacheInitializePhase2 if lsc: LocalTabDefCache::InitPhase2 Formrdesc("vb_profiles“") else: formrdesc("vb_profiles“) InitCatalogCache ::CheckAuthentication PerformAuthentication ClientAuthentication ::InitUser InitializeSessionUserId ::SetDatabase ::SetDatabaseByName ::InitSessionDatabase ::LockDataBase ::LoadSysCache # 2 初始化缓存 RelationCacheInitializePhase3 if lsc: LocalTabDefCache::InitPhase3 LoadCriticalIndex else: load_critical_index(ProfileRelationId) InitDatabase FinishInit InitCatalogCache RelationCacheInitialize

exec_simple_query('SELECT * FROM t1 WHERE c1 > 10') pg_parse_query pg_analyze_and_rewrite parse_analyze transformStmt transformSelectStmt transformWhereClause transformExpr transformAExprOp oper oper_search # 查找操作符所有相关函数 enforce_generic_type_consistency # 根据已知数据类型，找到最符合的函数，自动调用隐式转换 make_op pg_plan_queries PortalRun ExecInterpExpr ExecEvalExprSwitchContext -- ciphera_gt / int4gt

1 需求背景 客户使用vb什么版本？ 2215 客户的要求： 2 需求分析 2.1 适配点 在使用vastbase的多个阶段，都可能涉及pg_hba.conf 编译安装 初始化 升级 配置 gs_guc修改pg_hba 手动编辑pg_hba 启动 运维 pg_backup pg_rewind vb_ctl restart 2.2 代码流程 编译 初始化 main # initdb.cpp canonicalize_path # PGDATA find_other_exec # 在gs_initdb bin目录下，查找gaussdb bin # 在$GAUSSHOME/share/postgrsql目录下，检查以下文件是否存在 check_input('postgres.bki, postgres.description, pg_hba.conf.sample, postgresql.conf.sample, ..., system_views.sql, ...') check_locale_encoding init_log gs_getenv_r('GAUSSLOG') canonicalize_path('$PGDATA/PG_VERSION') fprintf(PG_MAJORVERSION = '9.2') setup_config readfile("postgresql.conf.sample") readfile("pg_hba.sample") writefile("pg_hba.conf") bootstrap_template1 write_version_file setup_auth get_set_pwd setup_depend load_plpgsql setup_sysviews 升级 配置 启动 pg_ctl 运行 PostmasterMain # postmaster 线程 InitializeGUCOptions getopt_r load_hba # 加载hba ServerLoop ConnCreate StreamConnection BackendStartup PostgresMain # postgres 线程 InitBackendWorker InitSession CheckAuthentication PerformAuthentication port->protocol_config->fn_authenticate ClientAuthentication hba_getauthmethod check_hba g_instance.libpq_cxt_comm_parsed_hba_lines # 读取缓存的hba if init_user: get_default_auth_method else: check_hostname sendAuthRequest recv_and_check_password_packet # 接收客户端的password crypt_verify for (;;) ReadCommand exec_simple_query('SQL') SIGHUB_handler load_hba 运维 ...

1 Page的概念 1.1 前置概念 首先，简单介绍一些基本概念： 关系 Relation：PostgreSQL是一款关系型数据库，在数据库中，1个表对应关系代数中的1个关系，即1个Relation。本博客中，将1个Relation指1个表 数据文件 RelFile：通过CREATE TABLE t1 (c1 INT, c2 TEXT)等语法创建1个表时，PostgreSQL会生成1个与表对应的数据文件，表中数据都将被存储至数据文件中 数据库页 Page：1个数据文件，由1个或多个大小为8192字节（即8k）的数据页组成 行 Row：在SQL语法INSERT INTO t1 VALUES (1, 'data1')中，(1, 'data1')是表中的1行数据 元组 Tuple：在PostgreSQL中，1个Tuple存储1行数据，数据越长，Tuple越长。1个Page可以存储多个Tuple 1.2 Page Page的格式如下：

1 特性背景 2 实现方案 2.1 系统表 1. pg_key_info 密钥信息 2. pg_key_data 密钥数据 3. pg_encryption 加密对象 2.2 定义表 2.3 定义表加密 2.4 操作表 2.5 管理表加密 2.6 管理主密钥 2.7 管理数据密钥 2.8 管理加密定义 2.8 未来计划 一、功能补充 二、语法复用 3 关键设计 3.1 格式设计 3.2 流程设计 整体流程 加密粒度 加密时机 加密判断 密钥存储 解密时机 解密判断 密钥来源 3.3 其他设计 3.2 主备密钥同步 4 特性源码 4.1 功能概览 基本功能篇 高级功能篇 加强篇 4.2 新增系统表 pg_key_info 4.2 新增系统表 pg_key_data 4.3 新增语法 CREATE KEY 4.4 新增语法 CREATE TABLE .. (protect=$data_key) 4.5 对加密对象的数据进行加密 参考 1 特性背景 2 实现方案 2.1 系统表 1. pg_key_info 密钥信息 keyoid keyname type alrotithm storage access protect create_time round 1 mk_1 master_key any localkms path=./keyfile $pass1 2024/02/01 13:01:01 - 2 dk_1 date_key aes_256_cbc database m100.d100.b1.i2 mk1 2024/02/01 13:01:01 0 2. pg_key_data 密钥数据 oid keydata algorithm 3 abcd1234 aes_256 3. pg_encryption 加密对象 type name oid special protect oldest_round table t1 100 - dk_1 1 2.2 定义表 定义表 定义表的语法与非加密表一致，我们通过单独的语法定义ENCRYPTION。 ...

接口 void smgrcreate(SMgrRelation reln, ForkNumber forknum, bool isRedo); // 创建文件 void smgrdounlink(SMgrRelation reln, bool isRedo); SMgrRelation smgropen(RelFileNode rnode, BackendId backend); void smgrclose(SMgrRelation reln); void smgrwrite(SMgrRelation reln, ForkNumber forknum, BlockNumber blocknum, char *buffer, bool skipFsync); void smgrread(SMgrRelation reln, ForkNumber forknum, BlockNumber blocknum, char *buffer); void smgrextend(SMgrRelation reln, ForkNumber forknum, BlockNumber blocknum, char *buffer, bool skipFsync); 调用关系 // 索引模块 btbuildempty() smgrwrite(metapage) _bt_blwritepage() smgrwrite() spgbuildempty() smgrwrite() |- FlushBuffer() ... |- FlushRelationBuffers() |- LocalBufferAlloc() smgrwrite() // FlushBuffer |- BufferAlloc() // readBuffer时，申请一个buffer，可能调用页面淘汰算法 |- FlushDatabaseBuffers() |- FlushRelationBuffers() |- FlushOneBuffer() FlushBuffer() // FlushOneBuffer XLogReadBufferForRedoExtended() FlushOneBuffer() // FlushRelationBuffers |- heap_sync() |- ATExecSetTableSpace() // ALTER TABLE SET TABLESPACE FlushRelationBuffers() // heap_sync when HEAP_INSERT_SKIP_WAL |- intorel_shutdown() |- CopyFrom() |- transientrel_shutdown() |- ATRewriteTable() heap_sync() // FlushDatabaseBuffers dbase_redo() FlushDatabaseBuffers() write时机： readBuffer时，如果buffer不在内存，且共享缓冲池已满，调用页面置换算法进行刷盘 alter table set table space copy