PostgreSQL使用

postgresql安装#

包管理器安装#

如果使用官方的包管理器安装，默认情况下pgsql会被按照到/usr/local下，其配置文件可以在/etc下对应的文件夹中找到，其他文件一般可以在/var下的对应位置找到。
并且会创建一个名为postgres的数据库超级用户用于管理数据库，和一个同名的仓库。登陆时需要切换到postgres用户下，直接使用psql命令就可以进入默认的数据库中——在ubuntu中使用apt安装的pgsql16，默认的用户登录模式是peer，如果不修改配置文件就只能这么登录，原因在于peer模式下，会检查登录数据库的用户是否是当前系统用户，如果不是就不能通过校验。如果不是peer模式，可以使用psql -U postgres来指定使用postgres用户登录数据库。

源码安装#

使用源码安装可以更加灵活地自定义所要安装地模块，在官网下载对应的源码就不用说了，下载bz2压缩格式的压缩包更小一点，使用tar -jxvf xxx.tar.bz2就可以解压对应的压缩包。
进入对应文件夹下，直接使用./configure来生成make文件，configure过程中可能出现找不到一些三方库的情况，比如zlib，在ubuntu下直接sudo apt install zlib1g-dev即可，需要指定是开发者版本，即后面要加上-dev后缀。
另外，如果要指定自定义路径，使用--prefix=/usr/local/pgsql来指定要安装的路径，其他选项使用./configure --help可以查看。
configure完成后，直接make+sudo make install二连即可。
pgsql有一些有用的额外组件在contrib文件夹下，如果需要安装进入contrib文件夹下，继续make+sudo make install。
到此源码的安装就基本结束了，然后配置一下环境变量，比如在/etc/bash.bashrc中添加

export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

建议也新建一个系统用户，用于管理数据库，比如postgres，然后在对应的终端配置文件.bashrc中添加

export $PGDATA=/home/postgres/pgdata

指定数据库的数据目录路径，切换到postgres用户下，执行initdb，就可以看到数据库创建成功了，使用pg_ctl start -D $PGDATA命令启动数据库服务，使用pg_ctl stop [im mode]结束服务。

创建用户和权限#

创建用户命令

create user name [with] [options]

option	说明
LOGIN/NOLOGIN	是否允许用户作为登录用户使用。
CREATEDB/NOCREATEDB	是否允许用户创建数据库。
CREATEROLE/NOCREATERELE	是否允许用户创建新用户。
INHERIT/NOINHERIT	是否允许用户继承创建它的角色的权限，默认是允许。
REPLICATION/NOREPLICATION	是否允许用户作为复制用户使用。
BYPASSRLS/NOBYPASSRLS	允许用户绕过行级安全策略。
CONNECTION LIMIT connlimit	设置用户可以同时打开的连接数上限。
ENCRYPTED/UNENCRYPTED PASSWORD 'passwd'	设置密码，并且是否加密保存。
VALID UNTIL 'timestamp'	设置密码的有效期，过期后用户必须更改密码。
...

比较常用的也就是create user namej with PASSWARD 'passwd'。

在创建了用户后，需要赋予用户应有的权限：

grant all privilege on database dbname to username;  赋予操作数据库的权力
grant all select update insert on table tablename to username;  // 赋予操作表的权力

具体有哪些权力可以用到再查吧，反正不用也会忘。
另外，pgsql还有个SCHEMA的概念，类似与命令空间的意思，所以可能赋予了用于操作数据库的权力，但可能还是创建不了表，还需要赋予使用对应SCHEMA的权力：

grant usage on schema schemaname to username;

刚创建的数据库默认是有个public schema的。

可以使用超级用户创建数据库后将权限赋予创建的用户，然后使用set role username;来切换用户。

psql常用命令#

登录#

pgsql登录数据库的完整命令如下：

psql -h <hostname or ip> -p <port> [databasename] [user] 

帮助#

使用psql --help可以获取和psql后可跟随的选项。
在登录到数据库后，使用\h指令，来获取数据库相关的说明文档，后跟随一个指令，比如\h select可以获取select的说明文档。

\d命令#

\d可用于查看数据库的各种信息：

\d [pattern][+]

