Compare PostgreSQL and Oracle dead lock detect and transaction
背景 Oracle 和 PostgreSQL的死锁检测和处理有较大区别。 主要差别在于 : 1. 死锁被检测到的属于哪个SESSION？Oracle随机检出，从实验来看应该是第一个启动的死锁事务。而PostgreSQL是死锁发生时的最后一个事务，与ORACLE相反（从PG的deadlock_timeout参数...
Recent Posts
TOAST,The Oversized-Attribute Storage Technique - 暨存储格式main, extended, external, plain介绍
背景 PostgreSQL的数据类型有哪些存储格式？ 如何存储超过一个数据块大小的数据呢？ 正文 toast直接翻译的话就是切片面包(sliced bread)的意思，PostgreSQL中TOAST是一个缩写，全称是The Oversized-Attribute Storage Technique，为什么会...
一个事务最多能锁多少对象? how many objects can be locked per transaction
背景 在PostgreSQL中，表、视图、物化视图、序列、索引、TOAST等，统称为对象，那么在一个事务中可以锁多少个对象呢？（我不打算在这里讲锁级别。可以参考锁级别文章） 通过pg_locks这个视图可以查看到当前数据库的锁信息（注意行锁在行头，不会体现在pg_locks中，也不占用内存）。 或者直接通过如...
Recover droped column from PostgreSQL
背景 PostgreSQL中使用alter table tbl drop column 删除列的操作，实际上只是标记列为删除状态。并不是里面就回收该列占用的空间。 只有当对原来存在的行发生UPDATE时或新插入行的时候才会影响真实的列记录。 恢复测试一： 对于执行alter table tbl drop c...
PostgreSQL 只读模式 , hot standby recovery 模式等介绍，以及如何建立只读账号
背景 有些时候，我们可能需要将数据库变成只读模式，或者提供一些只读账号。 比如需要给开发人员一些自由查询功能的账号，或者当数据库用了多少空间后，将其锁定为只读模式。 让数据库进入只读有几种方法， 1. 弱只读的方法，设置默认事务为只读，这种方法只能实现弱只读，因为用户可以在会话中改成写模式。 2. 进入r...
PostgreSQL’s Cursor USAGE with SQL MODE - 分页优化
背景 今天谈一下cursor SQL, 函数中CURSOR中的使用另外再谈. PostgreSQL客户端请求数据库返回大批量数据有几种常见的方法。 一、SELECT 二、CURSOR 三、分页取(ORDER BY OFFSET x LIMIT n) 环境: PostgreSQL 9.0.2 RHE...
WHY prepared Statement running slower in some situation CASE
背景 在某些情况下，使用PREPARED STATEMENT或函数，可能会比直接执行SQL更慢。为什么呢？ 这个要从执行计划说起， 下面来看一个测试表 digoal=> \d tbl_user Table "digoal.tbl_user" Column ...
Compare dblink module Within One Transaction in PostgreSQL - dblink, 自治事务(Oracle兼容性)
背景 PostgreSQL plpgsql函数为原子调用，如果需要实现阶段性提交，可以通过dblink调用，实现自治事务。 dblink实现自治事务 PostgreSQL 9.0.3 RHEL 5 x86_64 在PostgreSQL的存储函数中不支持自治事务，啥意思呢？也就是说一个存储过程里的SQ...
PostgreSQL’s two-phase commit used with dblink example - 2PC , 两阶段事务
背景 我在前面一篇博客有提到PostgreSQL的事务中如果混合了本地SQL和远程执行的SQL的话，可能会导致事务不完整。 那么怎么来解决这种混合事务的完整性问题呢?没错，prepare transaction为我们提供了强大而有弹性的two-phase commit功能。 下面来举例说明一下: 测试环境：...
PostgreSQL 随机查询优化
背景 在某些场景可能会需要随机的从表中取出记录。方法比较多，简单的方法可能给数据库带来巨大的开销，下面开始举例说明，看看如何优化一个随机查询。 测试表: create table tbl_user(id serial8 primary key,firstname varchar(32),lastname va...