PostgreSQL

一、PostgreSQL简介

  PostgreSQL数据库是目前功能最强大的开源数据库，支持丰富的数据类型（如JSON和JSONB类型、数组类型)和自定义类型。而且它提供了丰富的接口，可以很容易地扩展它的功能。
  PostgreSQL有以下优势：
  1、稳定可靠。PostgreSQL是唯一能做到数据零丢失的开源数据库。
  2、开源省钱。PostgreSQL数据库是开源的、免费的，而且是BSD协议，在使用和二次开发上基本没有限制。
  3、支持广泛：PostgreSQL数据库支持大量的主流开发语言，包括C、C+、Perl、Python、Java，以及PHP等。
  4、PostgreSQL社区活跃。

• PostgreSQL和MySQL的对比
    MySQL有以下几个缺点：

  1、功能不够强大。MySQL的多表连接查询方式只支持“Nest Loop"，不支持“hash join”和“sort merge join”。不仅如此，还有很多SQL语法它也不支持，子查询性能比较低。
  2、性能优化工具和度量信息不足。MySQL在运行过程中如果出现问题，只产生很少的性能数据，很难让维护人员准确定位问题产生的原因。MySQL的复制是异步的，无法通过Master/Slave做到数据零丢失。

  相对这些MySQL的弱点，PostgreSQL有以下几个优点：

  1、PostgreSQL功能强大。支持所有主流的多表连接查询的方式（如：“Nest loop”、“hash join”“sort merge join”等)；支持绝大多数的SQL语法（如：with子句）。PostgreSQL对正则表达式支持较强、内置函数也很丰富。
  2、性能优化工具与度量信息丰富。PostgreSQL数据库中有大量的性能视图，可方便地定位问题。
  3、从PostgreSQL9.1开始，支持同步复制功能。通过master和slave之间的复制可以实现零数据丢失的高可用方案。

  另外，由于MySQL对SQL语法支持的功能较弱，基本上不适合做数据仓库。而PostgreSQL不仅支持复杂的SQL,还支持大量的分析函数，非常适合做数据仓库。

• PostgreSQL和Oracle的对比
    PostgreSQL与Oracle的不同之处在于：PostgreSQL有更多的支持互联网特征的功能。PostgreSQL数据类型支持网络地址类型、XML类型、JSON类型、UUID类型，以及数组类型，有强大的正则表达式函数，where条件中可以使用正则表达式匹配，可以使用Python、Perl等语言写存储过程等。
    另外，PostgreSQL更小巧。Oracle安装包动则几个GB以上，PostgreSQL安装包只有几十MB大小。在任何一个环境都可以容易地安装PostgreSQL。

二、数据类型

  PostgreSQL支持较常见的数据类型：

  为了提高SQL的兼容性，部分类型还有很多别名，如integer类型，可以用int、int4表示；smallint可以用int2表示；char varying(n)可以用varchar((n)表示；numeric(m,n)可以用decimal(m,n)表示，等等。

2.1 布尔类型

  boolean的状态要么是"true"（真)，要么是"false’“(假)，如果是"unknown”(未知)状态，用NULL表示。boolean在SQL中可以用不带引号的TRUE或FALSE表示，也可以用更多的表示真和假的带引号的字符表示，如’true’、‘false’、yes’、'no’等。

• 布尔类型的操作符
    布尔类型可以使用的操作符是逻辑操作符和比较操作符。
    常用的逻辑操作符有：AND、OR、NOT。
    AND、OR操作结果：
  
    NOT操作结果：
  
    布尔类型可以使用“IS”比较运算符：

expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN

2.2 数值类型

• 整数类型
    整数类型有三种：smallint、int、bigint。
    常用的类型是int(或integer)),因为它提供了在范围、存储空间、性能之间的最佳平衡。一般只有在磁盘空间紧张时才使用smallint；只有在integer的范围不够时才使用bigint，因为前者绝对快得多。
• 精确的小数类型
    精确的小数类型可用numeric、numeric(m,n)、numeric(m)表示。这个类型特别适合用于货币金额和其他要求精确计算的场合。
    如果要声明一个字段的类型为numeric，可以用语法：NUMERIC(precision,scale)。其中，精度precision必须为正数，标度scale可以为零或正数。
    当字段声明了标度，超过小数点位数的标度会被自动四舍五入。而对于既没有声明精度也没有声明标度的number类型来说，则会原样存储。
    对于声明了精度的数值，如果insert插入的数值大于声明的精度范围，则会报错。
• 浮点数类型
    数据类型real和double precision是不精确的、变精度的数字类型。
• 序列类型
    在序列类型中，serial和bigserial与MySQL中的自增字段是一个意思。PostgreSQL实际上是通过序列(sequence)实现的。
• 货币类型
    货币类型(money type)可以存储固定小数的货币数目，与浮点数不同，它是完全保证精度的。其输出格式与参数lc_monetary的设置有关，不同国家的货币输出格式不一样。
• 数学操作符
  
• 数学函数
    dp表示double precision。
  
  