pattern可以是表、视图、索引、序列等，后面加个加号会显示更详细的信息。

• 如果只想显示匹配的表，使用\dt;
• 如果只想显示索引，使用\di;
• 如果只想显示序列，使用\ds;
• 如果只想显示视图，使用\dv;
• 如果只想显示函数，使用\df;
• 如果只想显示schema，使用\dn;
• 如果要显示所有表空间，使用\db;
• 要显示所有用户或角色，使用\du或\dg;
• 显示表的权限分配，使用\dp table或\z；
• 使用\dT查看自定义的枚举类型。

pgsql中，枚举类型需要用户自己定义：CREATE TYPE color AS ENUM ('red', 'green', 'blue');。

显示执行耗时#

使用\timing后，会打印语句的执行耗时。

指定字符集#

如果客户端的字符编码和服务器的不同，可能会出现乱码，可以使用\encoding命令来指定客户端的字符编码，比如\encoding gbk、\encoding utf8。

格式化输出#

使用\pset [option [value]]设置执行结果的输出格式。
比如默认的输出只有内边框，如果要设置为外边框，使用\pset border 2；设置为无边框，使用\pset border 0。
默认的输出格式是经过对齐处理的，如果要将执行结果输出为其他程序可读的文件可以去掉格式化处理，使用\pset format unaligned，这样输出的内容就取消了对齐。
默认的分隔符是“|”，可以\pset fieldsep '\t'设置分隔符为制表符。
然后，通过\o filename，可以将执行结果输出到指定的文件中。
这样输出的文件中是包含了表头的，去除表头可以使用\t来开启或去除。

执行外部SQL命令#

使用\i filename可以读取系统中的一个文件，然后执行存储在其中的SQL语句。

逻辑结构#

基本层级#

pgsql的数据库系统中，数据组织层级可分为三层：

1. 数据库：一个pgsql服务中可以有多个数据库，当连接到一个数据库时，就可以访问该数据库的数据，但不能访问别的数据库的数据；
2. 表，索引：一个数据库中可以有多张表和索引。区别于其他库的术语“Table”，pgsql中使用“Relation”来表述；
3. 数据行：每张表中都有很多个数据行，在pgsql中使用数据“Tuple”，在别的数据库中称为“Row”。

在pgsql中，一个服务（或称实例）下可以有多个数据库database，但一个数据库不可以属于多个实例；区别于Oracle的一个实例只能由一个数据库，但一个数据库可以在多个实例中。

基本操作#

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

创建#

语法：

create database name 
[ [with] [owner [=] user_name]
  [template [=] template_name]
  [encoding [=] encoding_name]
  [lc_collate [=] lc_collate_name]
  [lc_ctype [=] lc_ctype_name]
  [tablespace [=] tablespace_name]
  [connection limit [=] size] ];

一般情况下也就是用最简单的创建方式：create database dbname;

删除#

删除数据库的语法：

drop database [if exists] name;

修改#

修改数据库的语法：

alter database name [ [with] option [...] ];

可用的选项包括数据库的连接数、库名、所有者、表空间等等。

模式（Schema）#

模式可以理解为一个命名空间或者目录，不同模式下可以有相同的表、函数等对象。
不同数据库中对于Schema的定义并不一定相同，在pgsql中，一个用户一次只能访问一个数据库中的对象，如果要使用别的数据库下的对象需要切换数据库，且不同数据库的对象并不能同时访问。而Schema没有这个限制，可以同时访问数据库中不同Schema下的对象。pgsql的Schema更接近Mysql中的数据库概念。

创建Schema的方法和创建数据库基本类似：

create schema name [authorization username] [schema_element [...]];

如果不指定所有者，会默认将当前用户指定为所有者。模式的所有者也可以将Schema的权限赋予他人，比如创建和使用的权限：

grant create on schema name to username;
grant usage on schema name to username;

当然，也可以撤回这个权限：

revoke create on schema public from PUBLIC;

如果用户指定为PUBLIC，意思是所有其他用户。

另外，创建Schema时还可以嵌套地创建表和视图等在Schema下一级的对象：

create schema name 
    create table t1(id int, title text)
    create table t2(id int, content text)
    create view v1 as 
        select a.id, a.title, b.content from t1 a, f2 v where a.id = b.id;

