PostgreSQL中一种特殊场景下主键更新冲突的解决办法

有个场景需要更新主键字段，把主键字段都增加1，这样就有可能导致更新失败，下面看几个实例：

--构建测试表，表主键整型，按递增的次序插入数据
postgres=# CREATE TABLE test(id INT,CONSTRAINT pk_test PRIMARY KEY (id));
CREATE TABLE
postgres=# INSERT INTO test(id) SELECT generate_series(1,10);
INSERT 0 10
postgres=# select * from test;
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)
--数据在更新时，首先拿到表块的第一个元组id=1，此时把id+1即为2，2已存在数据表且id为主键，导致主键冲突更新失败
postgres=# UPDATE test SET id = id + 1;
ERROR:  duplicate key value violates unique constraint "pk_test"
DETAIL:  Key (id)=(2) already exists.

--删除测试表数据，按递减的次序插入数据
postgres=# TRUNCATE TABLE test;
TRUNCATE TABLE
postgres=# INSERT INTO TEST(ID) SELECT generate_series(10,1,-1);
INSERT 0 10
postgres=# SELECT * FROM test;
 id 
----
 10
  9
  8
  7
  6
  5
  4
  3
  2
  1
(10 rows)
--数据在更新时，首先拿到表块的第一个元组id=10，此时把id+1即为11，11在表中不存在可更新，以此类推所有行都能正常更新，更新语句运行成功
postgres=# UPDATE test SET id = id + 1;
UPDATE 10
postgres=# SELECT * FROM test;
 id 
----
 11
 10
  9
  8
  7
  6
  5
  4
  3
  2
(10 rows)

--删除测试表数据，随机插入数据，这种情况符合实际场景，数据行的增删改会导致主键和块元组并非正相关，从而导致主键更新失败
postgres=# TRUNCATE TABLE test;
TRUNCATE TABLE
postgres=# INSERT INTO test(id) SELECT * FROM (SELECT generate_series(1,10) AS id) AS t ORDER BY random();
INSERT 0 10
postgres=# SELECT * FROM test;
 id 
----
  4
 10
  9
  8
  5
  7
  2
  1
  3
  6
(10 rows)
--这里第一个返回的行是id=4，此时把id+1即为5，5已存在数据表且id为主键，导致主键冲突更新失败
postgres=# UPDATE test SET id = id + 1;
ERROR:  duplicate key value violates unique constraint "pk_test"
DETAIL:  Key (id)=(5) already exists.


--下面提供几种解决办法：
--1、删除主键，更新完了再加上
postgres=# BEGIN;
BEGIN
postgres=*# ALTER TABLE test DROP CONSTRAINT pk_test;
ALTER TABLE
postgres=*# UPDATE test SET id = id + 1;
UPDATE 10
postgres=*# ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (id);
ALTER TABLE
postgres=*# COMMIT;
COMMIT
postgres=# SELECT * FROM test;
 id 
----
  5
 11
 10
  9
  6
  8
  3
  2
  4
  7
(10 rows)

--2、使用两次更新
postgres=# BEGIN;
BEGIN
--首先可以找到主键id中目前最大的值，增加的量比最大的值还大，这样就不会导致主键冲突，下面先对主键增加101，然后再做一次更新，主键减去100
postgres=*# UPDATE test SET id = id + 101;
UPDATE 10
postgres=*# SELECT * FROM test;
 id  
-----
 106
 112
 111
 110
 107
 109
 104
 103
 105
 108
(10 rows)
--再做一次更新，主键减去100，相当于以前的id+1
postgres=*# UPDATE test SET id = id - 100;
UPDATE 10
postgres=*# SELECT * FROM test;
 id 
----
  6
 12
 11
 10
  7
  9
  4
  3
  5
  8
(10 rows)
postgres=*# COMMIT;
COMMIT

--3、把表按照主键先倒序排，然后再去更新，这样就不会产生主键冲突
--先分析下这个执行计划
postgres=# EXPLAIN
postgres-# UPDATE test a
postgres-#    SET id = a.id + 1 
postgres-#   FROM (SELECT id FROM test ORDER BY id DESC) b
postgres-#  WHERE a.id = b.id;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Update on test a  (cost=2.49..2.68 rows=10 width=38)
   ->  Hash Join  (cost=2.49..2.68 rows=10 width=38)  --散列连接，通过排好序的外表b进行探测连接
         Hash Cond: (b.id = a.id)
         ->  Subquery Scan on b  (cost=1.27..1.39 rows=10 width=32)
               ->  Sort  (cost=1.27..1.29 rows=10 width=4)
                     Sort Key: test.id DESC   --由于子查询中有ORDER BY，避免子查询上拉
                     ->  Seq Scan on test  (cost=0.00..1.10 rows=10 width=4)
         ->  Hash  (cost=1.10..1.10 rows=10 width=10)   --构建内表a的处理批次
               ->  Seq Scan on test a  (cost=0.00..1.10 rows=10 width=10)
(9 rows)

postgres=# UPDATE test a
postgres-#    SET id = a.id + 1 
postgres-#   FROM (SELECT id FROM test ORDER BY id DESC) b
postgres-#  WHERE a.id = b.id;
UPDATE 10
postgres=# SELECT * FROM test;
 id 
----
 13
 12
 11
 10
  9
  8
  7
  6
  5
  4
(10 rows)
复制