2.3 字符串类型

  varchar(n)和char(n)分别是character varying(n)和character((n)的别名，没有声明长度的character等于character(I)。
  这些类型的存储长度是4字节加上实际的字符串，比如说，character的存储长度为4+n，n为定义时的长度。
  虽然在某些其他的数据库系统里，定长的character(n)有一定的性能优势，但在PostgreSQL里与varchar((n)没有差别。故在大多数情况下，应该使用text或varchar。
  字符串类型有丰富的函数和操作符：

2.4 二进制数据类型

  PostgreSQL只有一种二进制类型：bytea。此数据类型允许存储二进制字符串，对应MySQL和Oracle中的blob类型。

2.5 日期和时间类型

  PostgreSQL的时间类型可以精确到秒以下，而MySQL的时间类型只能精确到秒。
  日期、时间和inteval类型之间可以进行加减乘除运算：


  日期、时间和inteval类型的函数：


  PostgreSQL提供了许多返回当前日期和时间的函数：

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)
now()
transaction_timestamp()

  CURRENT TIME和CURRENT TIMESTAMP返回带有时区的值；LOCALTIME和LOCALTIMESTAMP返回不带时区的值。
  CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP可以有选择地给予一个精度参数，该精度会导致结果的秒数域四舍五入到指定的小数位。如果没有精度参数，将给予所能得到的全部精度。

2.6 枚举类型

  枚举类型是一个包含一系列有序的静态值集合的数据类型。
  与MySQL中不同，在PostgreSQL中要使用枚举类型需要先使用CREATE TYPE创建一个枚举类型。示例：

CREATE TYPE week AS ENUM ('Sun','Mon','Tues','Wed','Thur','Fri''Sat');
CREATE TABLE duty(person text,weekday week);
INSERT INTO duty values('张三'，'Sun'):
INSERT INTO duty values('李四'，'Mon');

  如果输入的字符串不在枚举类型之间，则会报错。
  在枚举类型中，值的顺序是创建枚举类型时定义的顺序。
  枚举类型的值是大小写敏感的，因此“Mon”不等于“mon”。标签中的空格也是一样，如“Mon”(Mon后有一个空格)不等于“Mon”。
  枚举类型的函数：

三、数据库管理

3.1 数据库操作

  数据库的基本操作包括创建数据库、删除数据库、修改数据库等。

-- 创建数据库
CREATE DATABASE osdbadb;
-- 修改数据库
ALTER DATABASE name[[WITH]option[···]]
-- 改变数据库testdbe01的最大连接数为10
alter database testdb01 CONNECTION LIMIT 10;
-- 改变数据库testdb01的名称为mydb01
alter database testdb0l rename to mydb01;
-- 删除数据库
DROP DATABASE IF EXISTS name
-- 如果存在某个数据库,则删除（不存在也不报错）
drop database if exists mytestdb01;

3.2 表操作

  最常见的建表：

CREATE TABLE table name (
	col01_namme data_type,
	col02_namme data_type,
	col03_namme data_type
);

• 临时表
    PostgreSQL支持两类临时表，一种是会话级的临时表，一种是事务级的临时表。在会话级别的临时表中，数据可以一直保存在整个会话的生命令周期中，而在事务级别的临时表，数据只存在于这个事务的生命周期中。
    临时表的建表语法与普通表相似，多了TEMPORARY：

create TEMPORARY table

• 默认值
    建表时，可以为一个字段指定默认值。若已指定默认值的列新插入了一行，但设定了默认值的字段数值没有声明，那么这些字段将被自动填充为它们各自的默认值。示例：

createe table student (no int,name varchar(20),age int default 15);

• 约束
    约束有以下几类：检查约束、非空约束、唯一约束、主键、外键。
    检查约束是最常见的约束类型。使用该约束后，在设置某个字段里的数值时必须使这个约束的表达式的值为真。比如，要限一个人的年龄在0~150之间，示例：

CREATE TABLE persons (
	name varchar (40),
	age int CHECK (age >=0 and age <=150),
	sex boolean
）;

  非空约束只是简单地声明一个字段必须不能为NULL，示例：

CREATE TABLE books (
	book_no integer not null,
	name text,
	price numeric
)

  唯一约束保证在一个字段或一组字段里的数据相较于表中其他行的数据是唯一的，示例：

CREATE TABLE books (
	book_no integer UNIQUE,
	name text,
	price numeric
)

  外键约束是表之间关系的一种约束，用于约束本表中一个字段或多个字段的数值必须出现在另一个表的一个字段或多个字段中。这种约束也可以称为两个相关表之间的参照完整性约束。如“学生表”与“班级表”之间的关系，一个学生一定是某个班级的学生：

CREATE TABLE class (
	class_no int primary key,
	class_name varchar (40)
);
CREATE TABLE student (
	student_no int primary key,
	student_name varchar (40),
	age int,
	class_no int REFERENCES class (class_no )
）;

• 修改表
    修改表是通过“alter table”命令来实现的。使用这个命令可以做如下操作：

增加字段
删除字段
增加约束
删除约束
修改默认值
修改字段数据类型
重命名字段
重命名表

  给班级表增加一个“班主任(class_teacher)”的字段，示例：

ALTER TABLE class ADD COLUMN class_teacher varchar(40);

  删除班级表的字段“class teacher”，示例：

ALTER TABLE class drop COLUMN class_teacher;

  增加约束示例：

ALTER TABLE student ADD CHECK (age <16);
ALTER TABLE class ADD CONSTRAINT unique class_teacher UNIQUE (class teacher);

  删除约束语法：

ALTER TABLE student DROP CONSTRAINT constraint_name;

  删除约束示例：

ALTER TABLE student DROP CONSTRAINT student_age_check;
-- 非空约束是没有名称的，需要使用下面的语法去除约束：
ALTER TABLE student ALTER COLUMN student_name DROP NOT NULL;

  修改字段默认值示例：

ALTER TABLE student ALTER COLUMN age SET DEFAULT 15;

  设置默认值的操作并不会影响表中现有的任何数据行，它只是会为将来的INSERT命令改变默认值。
  删除字段默认值示例：

ALTER TABLE student ALTER COLUMN age DROP DEFAULT;

  这样实际上相当于把默认值设置为NULL。
  修改字段数据类型示例：

ALTER TABLE student ALTER COLUMN student_name TYPE text;

  在修改某字段类型之前，最好删除这个字段上的约束，修改完后再把合适的约束添加上去。
  重命名字段语法示例：

ALTER TABLE books RENAME COLUMN book_no TO book_id;

  重命名表语法示例：

ALTER TABLE class RENAME TO classes;

• 表继承
    表继承是PostgreSQL中特有的东西。假设有一张人员表“persons”:

CREATE TABLE persons (
	name text,
	sex boolean,
	age int
);

  现在再加一个学生表“student’”，学生表比人员表多了一个班级号“class_no”的字段：

CREATE TABLE students (
    class_no int
)INHERITS (persons);

  如果修改student表里的数据，persons表里的数据也会随之修改。如果往persons表里插入数据，student表里是看不到的。
  当查询父表时，会把这个父表中子表的数据也查询出来，反之则不行。如果只想把父表本身的数据查询出来，只需要在查询的表名前加“OLY”关键字，示例：

select * from only persons;

  所有父表的检查约束和非空约束都会自动被所有子表继承。不过其他类型的约束（唯一、主键、外键)则不会被继承。
  一个子表可以从多个父表继承，这种情况下它将拥有所有父表字段的总和，并且子表中定义的字段也会加人其中。如果同一个字段名出现在多个父表中，或者同时出现在父表和子表的定义里，那么这些字段就会被“融合”，因此在子表里就只有一个这样的字段。但要想融合，字段的数据类型必须相同，否则就会抛出一个错误。融合的字段将会拥有其父字段的所有检查约束，并且如果某个父字段存在非空约束，那么融合后的字段也必须是非空的。
  采用SELECT、UPDATE、DELETE等命令访问或操作一张父表时，也会同时访问或操作相应的子表，而使用ALTER TABLE命令修改一张父表的结构定义时，大多数情况下也会同时修改子表的结构定义，但“REINDEX”、“VACUUM”命令不会影响到子表。
  此外，唯一约束、外键的使用域也不会扩大到子表上。

• 分区表
    PostgreSQL是通过表继承来实现分区表的。表分区就是把逻辑上的一个大表分割成物理上的几个小块。
    分区的好处：

1. 删除历史数据时更快，如果是按时间分区的，在删除历史数据时，直接删除历史分区就可以了，如果没有分区，通过DELETE删除历史数据时会很慢。
2. 某些类型的查询性能可以得到极大提升，特别是表中访问率较高的行位于一个单独分区或少数几个分区上的情况下。如果在按时间分区的表中，大多数查询发生在时间最近的一个分区或几个分区中，而较早时间的分区比较少查询。
3. 当查询或更新一个分区的大部分记录时，连续扫描那个分区而不是使用索引离散地访问整个表，可以获得巨大的性能提升。
4. 很少用到的历史数据可以使用表空间的技术移动到便宜一些的慢速存储介质上。因为使用分区表可以将不同的分区安置在不同的物理介质上。

  在使用继承实现的分区表时，一般会让父表为空，数据都存储在子表中。
  建分区表步骤：

  1)创建“父表”，所有分区都从它继承。这个表中没有数据，不要在这个表上定义任何检查约束，除非你希望约束所有的分区。同样，在其上定义任何索引或唯一约束也没有意义。
  2)创建几个“子表”，每个都是从主表上继承的。通常，这些表不会增加任何字段。我们把子表称作分区，实际上它们就是普通的PostgreSQL表。
  3)给分区表增加约束，定义每个分区允许的键值。
  4)对于每个分区，在关键字字段上创建一个索引，也可创建其他你想创建的索引。严格来说，关键字字段索引并非必需的，但是在大多数情况下它是很有帮助的。如果你希望关键字值是唯一的，那么应该总是给每个分区创建一个唯一约束或者主键约束。
  5)定义一个规则或者触发器，把对主表的数据插人重定向到合适的分区表。
  6)确保constraint_exclusion里的配置参数postgresql…conf是打开的。打开后，如果查询中WHERE子句的过滤条件与分区的约束条件匹配，那么这个查询会智能地只查询这个分区，而不会查询其他分区。

  看一个分区表的例子，假设有一张销售明细表，定义：