Schema的修改和删除，也与数据库的操作类似。
通常要访问一张表时，需要指定Schema，即select * from schemaname.table1;，如果不指定的话，会默认使用public作为模式，这也是数据库在创建时会默认创建的一个公共模式。

表#

表是关系型数据库中最常用的概念。

存储属性——TOAST技术#

pgsql中使用到的TOAST（The Oversized-Attribute Storage Technique）技术：
由于pgsql的页面大小是固定的（一般8KB），并且不允许行跨越多个页面存储，因此不能直接存储很大的字段值。为了突破这个限制，大的字段值通常被压缩或切片成多个物理行存储到一张系统表中，即TOAST表。

一些基础类型不支持TOAST技术，也没有必要使用。只有变长类型的数据才会使用到TOAST。变长类型的前4字节称为长度字，后面再跟着具体内容或者是一个指针。长度字的高两位是标识，剩下30位表示实际长度，所以最大可以储存1GB的单个数据。高两位分别表示压缩标识位和行外存储标识位，如果均为零表示后面跟着的是未压缩的具体数据；如果压缩标识位置1，表示数据经过压缩；如果行外存储标识置1，表示后面存储的是一个指向TOAST表中存储实际数据位置的指针。不管怎样，长度字中保存的都是实际数据的大小。
行外存储的数据被分成多个Chunk块存储，每个Chunk块大约是一个Block的1/4（一块大小8KB，那么一个Chunk就大约2KB稍小一点），每个Chunk块作为独立的一行存储在TOAST表中，其中TOAST表中还附加了其他信息：

• chunk_id：表示该Chunk的特定TOAST值的OID；
• chunk_seq：一个表示该Chunk在对应数据中的位置的序列号；
• chunk_data：该Chunk实际使用的物理大小。

在chunk_id和chunk_seq上有一个唯一索引，提供数据的快速检索。因此指向行外存储的指针数据由四个部分组成：要查询的TOAST表的OID、chunk_id、原始数据大小和实际存储大小（如果有压缩则两者不同）。加上头部的长度字，一个行外存储的数据在原表中的总大小是20字节。
基于高两位的标识位，每个支持TOAST技术的字段有4种TOAST策略选择：

• PLAIN：避免压缩和行外存储；
• EXTENDED：允许压缩和行外存储，pgsql的默认选项，数据过大时先进行压缩，仍然过大时进行行外存储；
• EXTERNAL：只进行行外存储，不允许压缩；
• MAIN：允许压缩，但不允许行外存储，实际上如果压缩后还是存储不下时，仍会进行行外存储，但这是实在无法把数据变得更小后的最后存储手段。

修改字段的存储策略的命令如下：

alter table tablename aLter col set storage EXTERNAL;

存储属性——fillfactor#

表中有两个填充因子属性可以设置，fillfactor设置当前表的填充因子，toast.fillfactor设置当前表对应的toast表的填充因子。
填充因子是10到100的整数，表示表在使用多少空间后就不再允许填充，剩余的空间用于数据的更新。

pgsql在更新数据时，元数据不会被删除，而是在表还有空间的情况下在空闲的位置添加一条新数据，然后通过一种“Head-Only Tuple”的技术，在原数据行和新数据行之间建立一个链表，这样不需要更新索引，仍可以通过原索引加链表的方式找到最新数据。
但是“Head-Only Tuple”技术不可以跨数据块，如果新行必须插入新数据块，就无法使用这种技术，而是必须更新所有索引，这样的开销就比较大。所以对于需要频繁修改的表，可以将填充因子设为一个较小的值。

临时表#

pgsql支持两种临时表，一种是会话级的临时表，另一种是事务级的临时表。会话级临时表会存在于整个会话的生命周期中，而事务级临时表只会存在对应的事务生命周期中，事务结束她也就被销毁了。

创建会话级临时表需要加上TEMPORARY关键字：

create temporary table mytmp(id int primary key, note text);

创建事务级临时表要在上面基础上再在后面加上ON COMMIT DELETE ROWS关键字：

create temporary table mytmp(id int primary key, note text) on commit delete rows;

使用\dt命令查看表会发现，临时表会被存放在一个pg_temp开头的Schema下。

