Greenplum主键和序列的使用

2022-06-08 00:00:00 序列字段类型主键约束

主键和键限制

主键约束是约束和非空约束的组合。GP数据库为每个unique或primary key约束自动创建unique索引来强制执行性。因此，无需为主键列显式创建索引。

Appendonly追加表不支持主键和键约束

在追加优化表上不允许使用unique和primary key约束，因为由约束创建的unique索引在追加优化表上不允许

随机分布的表不支持主键和键约束

GP表如果需要建键，表必须是哈希分布的（不是随机分布）。即随机分布不支持主键（约束+非空约束）和键，因为随机分布保证不了整体数据的性。

有约束未指定分布键的表会将键作为分布键

如果表中含有键，但是没有指定分布键，则GP会默认将键作为表的分布键

同一个表不支持同时存在两个约束的字段

GP数据库不支持同时存在两个的字段(复合主键除外)。

分布键必须是主键列约束的左子集并且列的顺序是正确的

例如，如果主键是（a，b，c），该分布键只能是以下集合之一：（a），（a，b），或（a，b，c）

不能直接修改主键类型

直接修改主键类型报错如下：ERROR: relation "XXXXX" already exists

正确的修改方式先drop主键类型，修改类型后，再进行主键的重新创建

postgres=# ALTER TABLE public.t_hash_3 drop CONSTRAINT t_hash_3_pkey;
ALTER TABLE
postgres=# alter table public.t_hash_3 alter column id TYPE bigint;
ALTER TABLE
postgres=# ALTER TABLE public.t_hash_3 ADD CONSTRAINT t_hash_3_pkey primary key(id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t_hash_3_pkey" for table "t_hash_3"
ALTER TABLE

如果报约束名不存在时，可以去数据字典（pg_class和pg_constraint）中查找实际的约束名

postgres=# select oid from pg_class where relname='t_hash_3';
  oid
-------
 73870
(1 row)

postgres=# select conname from pg_constraint where conrelid=73870;
    conname
---------------
 t_hash_3_pkey
(1 row)

serial类型

如果业务需要实现自增，我们会推荐serial类型。但是某业务反馈该类型并不能达到自增的效果，因此好好研究了下该类型的用法。

GP沿用postgresql序列号（SERIAL）类型，postgresql中包括smallserial（smallint,short）,serial(int)和bigserial(bigint,long long int)三种，本文测试使用的gp5.x版本只支持serial。

序列号类型其实不是真正的类型，当声明一个字段为序列号类型时其实是通过建立一个全局序列 表名_字段名_seq 实现的，INSERT时如果没有给该字段赋值会默认获取对应序列的下一个值。

添加自增字段的两种方式

通过serial自动创建序列

postgres=# create table tuniq(id serial, name text);
NOTICE:  CREATE TABLE will create implicit sequence "tuniq_id_seq" for serial column "tuniq.id"
NOTICE:  Using default RANDOM distribution since no distribution was specified.
HINT:  Consider including the 'DISTRIBUTED BY' clause to determine the distribution of rows.
CREATE TABLE
postgres=# \d tuniq
                         Table "public.tuniq"
 Column |  Type   |                     Modifiers
--------+---------+----------------------------------------------------
 id     | integer | not null default nextval('tuniq_id_seq'::regclass)
 name   | text    |
Distributed randomly

通过sequence自定义序列

除了上述通过使用serial类型来自动创建序列，我们也可以先创建一个序列，然后将表的某字段默认值设为该序列的下一个序列值。

通过\h查看创建序列的语法

postgres=#  \h create sequence
Command:     CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table.column | NONE } ]

含义如下

关键字	含义
INCREMENT BY	每次序列增加（或减少）的步长
MINVALUE	序列小值，NO MINVALUE表示没有小值
MAXVALUE	序列大值，NO MAXVALUE表示没有大值
START WITH	以什么序列值开始
CYCLE	序列是否循环使用
OWNED BY	可以直接指定一个表的字段，也可以不指定

postgres=# create sequence sql_tbl_serial2_a;
CREATE SEQUENCE
postgres=# create table tbl_serial2(a int not null default nextval('sql_tbl_serial2_a'),b varchar(2));
NOTICE:  Using default RANDOM distribution since no distribution was specified.
HINT:  Consider including the 'DISTRIBUTED BY' clause to determine the distribution of rows.
CREATE TABLE
postgres=# \d+ tbl_serial2
                                            Table "public.tbl_serial2"
 Column |         Type         |                        Modifiers                        | Storage  | Description
--------+----------------------+---------------------------------------------------------+----------+-------------
 a      | integer              | not null default nextval('sql_tbl_serial2_a'::regclass) | plain    |
 b      | character varying(2) |                                                         | extended |
Has OIDs: no
Distributed randomly
--或者在已经好的表添加默认值约束
postgres=> ALTER TABLE tbl_serial2  ALTER id  set default nextval('sql_tbl_serial2_a'::regclass);
ALTER TABLE

查询自增字段定义信息