CREATE TABLE sales detail(
	product_id int not nul1,--产品编号
	price numeric(12,2),--单价
	amount int not nul1,--数量
	sale_date date not null,-销售日期
	buyer varchar(40),--买家名称
	buyer_contact text --买家的联系方式
);

  主表就是上面的sales detail表。现在按销售日期进行分区，每个月一个分区：

CREATE TABLE sales_detail_y2014m01 (CHECK (sale_date >DATE '2014-01-01'AND
sale date_DATE '2014-02-01'))INHERITS (sales_detail);
CREATE TABLE sales_detail_y2014m02 (CHECK (sale_date >DATE '2014-02-01'AND
sale date DATE '2014-03-01'))INHERITS (sales_detail);
CREATE TABLE sales_detail_y2014m03 (CHECK (sale_date >DATE '2014-03-01'AND
sale date DATE '2014-04-01'))INHERITS (sales_detail);
...
CREATE TABLE sales_detail_y2014m12 (CHECK (sale_date >DATE 2014-12-01'AND
sale date DATE '2015-01-01'))INHERITS (sales_detail);

  每个分区实际上都是一张完整的表，只不过它们是从sales_detail表继承定义的。父表sales_detail中实际是不存数据的。以后若要删除旧数据，只需要删除最早月份的表即可。每个分区表中都加了一个约束，这表示只允许插入本月内的数据。
  一般情况下，还可以在分区键“sale_date”上建索引：

