PGSQL 单sql多insert例子

-- INIT database
CREATE TABLE msg (
  msgId char(32) PRIMARY KEY, -- ulid
  uid bigint not null,
  con text not null
);

create table seum(
  clientId int,
  uid bigint not null,
  msgId char(32) not null,
  co int not null default 0,
  primary key(uid, clientId)
);

create table reum(
  serverId bigint,
  uid bigint not null,
  msgId char(32) not null,
  primary key(uid, serverId)
);

create table reumId(
  uid bigint primary key,
  rid int not null default 0
);

-- just 4 row lock
-- 可能会出现死锁

with
  -- 1.保存im发送信息
  msgInsert as (insert into msg values ('1', 1, '{}')),
  -- 2 从用户接收id表中的id进行自增1
  reumIdInsert as (insert into reumId values(2, 1) on conflict (uid) 
            do update set rid = reumId.rid + 1 returning rid),
  -- 3 用户接收表入库
  reumInsert as (insert into reum values ((select rid from reumIdInsert limit 1),
                          2,'1')),
  -- 4 用户发送表入库，如果用户clientId重复则进行重发次数+1
  seumInsert as (insert into seum values (1, 1, '1',0) on conflict (uid, clientId) 
            do update set co = seum.co + 1 returning co)
  -- 5 server获得重发次数，如果重发次数大于0则事务回滚。
select * from seumInsert;