PostgreSQL多版本并发控制简析

本篇基于PostgreSQL9.6版本。 并发引出的问题 在没有任何限制且数据只有一个版本的情况下,多个事务同时执行,可能出现以下问题: 脏读:一个事务读到了其他事务还未提交的数据。 不可重复读:一个事务重复读取同一记录,期间由于其他事务修改该记录并提交,导致读取的内容与之前不同。 幻读:一个事务重复读取符合条件的所有记录,期间由于其他事务新增或删除符合条件的记录,导致读取的结果与之前不同。 如果结合业务场景,可以衍生出更多具体的可讨论的现象。问题的根源在于,事务之间数据的隔离程度的高低。通常,脏读是不能容忍的,因为他操作了不确定的数据。在PostgreSQL中虽然支持多种事务隔离级别,但都不允许出现脏读。 PostgreSQL的事务隔离级别如下图, 并发控制的策略 # 锁 最直接的方式是用锁。锁是加在资源上的,对资源的操

PostgreSQL经验小结

本篇基于9.5版本。 >> 解决psql登入后不能输入中文 psql登入后提示No entry for terminal type “xterm”; using dumb terminal settings,查看/opt/PostgreSQL/9.5/bin/psql发现需要依赖某些文件。在系统中找到相关文件,然后创建软连接,再次登录即可输入中文。 >> 对象命名习惯 PostgreSQL中的对象名(表名、字段名、视图名、索引名、函数名、序列名等)是区分大小写字母的,但在SQL语句中直接使用大写字母的话会被自动转换成小写,解决办法是用双引号修饰对象名,阻止自动转换。为了降低复杂度和避免由此引发的bug,建议对象命名时不要用大写字母,单词之间用下划线分隔。 例如:t_user_info,i_user_

PostgreSQL结构与存储简析

本篇使用PostgreSQL版本为9.5。 数据文件目录 PostgreSQL的数据文件目录构成如下(截自Postgres-XL): 子目录global:存放全局范围的表和索引等对象,例如pg_database。 PostgreSQL实例在初始化的时候,默认创建两个表空间(tablespace),pg_global和pg_default。 其中pg_global对应的目录就是这个子目录global,可以通过以下SQL语句查询所有属于表空间pg_global的表和索引, 每个表或索引对应一个或多个文件,在子目录global下以pg_class.relfilenode的值命名,结构为:global/<pg_class.relfilenode>。 子目录base:存放定义在表空间pg_default下的数据库(databa

Postgres XL管理与维护

Postgres-XL本身是分布式的多节点的数据库,维护起来要比单机的PostgreSQL要复杂一些,但是很多方面其实跟PostgreSQL大同小异。以下是一些入门级别的实践小结。使用版本9.5。 集中式管理 Postgres-XL由gtm、gtm proxy、datanode、coordinator四种功能节点组成,就算只有2个datanode、2个coordinator,主机数量也可能达到五个。如果再配上standby节点、因请求压力增大而增加datanode数量,集群环境很容易突破十多个主机。这样一个不大不小规模的Postgres-XL要是经常开十多个窗口上去维护也会把人累个半死,并且对于一个发展中的小公司而言,这样的集群可能不止一个。所以,如果能在一个中心控制台远程操作所有主机,执行一些常用的简单的命令,将是一件省

PostgreSQL命令速查 – 规则与触发器

参考Postgres XL官方文档,PostgreSQL官方文档 规则 规则(Rule)是定义在表上的,当该表发生insert、update、delete、select等操作时,自动触发其他操作。规则只能依附于某个表存在,删表时会连带删除表上的规则。当后面的被触发语句执行发生错误时,前面的触发语句也执行失败。 触发器 普通触发器(TRIGGER)是定义在表、视图和外部表上的,当该表发生insert、update、delete、truncate等操作时,自动触发一个返回值是trigger的特殊函数。普通触发器只能依附于某个表存在,删表时会连带删除表上的触发器。普通触发器创建时分为row级别和statement级别,一些内置变量如NEW、OLD只能在row级别使用。 事件触发器 事件触发器(EVENT TRIGGER)是独立存

PostgreSQL命令速查 – 扩展模块

扩展模块可以认为是第三方提供的一批函数,通过CREATE/DROP EXTENSION someone来安装或移除,通过\dx命令查看所有已安装的扩展模块。 更多扩展模块参考PostgreSQL官方文档 bloom 基于Bloom filters的索引方式。Bloom filters用于判断一个元素是不是在一个集合里,判定有则很可能有,判定无则一定无。优点是省空间低耗时,缺点是有一定误算率。 btree_gin 基因倒排索引,查找速度快,适合静态数据。 btree_gist 通用搜索树索引,更新快,适合动态数据,但可能会产生错误的匹配。 citext 大小写不敏感的text类型,该类字段存入的文本在与其他文本比较时将无视大小写。 dblink 用于访问其他PostgreSQL数据库。 file_fdw 用于直接读取数据库以

PostgreSQL命令速查 – 事务与过程语言

官方文档参考这里,Exception码参考这里 事务 在PostgreSQL里,一组被BEGIN和COMMIT包围的语句称为一个事务块,没有显式发起BEGIN命令的语句默认作为单独一个事务执行。事务过程中失败将回滚。 PL/pgSQL PostgreSQL既支持直接执行一组plpgsql语句,又支持将语句写在function中通过函数名反复调用执行。一组过程语句被BEGIN和END包围,内部不形成事务,通过EXCEPTION块来处理异常情况。 直接执行一组plpgsql语句 创建自定义函数,存储过程

PostgreSQL命令速查 – 数据操作

参考Postgres XL官方文档,PostgreSQL官方文档 数据类型 【名称(别名)】 【大小】 【格式】 【描述】 smallint (int2) 2 bytes 数字:整数,取值范围-32768到+32767 integer (int,int4) 4 bytes 数字:整数,取值范围-2147483648到+2147483647 bigint (int8) 8 bytes 数字:整数,取值范围-9223372036854775808到+9223372036854775807 numeric (decimal) 变长 1.23 数字:浮点数,numeric(p,s) 例如numeric(5,2)表示精度xxx.xx real (float4) 4 bytes 数字:浮点数,6位十进制数精度 double preci

PostgreSQL命令速查 – 基础

参考Postgres XL官方文档,PostgreSQL官方文档 登入退出 反斜线命令 角色(用户USER、组GROUP是角色ROLE的别名)和权限 表空间(指定文件存放的磁盘目录,创建数据库时通过设置表空间来隔离IO) 数据库 模式(为了减少同一database里对象命名冲突而设计的组,缺省为public) 表 结构描述 序列 索引 视图 批量执行 导出导入