CREATE INDEX sale_detail_y2014m01_sale_date ON sales_detail_y2014m01 (sale_date);
CREATE INDEX sale_detail_y2014m02_sale_date ON sales_detail_y2014m02 (sale_date);
CREATE INDEX sale_detail_y2014m03_sale_date ON sales_detail_y2014m03 (sale_date);
...
CREATE INDEX sale_detail_y2014m12_sale_date ON sales_detail_y2014m12 (sale_date);

3.3 视图操作

  视图就是由查询语句定义的虚拟表。对用户来说，看到的视图就如同一张真的表。从视图中看到的数据可能来自数据库中的一张或多张表，也可能来自外部，这主要取决于视图的查询语句是如何定义的。
  使用视图一般有以下几个原因：

  可使复杂的查询易于理解和使用。
  安全原因。视图可以隐藏一些数据，如在一张用户表中，可以通过定义一张视图，把密码等敏感字段去掉。
  把一些函数返回的结果映射成视图。

  一般数据库提供的视图大多是只读的，在PostgreSQL数据库中提供的视图默认也是只读的，但可以使用规则系统做出一张可更新的视图。
  创建视图的语法：

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW name [(column_name [,...])]
AS query

  例如，建一张用户表“users’”,其中包含了敏感字段“password”,现在要建一张视图把敏感字段“password”排除：

CREATE TALBE users(
	id int,
	user_name varchar (40),
	password varchar(256),
	user_email text,
	user_mark text
);
CREATE VIEW vw_users AS SELECT id,user_name,user_email,user_mark FROM users;

  也可以使用TEMP”或“TEMPORARY”关键字来建一张临时视图，当session结束时，视图就不存在了：

CREATE temp VIEW vw_users AS SELECT id,user_name,user_email,user_mark FROM users;

  视图也能为查询的各列定义另一个名称。例如将原表中的id、user_name、user_email、user_mark等列名重新定义为no、name、email、mark：

CREATE VIEW vw_users(no,name,email,mark)
AS 
SELECT id,user_name,user_email,user_mark FROM users;

3.4 索引操作

  建立索引的好处是加快对表中记录的查找或排序。但建索引也是有代价的：

  1、增加了数据库的存储空间。
  2、在插入和修改数据时要花费较多的时间，因为索引也要随之更新。

  索引除了有加快查询的作用外，还有一些其他的用处，比如唯一索引还起到唯一约束的作用。
  PostgreSQL中常见的索引底层实现有B-tree和Hash。

  B-tree：最常用的索引，B-tree索引适合处理等值查询和范围查询。
  Hash：只能处理简单的等值查询。

• 创建索引
    创建索引的语法：

CREATE [UNIQUE] INDEX [CONCURRENTLY] [name] ON table_name [USING method]
	({column_name | (expression )} [COLLATE collation] [opclass] [ASC | DESC] [ NULLS {FIRST | LAST}][,···])
	[WITH (storage_parameter value [,..])]
	[TABLESPACE tablespace_name]
	[WHERE predicate]

  比如有一张联系人表：

CREATE TABLE contacts(
	id int primary key,
	name varchar (40),
	phone varchar (32)[],
	address text
);

  由于一个人可能有多个电话号码，所以把phone定义是一个数组。
  为了实现按姓名（name)快速查询，可以在字段“name”上建一个简单的B-ree索引。

CREATE INDEX idx_contacts_name on contacts(name);

• 修改索引
    修改索引的语法：

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET(storage parameter=value[,...])
ALTER INDEX name RESET(storage_parameter[,···])

  修改索引示例：

-- 索引重命名
ALTER INDEX idx_contacts_name RENAME TO idx_contacts_name_old;
-- 把索引移到表空间tbs_data01下
ALTER INDEX idx_contacts_name_old SET TABLESPACE tbs_data0l;

• 删除索引
    删除索引的语法：

DROP INDEX [IF EXISTS] name [,··.] [CASCADE | RESTRICT]

  删除索引示例：

DROP INDEX IF EXISTS idx_contacts_name_old;

  删除索引时，默认会使用选项“RESTRICT”,所以加不加关键字“RESTRICT”都是一样的，如果有依赖对象依赖这个索引，则删除会失败。而使用“CASCADE”选项，表示当有依赖于这个索引的对象时，一并把这些对象删除掉，例如外键约束。示例：

DROP INDEX index_unique_class_no CASCADE;

四、并发

• ACID
    在日常操作中，对于一组相关操作通常需要其全部成功或全部失败。在关系型数据库中，这组相关操作称之为事务。在一个事务中，多个插人、修改、删除操作要么全部成功，要么全部失败，这称为“原子性”，实际上一个事务除了有这个特性以外，还需要有其他三个特性，即“一致性”、“隔离性”和“持久性”，即ACID。
    原子性（atomicity)：事务必须以一个整体单元的形式进行工作，对于其数据的修改，要么全都执行，要么全都不执行。如果只执行事务中多个操作的前半部分就会出现错误，那么必须回滚所有的操作，让数据在逻辑上回滚到先前的状态。
    一致性(consistency)：事务在完成时，必须使所有的数据都保持一致状态。
    隔离性（isolation)：事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务是不会查看中间状态的数据的。
    持久性（durability)：事务完成之后，它对于系统的影响是永久性的。即使今后出现致命的系统故障（如机器重启、掉电)，数据也将一直保持。

  在PostgreSQL中，可使用多版本并发控制(MVCC)来维护数据的一致性。相比于锁定模型，多版本并发控制的主要优点是在MVCC里对检索（读）数据的锁请求与写数据的锁请求不冲突，读不会阻塞写，而写也从不阻塞读。