临时表后的on commit关键字有三种行为：

• on commit preserve rows：和默认不带on commit一样，表和数据会存在于整个会话的生命周期；
• on commit delete rows：表示在事务结束时删除临时表中的数据，但是并不会删除临时表；
• on commit drop：表示在事务结束时直接删除临时表。

UNLOGGED表#

UNLOGGED表使用起来和普通的表没有任何区别，只是UNLOGGED表不会有WAL日志输出，因此执行DML语句的效率会更高。但有利也有弊，坏处是由于没有WAL日志输出，UNLOGGED表就无法在主从之间同步，且如果发生异常宕机，数据可能会丢失。
创建UNLOGGED表的方式是加上关键字UNLOGGED：

create unlogged table myunlogged(id int primary key, content text);

约束#

表中的数据除了设置类型和默认值外，还可以进行一些约束，包括：

• 检查约束；
• 非空约束；
• 唯一约束；
• 主键；
• 外键约束。

检查约束#

检查约束指在设置某个字段的数值时必须使该约束的表达式计算结果为真，比如约束一个人的年龄在0~150岁之间：

create table person(name varchar(20), 
    age int CONSTRAINT check_age CHECK(age >= 0 and age <= 150), 
    sex boolean);

非空约束#

非空约束可以使用not null关键字创建，其效果等效于创建了一个检查约束：

check(col_name IS NOT NULL)

唯一约束#

唯一约束保证了表中的一个字段或一组字段相较于其他行的数据是唯一的。

create table(id int, name varchar(20), sex boolean UNIQUE(id));

主键#

主键隐含了唯一约束的要求，并且还要求对应的字段不能是空值，使用关键字primary key。

create table(id int primary key, name varchar(20));

外键约束#

外键约束是一种表与表之间的约束关系，用于约束当前表中对应字段的数值必须在外部的单个表甚至多个表中存在。
例如班级和学生之间的关系：

create table class(class_no int primary key, 
    class_name varchar(20));
create table student(student_no int primary key, 
    student_name varchar(20),
    class_no int references class(class_no));

约束了学生表中每条数据的班级号必须对应在班级表中存在。

表继承#

表继承是pgsql特有的一个特性，通过继承，子表可以获取到父表中的字段类型，并且父表的检查约束和非空约束也会被继承，但是唯一约束、主键和外键不会被继承。子表还可以继承多个父表，这样他就拥有了所有父表的字段的总和。
如果子表中的字段和父表中的字段重复，那么这个字段会被融合，该字段的约束将是父表约束和子表约束的并集。
查询父表时，会查询到子表的数据；但是查询子表并不会查询到父表中的数据。
实际上，在使用select、update、delete等命令操作父表时，会同步操作子表中的对应数据。

示例#

postgres=# create table person(name text, age int, sex boolean);
CREATE TABLE
postgres=# create table student(class_no int) inherits(person);
CREATE TABLE

创建了一张记录人员信息的表person，和一张记录学生信息的表student，student表继承自person表;

postgres=# insert into student values('张三', 15,true, 1), ('李四', 16, false, 2);
INSERT 0 2
postgres=# select * from person;
 name | age | sex
------+-----+-----
 张三 |  15 | t
 李四 |  16 | f
(2 rows)

postgres=# select * from student;
 name | age | sex | class_no
------+-----+-----+----------
 张三 |  15 | t   |        1
 李四 |  16 | f   |        2
(2 rows)

然后向学生信息表中添加了两条信息，可以看到，在person表和student表中都可以查到对应的信息。

postgres=# update student set age = 17 where name='张三';
UPDATE 1
postgres=# select * from person;
 name | age | sex
------+-----+-----
 李四 |  16 | f
 张三 |  17 | t
(2 rows)

postgres=# update person set age = 18 where name = '李四';
UPDATE 1
postgres=# select * from student;
 name | age | sex | class_no
------+-----+-----+----------
 张三 |  17 | t   |        1
 李四 |  18 | f   |        2
(2 rows)

如果修改了子表中的数据，父表中的信息也会跟着更新，同样修改父表中的信息，子表中的数据也会更新。

postgres=# insert into person values('王五', 30, true);
INSERT 0 1
postgres=# select * from person;
 name | age | sex
