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 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的格式如下：

接口 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

1 Buffer基本介绍 1.1 Buffer解决的问题 实际应用场景中，PostgreSQL可能需要1分钟处理数十万次事务，频繁读写Page。Buffer提供以下2个基本功能： 加载Page至内存：PostgreSQL需要读写数时，都需先从磁盘中，将指定的Page加载至内存中，再在内存中操作Page 缓存常用Page：Page被加载至内存后，还会在内存停留一段时间，降低PostgreSQL直接从磁盘读写Page的频率 1.2 Buffer的基本功能 Buffer缓存Page时，需考虑以下几个场景： 插入Page：大部分事务操作表的Page时，先从Buffer中查找Page，如果Page不在Buffer中，再从磁盘读取Page，将其放入Buffer中，再操作Buffer中的Page 查找Page：访问Page时，通过文件名和Page编号等信息可确定唯一Page 访问Page：所有事务均可访问Buffer，需考虑多事务并发读写同一Pape的场景 淘汰Page：Buffer能缓存的Page数量有限，在Page数量过多时，需淘汰一些Page 落盘Page：Buffer中通常缓存表数据文件的Page，前文提到，事务持久化的标志是WAL日志落盘，因此，Buffer中的Page无需实时落盘，仅需异步落盘即可 2 Buffer的基本原理 2.1 插入Page 插入Page主要由读Page操作触发，上层函数读取Page的主要流程如下： 上层模块统一调用ReadBuffer()接口，读取指定表的指定Page ReadBuffer()中，先从Buffer查找Page，如果找到Page，则直接返回Buffer中的Page 如果未从Buffer找到Page，现在Buffer中预留存储Page的空间 之后，调用Smgr模块，从磁盘读取指定Page，并将其放入预留的空间内，再返回Buffer中的Page 函数调用关系大概如下图所示： ReadBuffer(Relation, BlockNumber) # 1 ReadBufferExtended() ReadBuffer_common() BufferAlloc() # 2,3 查找Page，如找到，返回；如未找到，预留存储Page的空间 smgrread(.., BlockNumber) # 4 如果未找到Page，从磁盘将Page读入预留的Page空间内，返回 2.2 查找Page 上层函数读Page时，首先进入查找Page阶段。 1.1 buffer池模型 模型： 1.2 buffer池接口 缓冲区接口： /* bufmgr.h */ /* 1 buffer池管理 */ void InitBufferPool(void); void InitBufferPoolAccess(void); void InitBufferPoolBackend(void); /* 2 buffer管理 */ /* 2.1 读buffer */ Buffer ReadBuffer(Relation reln, BlockNumber blockNum); Buffer ReadBufferExtended(Relation reln, ForkNumber forkNum, BlockNumber blockNum, ReadBufferMode mode, BufferAccessStrategy strategy); Buffer ReadBufferWithoutRelcache(RelFileNode rnode, ForkNumber forkNum, BlockNumber blockNum,ReadBufferMode mode, BufferAccessStrategy strategy); /* 2.2 清理buffer */ oid ReleaseBuffer(Buffer buffer); void UnlockReleaseBuffer(Buffer buffer); void MarkBufferDirty(Buffer buffer); void IncrBufferRefCount(Buffer buffer); Buffer ReleaseAndReadBuffer(Buffer buffer, Relation relation, BlockNumber blockNum); /* 2.3 刷盘buffer */ void CheckPointBuffers(int flags); void FlushOneBuffer(Buffer buffer); void FlushRelationBuffers(Relation rel); void FlushDatabaseBuffers(Oid dbid); void BufmgrCommit(void); bool BgBufferSync(void); /* 2.4 删除关联buffer */ void DropRelFileNodeBuffers(RelFileNodeBackend rnode, ForkNumber forkNum, BlockNumber firstDelBlock); void DropRelFileNodesAllBuffers(RelFileNodeBackend *rnodes, int nnodes); void DropDatabaseBuffers(Oid dbid); /* 2.5 锁buffer */ void UnlockBuffers(void); void LockBuffer(Buffer buffer, int mode); bool ConditionalLockBuffer(Buffer buffer); void LockBufferForCleanup(Buffer buffer); bool ConditionalLockBufferForCleanup(Buffer buffer); bool HoldingBufferPinThatDelaysRecovery(void); 1.3 buffer调用 插入数据 ...

5 wal落盘 WalWriterMain for :: XLogBackgroundFlush LWLockAcquire(LW_EXCLUSIVE) XLogWrite LWLockRelease() CommitTransaction RecordTransactionCommit BufmgrCommit XactLogCommitRecord # xlog记录commit TransactionTreeSetCommitTsData XLogFlush(XactLastRecEnd) for :: WaitXLogInsertionsToFinish XLogWrite write ResourceOwnerRelease AtEOXact_Buffers AtEOXact_RelationCache ... 1 概述 1.1 背景 https://zhmin.github.io/posts/postgresql-wal-format/ wal机制解决的问题：持久性、原子性、性能、数据同步、恢复 持久性需求： 执行一次事务/事务块，例如INSERT语句，除了要写数据文件，还需要更新系统表、索引文件、fsm文件、vm文件、事务提交文件等 为避免故障导致数据丢失，产生的数据写入磁盘才算事务成功 应用 数据库 磁盘 | 1. INSERT (5) --> | | 2. 读取数据页 <-- | | 3. 将数据写入数据页 | 4. 存储数据页 --> | | 5. 读取索引文件 --> | | 6... | 7. 读取fsm文件 --> | | 8... | 9. 读取vm文件 --> | | 10... | 11. INSERT 成功 <-- | 原子性需求： ...