• 自动提交
    PostgreSQL与其他数据库最大的区别是，大多数DDL可以包含在一个事务中，而且也是可以回滚的。因为有这个功能，所以非常适合把PostgreSQL作为Sharding的分布式数据系统
  的底层数据库。比如，在Sharding中，常常需要在多个节点中建相同的表，这时可以考虑把建表语句放在同一个事务中，这样就可以在各个节点上先启动一个事务，然后执行建表语句了。如果某个节点执行失败，也可以把前面已执行建表成功的操作回滚掉，这样自然就不会出现部分结点建表成功，部分结点失败的情况。
    在psql的默认配置下，自动提交“AUTOCOMMIT”项是打开的，也就是说，每执行一条SQL语句，都会自动提交。可以设置psql中的内置变量“AUTOCOMMIT”来关闭自动提交：

set AUTOCOMMIT off

• 保存点SAVEPOINT
    PostgreSQL支持保存点（SAVEPOINT)的功能，在一个大的事务中，可以把操作过程分成几个部分，第一个部分成功后，可以建一个保存点，若后面的执行失败，则回滚到这个保存点，而不必把整个事务都回滚掉。
    SAVEPOINT的使用示例：

-- 开启事务
begin;
-- 往表1插入2条数据
insert into testtab01 values(1);
insert into testtab01 values(2);
-- 建立保存点
savepoint my_savepoint01;
-- 往表2插入数据
insert into testtab02 values(1);
insert into testtab02 values(1);
-- 到这里，由于唯一键约束导致插人失败，这时不必回滚整个事务，只需要回滚到上一个保存点就可以了
-- 此时表1的数据并没有被回滚掉
rollback to SAVEPOINT my_savepoint01;
-- 再往表2中插人数据，成功后，把整个事务提交：
insert into testtab02 values(1);
insert into testtab02 values(2);
-- 提交
commit;
-- 表1和表2数据都插入成功

4.1 事务

• 事务隔离级别
    数据库的隔离级别有4种：

READ UNCOMMITTED：读未提交
READ COMMITTED：读已提交
REPEATABLE READ：重复读
SERIALIZABLE：串行化

  脏读：一个事务读取了另一个未提交事务写入的数据。
  不可重复读：指一个事务重新读取前面读取过的数据时，发现该数据已经被另一个已提交事务修改了。在大多数情况下，这还是可以接受的，只是在少数情况下会出现问题。
  幻读：一个事务开始后，需要根据数据库中现有的数据做一些更新，于是重新执行一个查询，返回一套符合查询条件的行，这时发现这些行因为其他最近提交的事务而发生了改变，导致现有的事务如果再进行下去就可能会在逻辑上出现一些错误。

  读已提交是PostgreSQL里的默认隔离级别。当一个事务运行在这个隔离级别时，SELECT查询（没有FOR UPDATE/SHARE的子句）只能看到查询开始之前已提交的数据，而无法看到未提交的数据或在查询执行期间其他事务已提交的数据。不过SELECT看得见其自身所在事务里前面尚未提交的更新结果。实际上，SELECT查询看到的是在查询开始运行瞬间的一个快照。请注意，在同一个事务里两个相邻的SELECT命令可能看到不同的快照，因为可能有其他事务会在第一个SELECT执行期间提交。

• 两阶段提交
    PostgreSQL数据库支持两阶段提交协议。
    在分布式系统中，事务往往包含了多台数据库上的操作。单台数据库的操作能够保证原子性，而多台数据库之间的原子性，就需要通过两阶段提交来实现了，两阶段提交是实现分布式事务的关键。
    两阶段提交的5个步骤：

  1、应用程序先调用各台数据库做一些操作，但不提交事务；然后调用事务协调器（这个协调器可能也是由应用自己实现的)中的提交方法。
  2、事务协调器将联络事务中涉及的每台数据库，并通知它们准备提交事务，这是第一阶段的开始。在PostgreSQL一般是调用“PREPARE TRANSACTION”命令。
  3、各台数据库接收到“PREPARE TRANSACTION”命令后，如果要返回成功，则数据库必须将自己置于以下状态：确保后续能在被要求提交事务时提交事务，或者在被要求回滚事务时能回滚事务。所以PostgreSQL会将已准备好提交的信息写入人持久存储区中。如果数据库无法完成此事务，它会直接返回失败给事务协调器。
  4、事务协调器接收到所有数据库的响应。
  5、在第二阶段，如果任一数据库在第一阶段返回失败，则事务协调器将会发一个回滚命令(ROLLBACK PREPARED)给各台数据库。如果所有数据库的响应都是成功的，则向各台数据库发送“COMMIT PREPARED”命令，通知各台数据库事务成功。

  在使用二阶段提交功能时，需要把参数“max_prepared_transactions”设置成一个大于零的数字。设置这个参数后需要重启数据库，直接设置这个参数是不能成功的。
  一个二阶段提交的例子：