------+-----+-----
 王五 |  30 | t
 李四 |  16 | f
 张三 |  17 | t
(3 rows)

postgres=# select * from student;
 name | age | sex | class_no
------+-----+-----+----------
 李四 |  16 | f   |        2
 张三 |  17 | t   |        1
(2 rows)

但如果只是向父表中插入数据，在子表中是查询不到的。

如果只想查询当前表中的数据，需要添加关键字ONLY。

postgres=# select * from only person;
 name | age | sex
------+-----+-----
 王五 |  30 | t
(1 row)

分区表#

表分区就是逻辑上将一个大表分割成多个物理上的块，目的是为了提高效率。
按照简单的基本原则，当表的大小超过了数据库的物理内存大小时就需要进行分区了。
分区表的创建有两种方式：通过表继承和声明式分区，在pgsql10之前只能使用表继承的方式。
声明式分区内部也是通过表继承来实现的，是pgsql进行的一层封装，使用起来更加方便。

声明式分区#

像下面这样使用声明式分区创建一张销售订单表：

create table sale_detail (
    product_id int not null,    -- 产品编号
    price numeric(12,2),     -- 单价
    amount int not null,    -- 数量
    sale_date date not null,    -- 销售日期
    buyer varchar(20),      -- 买家名称
    buyer_contact text      -- 买家联系方式
) partition by range(sale_date);

此时是不能操作这张表的，因为还没有创建分区表的分区，可以类比于这个命令是声明了一张分区表，需要再给出实际的定义：

postgres=> create table sale_detail_y2020m08 partition of sale_detail
postgres-> for values from ('2020-01-01') to ('2020-08-01');
CREATE TABLE

使用partition of命令指定给对应的分区表加分区，表示当前的表是哪个分区表的分区，最后的for values from ... to ...指明了分区的范围。
可以使用\d+来查看一下分区表的详细信息

cwt=> \d+ sale_detail
                                            Partitioned table "public.sale_detail"
    Column     |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
---------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 product_id    | integer               |           | not null |         | plain    |             |              |
 price         | numeric(2,2)          |           |          |         | main     |             |              |
 amount        | integer               |           | not null |         | plain    |             |              |
 sale_date     | date                  |           | not null |         | plain    |             |              |
 buyer         | character varying(20) |           |          |         | extended |             |              |
 buyer_contact | text                  |           |          |         | extended |             |              |
Partition key: RANGE (sale_date)
Partitions: sale_detail_y2020m08 FOR VALUES FROM ('2020-01-01') TO ('2020-08-01')

现在向分区表中插入几条数据，然后查询出来：

postgres=> insert into sale_detail values(1, 99.99, 2, '2020-01-01', '张三', '中国'),
(2, 99.99, 2, '2020-02-02', '李四', '美国'),
(3, 99.99, 2, '2020-07-31', '王五', '俄罗斯');
INSERT 0 3
postgres=> select * from sale_detail;
 product_id | price | amount | sale_date  | buyer | buyer_contact
------------+-------+--------+------------+-------+---------------
          1 | 99.99 |      2 | 2020-01-01 | 张三  | 中国
          2 | 99.99 |      2 | 2020-02-02 | 李四  | 美国
          3 | 99.99 |      2 | 2020-07-31 | 王五  | 俄罗斯
(3 rows)

如果插入一条不在分区中的数据，就会报错：

postgres=> insert into sale_detail values(4, 99.99, 2, '2020-08-01', '赵六', '法国');
ERROR:  no partition of relation "sale_detail" found for row
DETAIL:  Partition key of the failing row contains (sale_date) = (2020-08-01).

可以看到，限制的范围应该是个左闭右开的区间。

触发器#

触发器（Trigger）是由事件自动触发执行的一种特殊存储过程，触发事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。
常用于加强数据的完整性约束和业务规则上的约束等。

创建触发器的步骤如下：

1. 先创建一个执行函数，返回类型是触发器类型；
2. 然后创建一个触发器。

创建触发器的语法：

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE|AFTER|INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM reference_table name ]
    { NOT DEFERRABLE | [DEFERRABLE] {INITIALLY IMMEDIATE | INITIALLY DEFFERRED} }
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN(condition) ]
    EXECUTE PROCEDURE function_name(arguments);

