136
京東網上商城
Greenplum數據增量導入的唯一值自增處理
阿裏雲的Greenplum（以下簡稱GP）已經公測了一段時間，陸續接到很多用戶的反饋。其中一些使用上的問題比較有趣，在這裏與大家分享一下。
其中一個case是字段的唯一鍵和自增值問題。在導入GP之前，某id字段已經保證了唯一性，但在此次導入之後，可能會有更多的導入，這個時候希望GP在原來最大id值的基礎上自增。
GP是在PostgreSQL（以下簡稱PG）上開發而來，其操作基本沿用。在PG上，實現自增的方法是通過serial：
postgres=> create table tuniq(id serial, name text);
CREATE TABLE
postgres=> insert into tuniq (name) values('zero');
INSERT 0 1
postgres=> insert into tuniq (name) values('second');
INSERT 0 1
postgres=>
postgres=>
postgres=> select * from tuniq;
id | name
----+--------
1 | zero
2 | second
(2 rows)
這裏的serial，並不是一個數據類型，而是通過建立一個全局序列**“tuniq_id_seq”**（表名_字段名_seq）實現的，每次插入的時候會從這個seq中取值作為字段的默認值，從而做到自增。
那麼，如果你執行下麵的語句會怎麼樣？
postgres=> insert into tuniq (id, name) values(1, 'second');
在id沒有唯一約束的情況下，這是可以執行成功的。原因是id字段並沒有加任何約束，而serial隻是簡單的從sequence給id賦值而已。這樣就帶來一個問題：
postgres=> select * from tuniq;
id | name
----+--------
1 | zero
2 | second
1 | second
(3 rows)
如果在這個字段上有唯一約束的話，那麼開始的時候導入包括id在內的數據，之後執行不包括id的插入的時候，就會去從sequence取值。而這個時候，因為sequence的當前最新值尚未更新，所以可能會出現與已導入數據衝突的情況，如：
postgres=> create table tuniq(id serial unique, name text);
CREATE TABLE
postgres=>
postgres=>
postgres=> insert into tuniq values(0, 'zero');
INSERT 0 1
postgres=> insert into tuniq values(1, 'first'); INSERT 0 1
postgres=> select * from tuniq; id | name
----+-------
0 | 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 0 1
postgres=> select * from tuniq;
id | name
----+--------
0 | zero
1 | first
2 | second
(3 rows)
更詳細的用法和解釋參考這裏。
那如果是開始加了唯一鍵約束，但沒有采用serial該如何實現字段的繼續遞增？
正所謂：**知其然，知其所以然**。serial的原理，不過是從sequence取值作為字段的默認值而已。那如果想要做到類似的方式，用同樣的方式做就好了。
讓我們模擬一下這個場景：
postgres=> create table tuniq(id int unique, name text);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "tuniq_id_key" for table "tuniq"
CREATE TABLE
postgres=>
postgres=> insert into tuniq(id, name) values(0, 'zero');
INSERT 0 1
postgres=> insert into tuniq(id, name) values(1, 'first');
INSERT 0 1
postgres=> insert into tuniq(id, name) values(2, 'second');
INSERT 0 1
postgres=>
postgres=> select * from tuniq ;
id | name
----+--------
1 | first
0 | zero
2 | second
(3 rows)
這個數據已經導入完成，後續想讓id鍵自增的話，需要先建一個sequence，並更新到最新的值:
postgres=> create sequence tuniq_id_seq;
CREATE SEQUENCE
postgres=>
postgres=> select setval('tuniq_id_seq', max(id)) from tuniq;
setval
--------
2
(1 row)
postgres=>
postgres=> ALTER TABLE tuniq ALTER id set default nextval('tuniq_id_seq'::regclass);
ALTER TABLE
這個時候，就可以把sequence的值作為tuniq表id字段的默認值了，如：
postgres=> insert into tuniq(name) values('third');
INSERT 0 1
postgres=>
postgres=>
postgres=> select * from tuniq;
id | name
----+--------
1 | first
3 | third
0 | zero
2 | second
(4 rows)
從以上我們可以看出，用PG/GP的自增需要注意：
- serial是通過sequence設置字段的**默認值**
- 可以考慮加上唯一約束，防止主動插入該字段的值，破壞該字段值的自增序和唯一性（如果業務關心的話）
最後更新：2017-05-23 15:02:48