-- 建一张表做测试
create table testtab01(id int primary key);
-- 启动事务,插入1条数据
begin;
insert into testtab0l values(1);
-- 使用"PREPARE TRANSACTION"命令准备好事务提交（第一阶段）
PREPARE TRANSACTION 'osdba_global_trans_0001';

  命令中“osdba_global_trans_0001”是两阶段提交中全局事务的ID，由事务协调器生成(事务协调器也可能是由应用实现的，事务协调器会持久化这个全局事务ID。PostgreSQL数据库一旦成功执行这条命令，就会把此事务持久化，意思是即使数据库重启，此事务既不会回滚，也不会丢失)。
  此时，重启数据库。重启后，使用“COMMIT PREPARED”真正提交这个事务提交（第二阶段）：

COMMIT PREPARED'osdba_global_trans_0001';

  此时，数据真正插入成功。
  从该例子可以看出，一旦成功执行“PREPARE TRANSACTION”命令，事务就会被持久化，即使重启数据库，仍然可以提交这个事务，事务中的操作不会丢失。

4.2 锁

  在PostgreSQL数据库中有两类锁：表级锁和行级锁。当要查询、插人、更新、删除表中的数据时，首先是要获得表上的锁，然后再获得行上的锁。

• 表级锁
    表级锁模式：

  表级锁冲突情况：

  “N”表示这两种锁冲突，也就是不同的用户不能同时持有这两种锁，“Y”表示可以兼容。
  表级锁只有“SHARE”和“EXCLUSIVE”这两种锁。很容易理解，这两种锁基本就是读、写锁的意思。加了“SHARE”锁后相当于加了读锁，表的内容就不能变化了。可为多个事务加上此锁，只要任意一个人不释放这个读锁，则其他人就不能修改这个表。加上了“EXCLUSIVE”后，则相当于加了写锁，这时别的进程既不能写也不能读这条数据。
  但是，后来数据库又加上了多版本的功能。有了该功能后，如果改某一行的数据，实际上并没有改原先那行数据，而是另复制出了一个新行，修改都在新行上，事务不提交，别人是看不到这条数据的。由于旧的那行数据没有变化，在修改过程中，读数据的人仍然可以读到旧的数据，这样就没有必要让别人不能读数据了。若是在多版本功能下，除了以前的“SHARE”锁和“EXCLUSION”锁外，还需要增加两个锁，一个叫“ACCESS SHARE”,表明加上这个锁，即使正在修改数据的情况下也允许读数据，另一个锁叫作“ACCESS EXCLUSION”,意思是即使有多版本的功能，也不允许访问数据。
  表级锁加锁的对象是表，这使得加锁的范围太大，导致并发不高，于是人们提出了行级锁的概念，但行级锁与表级锁之间会产生冲突，这时就需要有一种机制来描述行级锁与表级锁之间的关系。在MySQL中是使用“意向锁”的概念来解决这个问题的，方法就是当我们要修改表中的某一行数据时，需要先在表上加一种锁，表示即将在表的部分行上加共享锁或排他锁。PostgreSQL也是这样实现的，如“ROW SHARE”和“ROW EXCLUSIVE”这两个锁，实际就对应MySQL中的共享意向锁(IS)和排它意向锁(IX)。从“意向锁”的概念出发，可以得到意向锁的下面两个特点：

  意向锁之间总是不会发生冲突的，即使是“ROW EXCLUSIVE”之间也不会发生冲突，因为它们都只是“有意”要做什么，还没有真做，所以是可以兼容的。
  意向锁与其他非意向锁之间的关系和普通锁与普通锁之间的关系是相同的。举例：“X”与“X”锁是冲突的，所以“X”锁与“X”是冲突的；“X”与“S”锁是冲突的，所以“X”锁与““S”也是冲突的；“S”与“S”锁是不冲突的，所以“IS”与“S”也是不冲突的。

  如果把共享锁“SHARE”简写为“S”,排他锁“EXCLUSIVE”简写为“X”,把“ROW SHARE”简写为“IS”,把“ROW EXCLUSIVE”简写为“X”,这四种锁之间的关系则如下：

  意向排他锁“X”它们自己互相之间是不会冲突的，这时可能就需要一种稍严格的锁，就是这种锁自己之间也会冲突，至于和其他锁冲突的情况则与“X”相同，这种锁在PostgreSQL中就叫“SHARE UPDATE EXCLUSIVE”。不带FULL选项的VACUUM、CREATE INDEX CONCURRENTLY命令都会请求这样的锁。这也很好理解，因为这些命令除了不允许在同一个表上并发执行本操作外，其他情况与更新表时对表加锁的需求是一样的。
  在PostgreSQL中还有一种锁，称为“SHARE ROW EXCLUSIVE”,这种锁可以看成是同时加了“S”锁和“IX”锁的结果，PostgreSQL命令不会自动请求这个锁模式，也就是说PostgreSQL内部目前不会使用这种锁。
  总结一下，PostgreSQL中有8种表锁，最普通的是共享锁“SHARE”和排他锁“EXCLUSIVE”，因为多版本的原因，修改一条数据的同时，允许了读数据，所以为了处理这种情况，又加了两种锁“ACCESS SHARE”和“ACESS EXCLUSIVE”,锁中的关键字“ACCESS"是与多版本读相关的。
  此外，为了处理表锁和行锁之间的关系，有了“意向锁”的概念，这时又加了两种锁，即意向共享锁和意向排他锁。由于意向锁之间不会产生冲突，而且意向排他锁互相之间也不会产生冲突，于是又需要更严格一些的锁，这样就产生了“SHARE UPDATE EXCLUSIVE”和“SHARE ROW EXCLUSIVE”这两种锁。于是，总共就有了8种锁。
  在PostgreSQL中，显式在表上加锁的命令为“LOCK TABLE”命令，此命令的语法：