tuniq_id_seq为通过serial自动创建的序列。可通过\d 序列名，可直接查看到序列的定义

postgres=#  \d tuniq_id_seq
        Sequence "public.tuniq_id_seq"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | tuniq_id_seq
 last_value    | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 
 is_cycled     | boolean | f
 is_called     | boolean | f

serial范围

通过\d查看到序列的大值为9223372036854775807，然而我们测试，发现由于id字段本身是int类型，所以当超出int范围会报错

postgres=# insert into tuniq values(9223372036854775807, 'zero');
ERROR:  integer out of range
postgres=# insert into tuniq values(9223372036854775806, 'zero');
ERROR:  integer out of range

查找资料，验证serial类型的范围如下：

类型	字节数	含义	范围
serial	4字节	自增整数	1到+2147483647
bigserial	8字节	大范围的自增整数	1到9223372036854775807

如何增大自增字段的大值

由上可知，通过serial类型，大值只能到2147483647。如果该范围已经不够用。可通过先建序列，再与声明为bigint的字段建立约束即可。如下

postgres=#  create sequence sql_tbl_serial2_a increment by 1 minvalue 1 maxvalue 9223372036854775807 start with 1;
CREATE SEQUENCE
postgres=# create table tbl_serial2(a bigint not null default nextval('sql_tbl_serial2_a'),b varchar(2));
NOTICE:  Using default RANDOM distribution since no distribution was specified.
HINT:  Consider including the 'DISTRIBUTED BY' clause to determine the distribution of rows.
CREATE TABLE
postgres=# \d+ tbl_serial2
                                            Table "public.tbl_serial2"
 Column |         Type         |                        Modifiers                        | Storage  | Description
--------+----------------------+---------------------------------------------------------+----------+-------------
 a      | bigint               | not null default nextval('sql_tbl_serial2_a'::regclass) | plain    |
 b      | character varying(2) |                                                         | extended |
Has OIDs: no
Distributed randomly
postgres=# insert into  tbl_serial2 values(9223372036854775807, 'ze');
INSERT  1

自增字段往往伴随约束

如果不建立约束，会出现如下问题。

postgres=> create table tuniq(id serial, name text);
CREATE TABLE
postgres=> insert into tuniq (name) values('zero');
INSERT  1
postgres=> insert into tuniq (name) values('second');
INSERT  1
postgres=>
postgres=>
postgres=> select * from tuniq;
 id |  name
----+--------
  1 | zero
  2 | second
(2 rows)
--继续在自增字段插入一样的值，不会报错。
postgres=> insert into tuniq (id, name) values(1, 'second');
--出现自增字段重复，与业务预期不符
postgres=> select * from tuniq;
 id |  name
----+--------
  1 | zero
  2 | second
  1 | second
(3 rows)

该问题可通过在自增字段建立键约束解决。

自增字段为什么没有生效

如果有时候自增字段由oltp系统同步过来。有时候又需要在GP内部执行不包括自增字段的数据插入情况。

这个时候，因为sequence的当前新值尚未更新，所以可能会出现与已导入数据冲突的情况，如：

postgres=> create table tuniq(id serial unique, name text);
CREATE TABLE
postgres=> insert into tuniq values(, 'zero');
INSERT  1
postgres=> insert into tuniq values(1, 'first');
INSERT  1
postgres=> select * from tuniq;
id | name
----+-------
   | zero
  1 | first
(2 rows)
postgres=> insert into tuniq (name) values('second');
ERROR:  duplicate key value violates unique constraint "tuniq_id_key"
DETAIL:  Key (id)=(1) already exists.

此时我们需要先将序列值进行更新，才可正常运行。如下：

postgres=> select setval('tuniq_id_seq', max(id)) from tuniq;
 setval
--------
      1
(1 row)
postgres=> insert into tuniq (name) values('second');
INSERT  1
postgres=> select * from tuniq;
 id |  name
----+--------
   | zero
  1 | first
  2 | second
(3 rows)

序列的函数使用

函数	返回类型	描述
currval(regclass)	bigint	获取指定序列近一次使用netxval后的数值，如果没有使用nextval而直接使用currval会出错。
lastval()	bigint	返回近一次用 nextval 获取的任意序列的数值
nextval(regclass)	bigint	递增序列并返回新值
setval(regclass,bigint)	bigint	设置序列的当前数值
setval(regclass,bigint ,boolean)	bigint	设置序列的当前数值以及 is_called 标志，如果为true则立即生效，如果为false，则调用一次nextval后才会生效。

总结三点

serial是通过sequence设置字段的默认值
可以考虑加上约束，防止主动插入该字段的值，破坏该字段值的自增序和性（如果业务关心的话）
如果自增字段需要从外部同步，又需要在内部产生的场景，需要及时更新序列的新值。

参考

http://www.dbdream.com.cn/ https://gp-docs-cn.github.io/docs/refguide/sqlcommands/CREATE_TABLE.html https://yq.aliyun.com/articles/64314 https://blog.csdn.net/u012352957/article/details/84577468

相关文章