示例#

ubuntu=> create temp table student(student_no int primary key, student_name varchar(20), age int);
CREATE TABLE
ubuntu=> create temp table score(student_no int, chinese_score int, math_score int, test_date date);
CREATE TABLE
ubuntu=> create or replace function student_delete_trigger()
returns trigger as $$
begin
delete from score where student_no = old.student_no;
return old;
end;
$$
language plpgsql;
CREATE FUNCTION
ubuntu=> create trigger delete_student_trigger
after delete on student for each row execute procedure student_delete_trigger();
CREATE TRIGGER

首先创建两张表，一张学生表，一张成绩表，然后创建一个用于触发器的自定义的函数，最后创建相应的触发器。

创建自定义函数规则：

• CREATE [OR REPLACE] FUNCTION name([param TYPE, ...])：创建函数关键字，加上OR REPLACE表示会覆盖重名的函数，括号内指定函数的入参；
• RETURNS TYPE AS $variable_name$：指定返回类型，$$之间表示自定义的函数结构体名称，可以不起名，后面的$$表示函数结构体的结束；
• BEGIN：函数代码段开启；
• RETURN {variable_name|value}：返回的变量；
• END：函数代码段结束；
• LANGUAGE lan：指定语言。

OLD是触发器中的一个特殊变量，代表被删除的记录（即触发 DELETE 事件前的记录）。

然后先创建一些数据,

ubuntu=> insert into student values(1, '张三', 14), (2, '李四', 13), (3, '王二', 15);
INSERT 0 3
ubuntu=> insert into score values(1, 85, 75, date '2013-05-23'),
ubuntu-> (1, 80, 73, date '2013-09-18'),
ubuntu-> (2, 68, 83, date '2013-05-23'),
ubuntu-> (2, 73, 85, date '2013-09-18'),
ubuntu-> (3, 72, 79, date '2013-05-23'),
ubuntu-> (3, 78, 82, date '2013-05-23');
INSERT 0 6

删除掉学号为3的相关信息后查询，可以看到触发器在delete命令后自动触发了触发器，将成绩表中的相关数据也同时删除了。

delete from student where student_no = 3;
DELETE 1
ubuntu=> select * from score;
 student_no | chinese_score | math_score | test_date
------------+---------------+------------+------------
          1 |            85 |         75 | 2013-05-23
          1 |            80 |         73 | 2013-09-18
          2 |            68 |         83 | 2013-05-23
          2 |            73 |         85 | 2013-09-18
(4 rows)

语句级触发器和行级触发器#

语句级触发器是按照SQL语句来触发的，即无论一个SQL语句中执行了多少操作，只会触发一次；而行级触发器是按照数据的行变化来触发的。
值得注意的是，更新0行的操作也会查找合适的语句级触发器触发。

删除触发器#

DROP TRIGGER  [IF EXISTS] name ON table [CASCADE | RESTRICT];

后面两个可选项决定了删除的模式：

• CASCADE：连同依赖此触发器的对象一起删除；
• RESTRICT：如果存在依赖此触发器的对象，就拒绝删除，不填的话这是默认选项。

触发器函数中的特殊变量#

当把PL/pgSQL函数当作触发器函数调用时，系统会自动创建几个特殊变量：

变量	说明
NEW	为INSERT/UPDATE操作触发的行级触发器中存储新的数据行，数据类型是RECORD。语句级别的触发器中此变量未分配。
OLD	为UPDATE/DELETE操作触发的行级触发器中存储原有的数据行，数据类型是RECORD。语句级别的触发器中未分配。
TG_NAME	数据类型是name，包含实际触发的触发器名称。
TG_WHEN	内容为“BEFORE”或“AFTER”。
TG_LEVEL	内容为“ROW”或“STATEMENT”，表明是行级触发还是语句级触发。
TG_OP	内容是“INSERT”、“UPDATE”、“DELETE”、“TRUNCATE”之一的字符串，表明DML语句的类型。
TG_RELID	触发器所在表的OID。
TG_TABLE_NAME	触发器所在表的名称。
TG_TABLE_SCHEMA	触发器所在表的模式。
TG_NARGS	CREATE TRIGGER语句中赋予触发器过程参数的个数。
TG_ARGV[]	是text类型的数组，保存了CREATE TRIGGER语句中的参数。