LOCK [TABLE] [ONLY] name [,...] [IN lockmode MODE] [NOWAIT]

  name：表名。
  lockmode：就是前面介绍的几种表级锁模式，即ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE。
  NOWAIT：如果没有NOWAIT这个关键字时，当无法获得锁时，会一直等待，而如果加了NOWAIT关键字，在无法立即获取该锁时，此命令会立即退出并且发出一个错误信息。

• 行级锁
    行级锁模式比较简单，只有两种，即“共享锁”和“排他锁”，或者可以说是“读锁”或“写锁”。而在PostgreSQL中不称其为“读锁”的原因是，由于有多版本的实现，所以实际读取行数据时，并不会在行上执行任何锁（包括“读锁”）。
    显式地加行级锁命令：

SELECT ... FOR {UPDATE | SHARE} [OF table_name [,...] [NOWAIT] [...]] 

  此命令中的“NOWAIT”关键字与“LOCK TABLE”中的相同，加了“NOWAIT”后，若无法获得锁，则直接报错，而不会一直等待。
  如果在FOR UPDATE或FOR SHARE中使用“OF table name”明确指定了表名字，那么只有这些被指定的表会被锁定，其他在SELECT中使用的表将不会被锁定。一个其后不带“OF table name’”的FOR UPDATE或FOR SHARE子句将锁定该命令中所有使用的表。如果FOR UPDATE或FOR SHARE是应用于一个视图或者子查询的，那么它会锁定该视图或子查询中使用到的所有表。但有一种情况例外，就是主查询中引用了WITH查询时，WTH查询中的表并不会被锁定。如果想锁定WITH查询内的表行，需要在WITH查询内指定FORUPDATE或FOR SHARE关键字。

• 死锁
    死锁是指两个或两个以上的事务在执行过程中相互持有对方期待的锁，若没有其他机制，它们都将无法进行下去。例如，事务1在表A上持有一个排斥锁，同时试图请求一个在表B上的排斥锁，而事务2已经持有表B的排斥锁，同时却在请求表A上的一个排斥锁，那么两个事务就都不能执行了。PostgreSQL能够自动侦测到死锁，然后会退出其中一个事务，从而允许其他事务完成。
    死锁发生的4个必要条件：

  1、互斥条件：指事务对所分配到的资源加了排他锁，即在一段时间内只能由一个事务加锁占用。如果此时还有其他进程请求排他锁，则请求者只能等待，直至持有排他锁的事务释放为止。
  2、请求和保持条件：指事务已经至少持有了一把排他锁，但又提出了新的排他锁请求，而该资源上的排他锁已被其他事务占有，此时请求被阻塞，但同时它对自己已获得的排他锁又保持不放。
  3、不剥夺条件：指事务已获得的锁，在未使用完之前，不能被其他进程剥夺，只能在使用完时由自己释放。
  4、环路等待条件：指在发生死锁时，必然存在一个事务-资源的环形链，即事务集合{T0,T1,T2,…,Tn}中的T0正在等待一个T1持有的排他锁；P1正在等待P2持有的排他锁，…，Pn正在等待P0持有的排他锁。

  防止死锁的最好方法通常是保证所有使用一个数据库的应用都以相同的顺序在多个对象上请求排他锁。比如，在应用编程中，人为规定在一个事务中只能以一个固定顺序来更新表。假设在数据库中，有A、B、C、D四张表，现规定只能按如下顺序修改这几张表：“B→C→A→D”,若某个进程先更新了A表，然后又想更新C表，则必须先回滚先前对A表的更新，然后再按规定的顺序，先更新C表，再更新A表。

五、执行计划

  PostgreSQL中explain命令：

EXPLAIN [(option [,...])] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

  命令的可选项“options”为：

ANALYZE boolean
VERBOSE boolean
COSTS boolean
BUFFERS boolean
FORMAT TEXT XML JSON YAML

  ANALYZE选项通过实际执行的SQL来获得相应的执行计划。因为它真正被执行，所以可以看到执行计划每一步花掉了多少时间，以及它实际返回的行数。
  加上ANALYZE选项后，会真正执行实际的SQL,如果SQL语句是一个插入、删除、更新或CREATE TABLE AS语句，这些语句会修改数据库。为了不想影响实际的数据，可以把EXPLAIN ANALYZE放到一个事务中，执行完后回滚事务，如下：

BEGIN;
EXPLAIN ANALYZE···:
ROLLBACK;

  VERBOSE选项用于显示计划的附加信息。这些附加信息有：计划树中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称。该选项值默认为FALSE。
  COSTS选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。该选项
值默认为TRUE。
  BUFFERS选项显示关于缓冲区使用的信息。该参数只能与ANALYZE参数一起使用。显示的缓冲区信息包括共享块、本地块和临时块读和写的块数。共享块、本地块和临时块分别包含表和索引、临时表和临时索引，以及在排序和物化计划中使用的磁盘块。上层节点显示出来的块数包括其所有子节点使用的块数。该选项值默认为FALSE。
  FORMAT选项指定输出格式，输出格式可以是TEXT、XML、JSON或YAML。非文本输出包含与文本输出格式相同的信息，但其他程序更容易解析。该参数默认为TEXT。

