最近在给一套基于PostgreSQL内核的MPP分析型数据库做功能验证其中一个重点就是测试KADB对匿名代码块的支持情况。说到匿名代码块可能很多从业务SQL起步的同学会觉得陌生但做过数据库迁移或者写过复杂批量任务的人都清楚这玩意儿在实际运维和二次开发里相当常用。简单说匿名代码块就是不需要CREATE PROCEDURE、不需要持久化对象随手写一段PL/pgSQL程序体交给数据库执行的一次性代码PostgreSQL里用DO语句来做。这篇文章我就把整个测试过程、踩过的坑、以及最终如何判定支持还是部分支持的完整思路记录下来。内容不针对某个特定版本手头这套环境是典型的master-segment分布式架构和单机PostgreSQL的行为差异很大值得单独拎出来说。适合正在做国产化数据库选型、KADB兼容性验证、或者想在MPP架构上跑复杂PL/pgSQL逻辑的工程师参考。1. 先把概念理清楚KADB是什么匿名代码块又是什么1.1 KADB数据库的定位KADB是一款面向分析型场景的分布式数据库底层基于PostgreSQL生态整体架构采用主节点加多个数据节点的MPP模式。所谓MPP你可以理解成一个大仓库被拆分成多个小隔间每个节点只管自己那一亩三分地查询时所有节点一起干活最后把结果汇总回来。这种架构决定了它单表扫描能力非常强适合大数据量聚合分析但在一些细节行为上和单机PostgreSQL会有明显差异。之所以要单独验证KADB的匿名代码块支持是因为很多兼容性测试只盯着标准SQL和存储过程很少专门去测DO语句。但实际上DO语句在很多自动化脚本、数据修复、临时计算场景中是逃不掉的一环。如果数据库不支持或者支持得残缺运维脚本就得全部改成持久化函数工作量完全不是一个量级。再说一句KADB因为继承了PostgreSQL的生态SQL语法、psql客户端、扩展插件这些对使用习惯来说是很友好的。但继承不等于完全一致内核里的分布式调度器、事务管理器都是重写的行为差异往往就藏在这种不起眼的地方。1.2 匿名代码块DO语句的原理PostgreSQL里执行一段匿名代码块用的是DO关键字语法长这样DO $$ BEGIN RAISE NOTICE hello, anonymous block; END $$;这个$$不是写错了而是美元引用符用来把一整段PL/pgSQL代码包起来。美元引用的好处是可以避免单引号转义的麻烦而且可以自定义标签比如$body$ ... $body$这样嵌套多层字符串也不会乱套。匿名代码块的生命周期很短暂大致分成四步解析、编译、执行、销毁。第一次输入DO语句时服务端解析器会把代码体交给PL/pgSQL引擎编译成内部执行计划然后立即执行执行完毕就释放掉没有留下任何数据库对象。这也是它和存储过程最大的区别存储过程有名字、有权限体系、可以长期保存并反复调用匿名块则是一次性筷子。也正是因为这种用完即弃的特性匿名块特别适合做那些只跑一次、没必要入库的临时逻辑比如批量更新前的预检查、测试数据清理、调试一段复杂的循环逻辑等。1.3 为什么要在KADB上单独验证这个能力很多人在做数据库选型的时候有个惯性思维既然你说兼容PostgreSQL那PostgreSQL能跑的SQL你肯定也能跑。这个想法在标准SQL层面基本成立但在PL/pgSQL层面就得打个问号了。尤其是MPP数据库DO语句在分布式架构下存在几个天然的风险点第一执行位置问题。单机数据库的DO语句就在本地执行所有变量、游标、临时表的生命周期都很简单。但在MPP架构里DO语句通常在主节点上解析执行内部涉及的SQL语句需要分发到各个数据节点去执行这个分发过程能不能正确处理变量绑定、临时表、事务状态就是个大问题。第二事务语义问题。DO语句本身是包在一个事务里的如果内部抛异常整个事务回滚。但MPP下事务要协调多个节点两阶段提交、全局事务ID这些机制都参与进来出错回滚的代价比单机大得多。第三功能裁剪问题。有些数据库厂商为了性能或稳定性会刻意裁剪掉PL/pgSQL里过于灵活的功能比如动态SQL、游标、某些系统函数调用。这些裁剪往往不会写在产品介绍里只能靠自己实测。所以KADB支不支持DO语句、支持到什么程度直接影响后续运维脚本和二次开发的编码方式这个验证工作必须做还得做得细致。2. 测试前的环境准备与边界条件2.1 测试环境与版本信息先交代一下我这边的大致环境方便大家对照复现。测试用的是KADB的分布式集群形态配置了一个主节点、两个数据节点底层操作系统就是常规的Linux服务器。客户端工具用的psql这也是PostgreSQL生态里最标准的交互工具。组件数量用途KADB主节点1SQL入口DO语句在此解析KADB数据节点2存储数据执行下发的DMLpsql1执行测试SQL和DO块测试账号1模拟普通业务用户权限需要提醒的是版本号不同行为可能有差异。我手头这套环境应该是内核版本比较新的分支但我不建议你完全照搬结论最好拿着下面这些测试用例在自己的环境里跑一遍以实测结果为准。2.2 创建测试库与账号准备必要权限测试之前要把账号权限准备好否则一上来就会被各种权限错误卡住。我习惯单独建一个测试账号不直接用超级用户这样才能暴露出普通用户在真实业务中会遇到的问题。CREATE ROLE test_user LOGIN PASSWORD Test123; CREATE DATABASE testdb OWNER test_user; GRANT ALL ON DATABASE testdb TO test_user;这里有一个关键点要特别说明DO语句能否执行和PL/pgSQL语言的权限直接相关。在PostgreSQL较新的版本里plpgsql被标记为trusted语言普通用户默认有USAGE权限。但KADB在MPP架构下做了很多权限裁剪有时候普通用户执行DO块会报permission denied for language plpgsql。遇到这个报错解决办法是GRANT USAGE ON LANGUAGE plpgsql TO test_user;别小看这条命令很多测试卡在第一步就是栽在这儿。2.3 明确测试矩阵要覆盖哪些场景测试不能想到哪测到哪我习惯先列一个测试矩阵把各种维度都覆盖一遍。针对匿名代码块我把场景分成六类测试项验证目标基础DO块语法解析和基本执行是否正常变量与控制结构DECLARE、FOR、IF等是否兼容异常处理EXCEPTION块能否捕获错误事务回滚块内出错后DDL/DML是否整体回滚动态SQLEXECUTE拼接语句是否可用分布式行为临时表、分布式表DML、长事务表现每个测试项跑完后我不仅看输出结果还会去查数据库日志、执行计划确认语句是不是真的在每个数据节点上正确执行了。很多人只盯着有没有报错忽略了执行路径是否正确这两个维度得出的结论可能完全相反。3. 核心测试过程一步步验证匿名代码块3.1 最基础的DO匿名块看能不能跑起来第一个测试永远是最简单的先验证能跑。我执行的语句是DO $$ BEGIN RAISE NOTICE KADB support anonymous code block; END $$;正常执行后psql窗口会输出NOTICE: KADB support anonymous code block DO看到这个结果说明基础语法没问题。但先别高兴太早这只是第一步。我在测试时还额外做了两个动作一是打开详细错误输出用\set VERBOSITY verbose命令这样一旦后面出现错误能看到完整的错误位置和内部调用栈二是接着查了数据库日志确认这条DO语句确实是在主节点解析、并生成了执行任务而不是在某些兼容模式下被特殊处理掉了。这个最基础的用例过了才敢往下测复杂功能。如果连这么简单的匿名块都跑不通那后面全部不用测了直接给结论不支持就完事。3.2 带变量和控制结构的匿名块测试基础语法能跑接下来就测PL/pgSQL的核心能力变量声明、循环、条件判断。我用的测试用例是算1到10的累加和DO $$ DECLARE v_total INTEGER : 0; v_i INTEGER; BEGIN FOR v_i IN 1..10 LOOP v_total : v_total v_i; END LOOP; RAISE NOTICE sum %, v_total; END $$;输出结果NOTICE: sum 55 DO这说明DECLARE变量声明、FOR循环、字符串格式化输出这几个特性都正常。我继续加码测了IF条件嵌套、WHILE循环、CASE语句结果也都符合预期。PL/pgSQL的流程控制这一块KADB基本是把PostgreSQL的底子完整继承下来了。到这里可以初步判定KADB对匿名代码块的支持不是完全不可用而是有真实实现基础的。但距离完全支持还得继续往下测尤其是异常处理和事务回滚这两种行为在MPP架构下最容易隐身出问题。3.3 异常捕获与事务回滚行为匿名块里可以写EXCEPTION捕获异常被捕获的异常不会导致整个事务失败这个是PL/pgSQL的常见用法。我测试了除零错误的捕获DO $$ BEGIN BEGIN PERFORM 1 / 0; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE caught division by zero; END; END $$;输出NOTICE: caught division by zero DO异常捕获正常。但这还不够我还要测一个更关键的行为整个DO块的事务性。也就是说如果块内先执行了一段DDL然后故意抛出异常前面的DDL应该被整体回滚掉表不应该存在。测试用例DO $$ BEGIN CREATE TABLE t_rollback_test(id INT); RAISE EXCEPTION force rollback; END $$;这条语句执行后肯定会报错但表会不会留下来呢我接着查询SELECT to_regclass(t_rollback_test);结果返回空说明表确实被回滚了这个事务语义是正确的。这个测试很有价值因为很多MPP数据库在多个节点协同做DDL时回滚机制很容易出岔子能在KADB上看到整体回滚说明事务协调这块做得比较到位。顺带说一句PL/pgSQL块内直接写COMMIT是不允许的这在PostgreSQL里面就是明确限制KADB同样继承了这个限制。如果确实需要分步提交得用外部事务来控制而不是在匿名块里硬写。3.4 MPP分布式下的特殊场景测试单机行为测完接下来才是本次测试的重头戏分布式环境下的特殊行为。这一节的测试结果才是决定KADB匿名代码块够不够用的关键。第一个场景是临时表。在PostgreSQL单机下DO块里创建临时表是很常见的操作。我在KADB里执行DO $$ BEGIN CREATE TEMP TABLE tmp_test(id INT); INSERT INTO tmp_test VALUES (1), (2); RAISE NOTICE temp table count %, (SELECT COUNT(*) FROM tmp_test); END $$;执行结果正常计数值输出2。但要注意MPP下临时表默认挂在master节点上如果后续的分布式查询想关联这个临时表可能会因为数据分布问题产生广播或者报错这个属于分布式使用的边界认知不是匿名块本身的问题。第二个场景是分布式表上的DML。我建了一张按id分布的表然后在匿名块里做更新CREATE TABLE dist_test(id INT, val TEXT) DISTRIBUTED BY (id); INSERT INTO dist_test SELECT generate_series(1, 100), old; DO $$ BEGIN UPDATE dist_test SET val new WHERE id 50; END $$;执行成功。但光看不报错不够我还用EXPLAIN查看了执行计划确认UPDATE是分发到两个数据节点并行执行的。这才是分布式下真正有效的验证方式。第三个场景是长事务和锁的观察。匿名块里如果做大量数据修改整个块就是单个长事务这一点在MPP下会被显著放大因为所有数据节点都要持有锁直到全局事务结束。我故意在块里模拟了较长时间的循环更新果然在并发测试时其他会话出现了锁等待。这个问题不算不支持但属于使用时必须设计的边界条件后面我还会细说。4. 测试中遇到的问题与排查实录4.1 权限不足导致无法执行DO第一个问题出现在普通用户登录后执行最基础DO语句时。当时我用test_user连接数据库跑最简单的匿名块直接报错ERROR: permission denied for language plpgsql当时的反应是愣了一下的。因为碰过不少PostgreSQL分支大部分版本里plpgsql默认对Public开放使用没想到KADB这里限制得比较严。排查思路其实很清晰先查语言权限再查用户角色。用psql查看语言的权限信息\dL确认plpgsql语言没有授予Public或者test_user然后执行授权语句GRANT USAGE ON LANGUAGE plpgsql TO test_user;授权后再执行DO语句就正常了。这里给各位提个醒在KADB或者类似MPP数据库上做权限收敛的时候别光管表、库、模式的权限语言的USAGE权限也是个隐蔽的坑尤其在跑自动化脚本的时候一不小心就会被这个卡一下。4.2 匿名块中DDL的可见性问题另一个印象深刻的问题是有一次我在匿名块里创建了一张普通表块内查询正常但块结束后在外部用\d却看不到这张表。一开始以为是KADB的回滚机制出了问题后来仔细一查才发现是我自己在上一个测试里故意加了RAISE EXCEPTION但没注意块是否整体回滚导致那张表被回滚删除了。这是测试中很容易犯的操作盲区DO块本身的边界就是事务边界块内做的所有修改要么全成功要么全回滚。如果你希望DDL能保留下来就必须保证整个块没有任何异常抛出。排查这种问题的时候不能光盯着当前会话要回到事务语义上去理解同时配合to_regclass(表名)去确认对象到底存不存在。4.3 连接超时与锁等待第三个问题是性能维度的。我在测一个跑大批量更新的匿名块时块里面循环了十万行数据的更新操作结果另一个并发会话执行查询时卡住了等了几分钟直接报ERROR: canceling statement due to lock timeout这是因为KADB的MPP架构下一个更新事务要在所有涉及的数据节点上同时持锁锁的范围比单机数据库大得多。匿名块又是一个整体事务从块开始到块结束所有中间步骤的锁一直不释放一旦并发上来就导致其他查询长时间等锁。解决思路有两个方向。一是给会话设置锁超时时间让等待尽快暴露SET lock_timeout 5s;二是把大事务拆小不要在单个匿名块里做超大循环改成分批处理每批事务独立提交。结合分布式锁的特性后者才是真正治本的办法。匿名块擅长的是短小精悍的逻辑不适合做成大而全的批量作业。4.4 判定支持而不只是能用的完整标准测试到最后我给自己定了一个结论标准。要判定KADB支持匿名代码块不能只看它不报错至少得满足以下四点我整理成一张速查表判定维度验证方式结果语法兼容标准DO语句能否编译执行通过语义正确变量、控制流、异常结果是否符合预期通过事务一致块内出错后所有修改是否整体回滚通过分布路径内部DML是否实际下发到数据节点执行通过四条全部满足才敢下定论支持。如果你只测前两条很容易得出完全兼容PostgreSQL的乐观结论进而在生产环境踩到分布式特有的坑。我的结论是KADB对匿名代码块的核心功能是支持的但使用上要接受MPP架构带来的锁放大和长事务约束。5. 实测总结与后续使用建议5.1 结论判定核心支持边界受限经过完整测试最终结论可以归纳成一句话KADB对匿名代码块的功能支持基本到位核心语法和事务语义都能用属于核心支持、边界受限的状态。我用一个三色清单来归纳方便后续查阅能力分类具体说明完全支持DO基础语法、变量、流程控制、异常捕获、事务回滚、分布式表DML有条件支持临时表使用受限于master节点长事务在并发下可能放大锁等待受限不支持块内不能显式COMMITDO不返回结果集普通用户需额外授权语言权限如果你手头的KADB版本和我的有差异别照抄结论把第二章的测试矩阵拉出来跑一遍半小时就能得出自己的结论。5.2 适合用匿名代码块的场景实测下来下面这类场景用KADB匿名块是挺顺手的。第一类是一次性数据修复。比如线上某张分区表里出现了重复的脏数据需要按业务规则逐条判断并删除。写成DO块循环加判断全在一个事务里要么全成功要么全回滚还不用在库里留一个永久函数非常干净。第二类是复杂数据校验。在做数据迁移或者同步任务之后想快速核对两边数据是否一致可以写一个DO块把各种校验规则串起来发现不一致直接RAISE EXCEPTION让整个校验任务以错误状态暴露出来。第三类是自动化运维脚本里的临时逻辑。比如初始化一批测试账号、给一批历史数据打标签这种不常用但确实需要的逻辑写成匿名块嵌在Shell脚本里比创建存储过程更灵活也不会污染数据库对象。5.3 不适合用匿名代码块的场景有适合的就一定有不适合的。如果你发现自己正在下面几种场景里反复用DO块那就该考虑换方案了。第一公共逻辑的重复调用。同一个处理逻辑如果多个业务都在用必须创建正式的存储过程或者函数否则每次都要复制粘贴大段DO脚本维护成本极高而且别人根本不知道这段逻辑藏在哪个脚本里。第二需要参数化输入的场景。DO语句本身不接收外部参数想传参只能靠拼接字符串或者设置自定义变量非常别扭。这种需求直接用CREATE FUNCTION带参数才是正路。第三需要返回结果集给应用层的场景。DO块不返回结果集所有输出只能靠RAISE NOTICE打到日志里。如果你期望像调用函数一样拿到一张结果表别用DO写成RETURNS TABLE的函数。第四高频短事务场景。DO块每次执行都要走编译流程虽然单次开销不大但如果一秒执行几百次就明显不如预编译好的SQL语句或函数高效了。测试这个功能的时候我最大的体会就是数据库的兼容性从来不是二进制的黑或白而是分层次、分场景的灰度。KADB在标准PostgreSQL语法上的兼容做得不错但MPP架构带来的分布式语义差异必须靠实测去感知。就拿匿名代码块来说如果没有最后那轮分布式表的DML测试我可能就只停留在语法能跑的表面结论上根本意识不到长事务在并发环境下的锁放大问题。最后再分享一个小建议在KADB上使用匿名代码块之前先给PL/pgSQL语言做好授权再给你的脚本里加上lock_timeout这两个小动作能让你的匿名块跑得稳很多。
