PostgreSQL Oracle 兼容性之 - insert all into … 多表写入
背景 Oracle支持 insert all 的语法，同时往多个表插入。 https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm https://docs.oracle.com/cd/B19306_01/server...
Recent Posts
PostgreSQL oracle 兼容性 - 字符串内嵌 NULL字符（空字符）chr(0) 转换为 chr(32)
背景 在Oracle中，存储字符串时，允许用户将空字符存到字符串中，虽然这种用法可能不常见，但是给Oracle迁移到PG的用户带来了一些小麻烦，因为PG中chr(0)是作为结束符来处理的，不允许作为用户输入传入字符串中。 如果要存储chr(0)字符，PostgreSQL 必须存储在字节流类型中。 Oracle...
PostgreSQL 优化器案例之 - order by limit 索引选择问题
背景 当我们在执行一个这样的SQL时，假如有这样几个索引(c1,c2) (id)，数据库到底该用哪个索引呢？ explain select * from tbl where c1=200 and c2=200 order by id limit 10; 或 explain select * from t...
PostgreSQL WAL replay 加速(datapage preload) - 恢复加速, 备库延迟优化
背景 PostgreSQL 数据库恢复时，读取wal，如果当前wal page不是full page，则从这笔wal record对应的data file中拿到datapage，与wal record合并，覆盖对应data page。持续读取wal 实现恢复的目的。 需要注意wal是顺序读写，而data fil...
PostgreSQL 空间类型统计信息(Statistics, Cardinality, Selectivity, Estimate)不准确导致SQL执行计划不准(包含、相交查询)的优化实践
背景 PostgreSQL 通过几何类型以及PostGIS插件，支持丰富的空间类型，空间数据类型包括KNN检索，空间包含，空间相交，方位搜索，空间计算等操作。 为了得到正确的执行计划，在数据库优化器中，评估满足某个操作符条件的行数，是一个重要的CBO依据。 而评估选择性又依赖统计信息，同时依赖每种操作符的选择...
PostgreSQL jdbc 错误代码映射(SQLSTATE)
背景 Does such a class enumerating the PostgreSQL error codes already exist? Yes, it does: org.postgresql.util.PSQLState However, there are 238 error codes l...
PostgreSQL quorum based同步复制模式在极端情况下的0丢失破坏问题
背景 PostgreSQL 9.6开始支持了quorum based 同步复制机制，当客户端发起事务结束请求时，必须要等这笔事务对应的redo日志复制到了指定副本，主库才响应客户端。 从而保证客户端正常收到主库反馈后，WAL日志一定已经有了多个副本，保证数据的0丢失。 但是在极端情况下，可能无法保障0丢失，为...
PostgreSQL 如何检测分布式死锁 - postgrespro pg_shardman
背景 单机的死锁检测是比较容易实现的，通过判断等待中的事务是否查询环状，即可实现。例如下面的图，A等B, B等C, C等A，出现环状，即认为死锁。 而对于分布式事务，由于相互等待的事务可能出现在不同的数据节点上，如何判断分布式死锁呢？ 为了判断是否有环，必须引入全局事务号，或者有标识可以在每个数据节点上标...
PostgreSQL 模拟两个update语句死锁 - 利用扫描方法
背景 在单条UPDATE中模拟死锁，需要借助扫描方法，以及明确让AB两个会话分别锁定一条记录后再锁定对方已经锁定的记录。 利用批量update的语法以及values子句即可实现，这里利用了嵌套循环，确保被更新的表被多次扫描，并且每次扫描时两个会话更新的记录被错开，达到死锁效果。 同时为了让速度慢下来，使用pg...
PostgreSQL 电商业务(任意维度商品圈选应用) - json包range数组的命中优化 - 展开+索引优化
背景 电商，任意维度商品圈选应用，其中一个查询请求是这样的： 求 “某个国家、某个时间点、调价+折扣后的价格” 落在某个价格范围的商品。 首先需要有的要素包括： 1、商品ID 2、不同国家的商品价格 3、商品原价 4、商品日常价 5、不同时间段的价格折扣 6、调价系数 例子 1、表结构设计 ...