• 执行计划示例
  
    结果中“Seq Scan on testtab01”表示顺序扫描表“testtab01”,顺序扫描也就是全表扫描，即从头到尾地扫描表。后面的内容“(cost=0.00.184.00rows=10000 width=36)”可以分为三部分：

  cost=0.00.184.00:“cost=”后面有两个数字，中间是由“”分隔，第一个数字“0.00”表示启动的成本，也就是说返回第一行需要多少cost值；第二个数字表示返回所有的数据的成本。
  rows=10000:表示会返回10000行。
  width=36:表示每行平均宽度为36字节。

  成本“cost”描述一个SQL执行的代价是多少，默认情况下不同的操作其“cost”的值如下：

顺序扫描一个数据块，cost值定为1。
随机扫描一个数据块，cost值定为4。
处理一个数据行的CPU,cost为0.01。
处理一个索引行的CPU,cost为0.005。
每个操作符的CPU代价为0.0025。

• 执行计划的输出格式
    默认情况下，输出的执行计划是文本格式，但也可以输出Json格式：
  
    也可以输出xm格式：

explain (format xml) select * from testtab0l;

  也可以输出YAML格式：

explain (format YAML) select * from testtab0l;

  加上“analyze”参数后，可通过实际执行来获得更精确的执行计划，示例：

  可以看出，加了“analyze”参数后，可以看到实际的启动时间（第一行返回的时间)、执行时间、实际的扫描的行数：(actual time=-0.493.4.320rows=10000 loops=-1),其中启动时间为0.493毫秒，返回所有行的时间为4.320毫秒，返回的行数是10000。
  联合使用analyze选项和buffers选项，可通过实际执行来查看实际的代价和缓冲区命中的情况，示例：

  因为加了buffers选项，执行计划的结果中就会出现一行“Buffers:shared hit=l6284 read=196482 written-=196450”,这行中“shared hit=-16284”表示在共享内存中直接读到16284个块，整行表示从磁盘中读了16482块，写磁盘共196450块。有人问SELECT为什么会写？这是因为共享内存中有脏块，从磁盘中读出的块必须把内存中的脏块挤出去，所以产生了很多的写。

• 全表扫描
    全表扫描在PostgreSQL也称为顺序扫描(seq scan),全表扫描就是把表的所有数据块从头到尾读一遍，然后从数据块中找到符合条件的数据块。全表扫描在EXPLAIN命令输出的结果中用“Seq Scan”表示，示例：
  
• 索引扫描
    索引通常是为了加快查询数据的速度而增加的。索引扫描，就是在索引中找出需要的数据行的物理位置，然后再到表的数据块中把相应的数据读出来的过程。索引扫描在EXPLAIN命令输出的结果中用“Index Scan”表示，示例：
  
• 位图扫描
    位图扫描也是走索引的一种方式。方法是扫描索引，把满足条件的行或块在内存中建一个位图，扫描完索引后，再根据位图到表的数据文件中把相应的数据读出来。如果走了两个索引，可以把两个索引形成的位图进行“and”或“or”计算，合并成一个位图，再到表的数据文件中把数据读出来。
    当执行计划的结果行数很多时会进行这种扫描，如非等值查询、IN子句或有多个条件都可以走不同的索引时。
    示例：
  
    在位图扫描中可以看到，“Bitmap Index Scan”先在索引中找到符合条件的行，然后在内存中建立位图，之后再到表中扫描，也就是看到的“Bitmap Heap Scan”。
    会看到“Recheck Cond:(id2>l0000)”,这是因为多版本的原因，当从索引找出的行从表中读出后，还需要再检查一下条件。
• 条件过滤
    条件过滤，一般就是在where条件上加的过滤条件，当扫描数据行时，会找出满足过滤条件的行。条件过滤在执行计划中显示为“Filter”，示例：
  
    如果条件的列上有索引，可能会走索引，不走过滤。SQL示例：

EXPLAIN SELECT FROM testtab0l where id<1000;

• Nestloop Join
    嵌套循环连接(Nestloop Join)是在两个表做连接时最朴素的一种连接方式。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大(>10000不适合)，要把返回子集较小的表作为外表，而且在内表的连接字段上要有索引，否则会很慢。
    执行的过程为：确定一个驱动表(outer table),另一个表为inner table,驱动表中的每一行与inner表中的相应记录JON类似一个嵌套的循环。适用于驱动表的记录集比较小(<10000)而且inner表有有效的访问方法（Index)。需要注意的是，JON的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。
• Hash Join
    优化器使用两个表中较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
    这种方式适用于较小的表可以完全放于内存中的情况，这样总成本就是访问两个表的成本之和。但是如果表很大，不能完全放入内存，优化器会将它分割成若干不同的分区，把不能放入内存的部分写入磁盘的临时段，此时要有较大的临时段以便尽量提高/O的性能。
    Hash Join示例：
  
    看表大小：
  
    因为表“'testtab01”大于“'testtab02”,所以HashJoin是先在较小的表“testtab02”上a建立散列表，然后扫描较大的表“testtab01”并探测散列表，找出与散列表匹配的行。
• Merge Join
    通常情况下散列连接的效果比合并连接好，然而如果源数据上有索引，或者结果已经被排过序，在执行排序合并连接时就不需要排序了，这时合并连接的性能会优于散列连接。
    下面的示例中，表testtab01和表testtab02的id字段上都有索引，且从索引扫描的数据已经排好序了，可以直接走Merge Join了：