表空间#

有时我们希望将不同的表放到不同的存储介质或文件系统下，这时就需要使用到表空间。pgsql中的表空间实际是为表指定了一个存储路径。
创建数据库时可以指定默认的表空间，创建表或索引时也可以指定特定的表空间。

创建表空间的语法如下：

CREATE TABLESPACE tablespace_name [OWNER user_name] LOCATION 'directory';

创建数据库时指定表空间，在后面加上表空间的关键字和路径，和数据库的其他属性一样，也可以通过DML语句来修改表空间。

CREATE DATABASE db_name TABLESPACE tbs_data;
ALTER DATABASE db_name TABLESPACE tbs_data;

创建表、索引或约束时都可以指定表空间：

CREATE TABLE table_name TABLESPACE tbs_data;
CREATE INDEX idx_name ON table_name tablespace tbs_data;
ALTER TABLE table_name ADD CONSTRAINT pk primary key(col_name) USING INDEX TABLESPACE tbs_data;

也可以将表从一个表空间移动到另一个表空间，在移动表的过程中，表将处于锁定状态：

ALTER TABLE table_name set tablespace pg_default;

视图#

视图就是由查询语句定义的虚拟表。使用视图一般出于以下原因：

• 可使复杂的查询易于理解和使用；
• 安全原因，视图可以隐藏一些敏感信息，比如密码；
• 把一些函数返回的结果映射成视图。

视图大多是可读的，创建视图的语法：

create [OR REPLACE] [TEMP] VIEW name [(column_name [, ...])] as query;

索引#

索引是数据库中用于快速查找数据的方法，索引中记录了表中的一列或多列值与其物理位置之间的映射关系，典型的例子就是书的目录，查找书的目录就可以快速锁定目标，然后翻到指定的页数。
建立索引的优点是加快了记录的查找和排序，但是有代价的：

• 增加了数据库的存储空间，典型的用空间换时间；
• 在插入和修改数据时需要花费更多的时间，因为索引也要跟着更新。

创建索引的语法如下：

CREATE [UNIQUE] INDEX [CONCURRENCY] [name] ON table_name [USING METHOD]
    ( {column_name|expression} [COLLATE collation] [opclass] [ASC|DESC]
    [NULLS {FIRST|LAST}] [, ...])
    [ WITH(storage_parameter = value [, ...])]
    [TABLESPACE tablespace]
    [WHERE predicate];

在创建索引时，可以正常对表进行查询，但是增、删、改操作要等索引建立完成之后才能执行。
比如在人员表中，可以根据id建立一个最简单的索引：

CREATE INDEX id_index on person(id);

对索引的操作和对表的操作基本类似。

用户和权限管理#

pgsql中使用角色的概念来管理数据库的访问权限，每个角色都是一系列权限的集合。在pgsql中用户和角色可以认为是一个概念。
用户（角色）在pgsql实例中是全局的，在同一实例的不同数据库中，看到的用户（角色）是同一个。
创建用户的语法

CREATE ROLE/USER name [WITH] [option ...];

ROLE和USER关键字都可以创建用户，略微的不同在于USER创建的用户默认是带有登录权限的，ROLE需要显式指定一下。

权限管理#

用户的权限可以分为两类：

1. 在用户创建时指定的的权限，比如是否是超级用户，是否能创建数据库，是否允许login登录等；
2. 使用GRANT和REVOKE命令管理的权限，包括能否在数据库中创建模式、能否创建临时表、能否连接某个数据库、能否在指定模式中创建对象、能否在指定表上使用DML和QML命令等。

修改第一类权限，需要使用ALTER命令，比如：

alter role_name WITH LOGIN;

第二类权限是通过GRANT和REVOKE命令来完成，有两个作用：

• 将某个角色的权限授予（撤回）给另外的角色；

grant role_name [, ...] to role_name [, ...];
revoke role_name [, ...] from role_name [, ...] [CASCADE|RESTRICT];

• 将数据库中对象的操作权限授予（撤回）给某个角色。

grant some_privileges on database_object_type object_name to role_name;
revoke some_privileges  on database_object_type object_name from role_name;

role_name如果是public，表示所有用户。

事务、并行和锁#

ACID#

• 原子性：事务的执行必须以一个整体进行，对于数据的修改，要么全都执行，要么都不执行。如果多个操作的前半部分出现错误，那么必须回滚所有的操作，让数据在逻辑上回滚到事务前的状体。
• 一致性：在事务完成时，必须使所有的数据保持一致性。
• 隔离性：事务查看数据时数据所处的状态，要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务是不会查看中间状态的数据的。
• 持久性：事务完成之后，它对于系统的影响是永久性的。就算之后系统发生故障，数据也会一直保持。

pgsql中使用多版本并发控制（MVCC）来维护数据的一致性。相对于锁定模型，优点是在MVCC下对数据的读锁请求和写锁请求并不会冲突，两者不会互相冲突。在pgsql中也提供了表和行级别的锁定语句。

保存点#

在一个比较大的事务中，可以使用SAVEPOINT设置保存点，如果保存点之后的事务执行失败了，不会将整个事务回滚，而是回滚到保存点的位置。

ubuntu=> begin;
BEGIN
Time: 0.127 ms
ubuntu=*> insert into tab1 values(1), (2);
INSERT 0 2
Time: 0.448 ms
ubuntu=*> savepoint mysavepoint01;
SAVEPOINT
Time: 0.117 ms
ubuntu=*> insert into tab2 values(1);
INSERT 0 1
Time: 0.433 ms
ubuntu=*> insert into tab2 values(1);
ERROR:  duplicate key value violates unique constraint "tab2_pkey"
DETAIL:  Key (id)=(1) already exists.
Time: 0.391 ms
ubuntu=!> select * from tab2;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
Time: 0.176 ms
ubuntu=!> rollback to savepoint mysavepoint01;
ROLLBACK
Time: 0.115 ms
ubuntu=*> select * from tab2;
 id
----
(0 rows)

Time: 0.250 ms
ubuntu=*> select * from tab1;
 id
----
  1
  2
(2 rows)

Time: 0.272 ms
ubuntu=*> insert into tab2 values(1);
INSERT 0 1
Time: 0.208 ms
ubuntu=*> insert into tab2 values(2);
INSERT 0 1
Time: 0.218 ms
ubuntu=*> commit;
COMMIT
Time: 2.014 ms
ubuntu=> select * from tab1 ;
 id
----
  1
  2
(2 rows)

Time: 2.263 ms
ubuntu=> select * from tab2 ;
 id
----
  1
  2
(2 rows)

Time: 0.224 ms

事务隔离等级#

隔离等级	脏读	不可重复读	幻读
读未提交（READ UNCOMMITTED）	可能	可能	可能
读已提交（READ COMMITTED）	不可能	可能	可能
重复读（REPEATABLE READ）	不可能	不可能	可能
串行化（SERIALIZABLE）	不可能	不可能	不可能

• 脏读：一个事务读取了另一个未提交事务写入的数据。
• 不可重复读：一个事务重复读取前面读取过的数据，发现该数据已经被另一个已提交事务修改了。
• 幻读：一个事务开始后，需要根据数据库中现有的数据做一些更新，于是重新执行了一个查询，发现查询出来数据行数因为其他最近提交的事务而发生了改变。

两阶段提交#

两阶段提交是实现分布式事务的关键。可分为如下5个步骤：

1. 各个数据库执行一些操作，但是不提交事务，然后应用程序调用事务协调器中的提交方法；
2. 事务协调器将联络事务中涉及的所有数据库，并通知它们准备提交事务，这是第一阶段提交的开始，pgsql中使用prepare transaction命令；
3. 各数据库在收到prepare transaction命令后，在确保自身状态可以在后续被要求提交事务时提交事务，被要求回滚事务时回滚事务，则返回成功；如果不能完成当前事务，就返回失败；
4. 事务协调器接收所有数据库的响应；
5. 如果事务协调器在第一阶段收到的响应中有失败，则向所有关联的数据库发送ROLLBACK PREPARED命令，回滚第一阶段提交；如果所有响应都是成功，则向各数据库发送COMMIT PREPARED命令，通知各数据库事务执行成功。