PostgreSQL AB表切换最佳实践 - 提高切换成功率，杜绝雪崩 - 珍藏级

6 minute read

背景

AB表切换经常出现在数据导入的场景中，例如每天或者每个固定周期，需要全量导入一批数据到数据库，同时被导入的数据要持续的被查询。

为了尽量避免导入影响查询，通常会使用AB表切换的方法。使用如下步骤：

1、建新表

2、全量数据导入到新表

3、在新表上面建必要的索引

4、切换新、老表名

但是，注意，由于切换表名是DDL操作，会加排它锁，与所有其他所会发送冲突。如果数据库中有长事务持有了老的表的任何锁，那么DDL会被堵塞，等待，同时会堵塞后来的任何需要持有老表锁的请求。

1、长事务，持有老表锁（共享锁）

2、切换表名，DDL，请求排他锁，等待，排它锁加入锁等待队列

3、其他会话，查询老表（请求共享锁），与锁等待队列中的DDL排他锁冲突，等待，共享锁加入锁等待队列。

以上情况，很容易造成雪崩。

《PostgreSQL 设置单条SQL的执行超时 - 防雪崩》

那么如何避免雪崩？并且在较短的时间内完成AB表切换呢？

1、杀持有新、旧表锁的会话  
  
2、在事务中切换表名  
  
开启事务  
  
设事务级锁超时  
  
对A,B表加排它锁  
  
切换A,B表  
  
重试若干次  
  
结束事务

我们可以把以上步骤函数化，提供调用

切换表1和表2的函数接口如下(暴力版)

create or replace function exchange_tab(  
  nsp name,       -- schema name  
  from_tab name,  -- 表名1  
  to_tab name,    -- 表名2  
  mid_tab name,   -- 中间表名（使用不存在的表）  
  timeout_s int,  -- 锁超时时间（秒），建议设小一点，比如1秒  
  retry int,      -- 重试几次  
  steps int default 1    -- 重试次数判断  
) returns boolean as $$  
declare  
begin  
  -- 检查中间表是否已存在，存在则报错  
  perform 1 from pg_class where relname=mid_tab and relnamespace=(select oid from pg_namespace where nspname=nsp) limit 1;  
  if found then  
    raise notice 'you should use not exists table for exchange.';  
    return false;  
  end if;  
    
  -- 如果重试次数达到，还没有切换成功，则返回切换不成功  
  if steps >= retry then  
    return false;  
  end if;  
    
  -- 设置锁超时  
  execute format('set local lock_timeout=%L;', timeout_s||'s');  
    
  -- 杀死持有 表1，表2 锁的会话  
  -- 如果是普通用户，只能杀死同名用户下的其他会话，所以如果持锁的是其他用户，则需要使用超级用户才能杀死  
  perform pg_terminate_backend(pid) from pg_stat_activity where   
    pid in (select pid from pg_locks where   
              database=(select oid from pg_database where datname=current_database())   
              and relation in ((nsp||'.'||from_tab)::regclass, (nsp||'.'||to_tab)::regclass)  
           )   
    and pid<>pg_backend_pid();  
    
  -- 对表1，表2 加排他锁  
  execute format('lock table %I.%I in ACCESS EXCLUSIVE mode;', nsp, from_tab);  
  execute format('lock table %I.%I in ACCESS EXCLUSIVE mode;', nsp, to_tab);  
    
  -- 切换表1，表2  
  execute format('alter table %I.%I rename to %I;', nsp, to_tab, mid_tab);  
  execute format('alter table %I.%I rename to %I;', nsp, from_tab, to_tab);  
  execute format('alter table %I.%I rename to %I;', nsp, mid_tab, from_tab);  
    
  -- 返回切换成功  
  return true;  
  
  -- 任何一步失败（比如锁超时异常），则重试  
  exception when others then  
    -- 重试次数显示  
    raise notice 'retry: %', steps;  
      
    -- 如果重试次数达到，还没有切换成功，则返回切换不成功  
    if steps >= retry then  
      return false;  
    else  
      -- 递归调用，重试，传入参数重试次数+1.    
      return exchange_tab(nsp, from_tab, to_tab, mid_tab, timeout_s, retry, steps+1);  
    end if;  
end;  
$$ language plpgsql strict;  

例子

1、创建两张表，用来切换

create table abc(id int);  
create table abc_tmp(id int);  

2、分别写入100,1000条记录

insert into abc select generate_series(1,100);  
insert into abc_tmp select generate_series(1,1000);  

3、开启一个事务，查询ABC表，持有共享锁

begin;  
select * from abc limit 1;  
  id    
------  
    1  

4、在另一个会话中切换表abc, abc_tmp。瞬间切换成功

select * from exchange_tab(  
  'public',  
  'abc',  
  'abc_tmp',  
  'abc_notexists',  
  1,  
  10  
);  
  
  
 exchange_tab   
--------------  
 t  
(1 row)  

5、查询abc表的会话已被杀

postgres=# select * from abc;  
FATAL:  terminating connection due to administrator command  
server closed the connection unexpectedly  
        This probably means the server terminated abnormally  
        before or while processing the request.  
The connection to the server was lost. Attempting reset: Succeeded.  

6、检查是否切换成功

postgres=# select * from exchange_tab(  
  'public',  
  'abc',  
  'abc_tmp',  
  'abc_notexists',  
  1,  
  10  
);  
 exchange_tab   
--------------  
 t  
(1 row)  
  
postgres=# select count(*) from abc;  
 count   
-------  
  1000  
(1 row)  
  
postgres=# select count(*) from abc_tmp;  
 count   
-------  
   100  
(1 row)  
  
postgres=# select * from exchange_tab(  
  'public',  
  'abc',  
  'abc_tmp',  
  'abc_notexists',  
  1,  
  10  
);  
 exchange_tab   
--------------  
 t  
(1 row)  
  
postgres=# select count(*) from abc;  
 count   
-------  
   100  
(1 row)  
  
postgres=# select count(*) from abc_tmp;  
 count   
-------  
  1000  
(1 row)  

切换表1和表2的函数接口如下(可控版)

1、增加一个参数，是否杀持锁会话（暴力）。

2、增加一个SLEEP参数，在重试前，等待若干秒。

create or replace function exchange_tab( 
  nsp name,       -- schema name  
  from_tab name,  -- 表名1  
  to_tab name,    -- 表名2  
  mid_tab name,   -- 中间表名（使用不存在的表）  
  timeout_s int,  -- 锁超时时间（秒），建议设小一点，比如1秒  
  retry int,      -- 重试几次  
  kill boolean default false,  -- 是否执行terminate backend
  sleepts int default 1,  -- 重试前睡眠多少秒
  steps int default 1    -- 重试次数判断  
) returns boolean as $$  
declare  
begin  
  -- 检查中间表是否已存在，存在则报错  
  perform 1 from pg_class where relname=mid_tab and relnamespace=(select oid from pg_namespace where nspname=nsp) limit 1;  
  if found then  
    raise notice 'you should use not exists table for exchange.';  
    return false;  
  end if;  
    
  -- 如果重试次数达到，还没有切换成功，则返回切换不成功  
  if steps >= retry then  
    return false;  
  end if;  
    
  -- 设置锁超时  
  execute format('set local lock_timeout=%L;', timeout_s||'s');  
  
  if kill then
    -- 杀死持有 表1，表2 锁的会话  
    -- 如果是普通用户，只能杀死同名用户下的其他会话，所以如果持锁的是其他用户，则需要使用超级用户才能杀死  
    perform pg_terminate_backend(pid) from pg_stat_activity where   
      pid in (select pid from pg_locks where   
                database=(select oid from pg_database where datname=current_database())   
                and relation in ((nsp||'.'||from_tab)::regclass, (nsp||'.'||to_tab)::regclass)  
             )   
      and pid<>pg_backend_pid();  
  end if;
  
  -- 对表1，表2 加排他锁  
  execute format('lock table %I.%I in ACCESS EXCLUSIVE mode;', nsp, from_tab);  
  execute format('lock table %I.%I in ACCESS EXCLUSIVE mode;', nsp, to_tab);  
    
  -- 切换表1，表2  
  execute format('alter table %I.%I rename to %I;', nsp, to_tab, mid_tab);  
  execute format('alter table %I.%I rename to %I;', nsp, from_tab, to_tab);  
  execute format('alter table %I.%I rename to %I;', nsp, mid_tab, from_tab);  
    
  -- 返回切换成功  
  return true;  
  
  -- 任何一步失败（比如锁超时异常），则重试  
  exception when others then  
    -- 重试次数显示  
    raise notice 'retry: %', steps;  
      
    -- 睡眠
    perform pg_sleep(sleepts);
    
    -- 如果重试次数达到，还没有切换成功，则返回切换不成功  
    if steps >= retry then  
      return false;  
    else  
      -- 递归调用，重试，传入参数重试次数+1.    
      return exchange_tab(nsp, from_tab, to_tab, mid_tab, timeout_s, retry, kill, sleepts, steps+1);  
    end if;  
end;  
$$ language plpgsql strict;  

ab表切换时，如果其他SQL被锁等待堵塞，堵塞释放后到底操作的是a表还是b表？

插入操作

1、建表

create table abc(id int);  
  
create table abc_tmp(id int);  

2、插入测试，返回当前插入的是哪个表

postgres=# insert into abc values(1) returning *,tableoid,tableoid::regclass;  
 id | tableoid | tableoid   
----+----------+----------  
  1 |    26746 | abc  
(1 row)  
  
INSERT 0 1  

3、开启切换流程，先锁住2个要被切换的表

postgres=# begin;  
BEGIN  
postgres=# lock table abc in access exclusive mode ;  
LOCK TABLE  
postgres=# lock table abc_tmp in access exclusive mode ;  
LOCK TABLE  

4、开启另一个会话，插入，并返回当前插入的是哪个表？

postgres=# insert into abc values(1) returning *,tableoid,tableoid::regclass;

堵塞，等待

5、切换流程继续，切换AB表

postgres=# alter table abc rename to abc_mid;  
ALTER TABLE  
postgres=# alter table abc_tmp rename to abc;  
ALTER TABLE  
postgres=# alter table abc_mid rename to abc_tmp;  
ALTER TABLE  
postgres=# end;  
COMMIT  

6、堵塞结束，到底插入了哪个表？

 id | tableoid | tableoid   
----+----------+----------  
  1 |    26743 | abc  
(1 row)  
  
INSERT 0 1  

结论：插入了切换后的ABC表

更新操作

接上面的表

truncate abc;  
truncate abc_tmp;  
  
insert into abc values (1);  
insert into abc_tmp values (1);  

1、更新测试，返回当前插入的是哪个表

update abc set id=2 returning *,ctid,tableoid,tableoid::regclass;  
  
 id | ctid  | tableoid | tableoid   
----+-------+----------+----------  
  2 | (0,2) |    26743 | abc  
(1 row)  
  
UPDATE 1  

2、开启切换流程，先锁住2个要被切换的表

postgres=# begin;  
BEGIN  
postgres=# lock table abc in access exclusive mode ;  
LOCK TABLE  
postgres=# lock table abc_tmp in access exclusive mode ;  
LOCK TABLE  

3、开启另一个会话，插入，并返回当前插入的是哪个表？

postgres=# update abc set id=2 returning *,ctid,tableoid,tableoid::regclass;

堵塞，等待

4、切换流程继续，切换AB表

postgres=# alter table abc rename to abc_mid;  
ALTER TABLE  
postgres=# alter table abc_tmp rename to abc;  
ALTER TABLE  
postgres=# alter table abc_mid rename to abc_tmp;  
ALTER TABLE  
postgres=# end;  
COMMIT  

5、堵塞结束，到底更新了哪个表？

 id | ctid  | tableoid | tableoid   
----+-------+----------+----------  
  2 | (0,2) |    26746 | abc  
(1 row)  
  
UPDATE 1  

结论：更新了切换后的ABC表

维系同样索引、约束、默认值的AB表切换

业务上通常会使用中间表，加载全量数据后，再与业务表切换。除了数据，还需要考虑索引、约束、默认值等与业务表保持一致。

例子

1、交换表UDF(包括索引)

create or replace function exchange_table(v_nsp name, v_old_tbl name, v_new_tbl name) returns void as $$  
declare  
  idx_def text[];  
  sql text;  
begin  
  set lock_timeout ='5s';  
  select array_agg(regexp_replace(indexdef,'INDEX (.+) ON (.+) ','INDEX i'||to_char(clock_timestamp(),'yyyymmddhh24miss')||'_'||rn||' ON '||quote_ident(v_nsp)||'.'||quote_ident(v_new_tbl))) 
    into idx_def 
    from (select *,row_number() over() rn from pg_indexes where schemaname=v_nsp and tablename=v_old_tbl) t;  

  foreach sql in array idx_def   
  loop  
    execute sql;     
  end loop;  
  -- 如果索引非常多，可以异步并行创建，所有索引创建完成后再切换表  
  -- PG 11版本，不需要异步创建，因为单个索引的创建已经支持并行  
  execute format('drop table %I.%I', v_nsp, v_old_tbl);  
  execute format('alter table %I.%I rename to %I', v_nsp, v_new_tbl, v_old_tbl);  
end;  
$$ language plpgsql strict;  

PG11 并行创建索引例子

《PostgreSQL 快速给指定表每个字段创建索引》

异步并行调用参考

《阿里云RDS PostgreSQL OSS 外部表实践 - (dblink异步调用封装并行) 从OSS并行导入数据》

《在PostgreSQL中跑后台长任务的方法 - 使用dblink异步接口》

以上UDF，结合《PostgreSQL dblink异步调用实践,跑并行多任务 - 例如开N个并行后台任务创建索引, 开N个后台任务跑若干SQL》改成并行创建

create or replace function exchange_table(v_nsp name, v_old_tbl name, v_new_tbl name) returns void as $$  
declare  
  idx_def text[];  
  sql text;  
  sqls text[];      
begin  
  
  set lock_timeout ='5s';  
  select array_agg(regexp_replace(indexdef,'INDEX (.+) ON (.+) ','INDEX i'||to_char(clock_timestamp(),'yyyymmddhh24miss')||'_'||rn||' ON '||quote_ident(v_nsp)||'.'||quote_ident(v_new_tbl))) 
    into idx_def 
    from (select *,row_number() over() rn from pg_indexes where schemaname=v_nsp and tablename=v_old_tbl) t;  
  foreach sql in array idx_def   
  loop  
    sqls := array_append(sqls, sql);  
  end loop;  
  -- 如果索引非常多，可以异步并行创建，所有索引创建完成后再切换表  
  -- PG 11版本，不需要异步创建，因为单个索引的创建已经支持并行  

  -- 并行
  perform run_sqls_parallel(  
    16,   -- 并行度  
    sqls  -- 执行index SQL数组  
  );  

  -- 切换表名
  execute format('drop table %I.%I', v_nsp, v_old_tbl);  
  execute format('alter table %I.%I rename to %I', v_nsp, v_new_tbl, v_old_tbl);  
end;  
$$ language plpgsql strict;  

2、DEMO

业务表\索引

create table tbl(id int primary key, info text not null, crt_time timestamp default now());  
create index idx_tbl_1 on tbl (crt_time);  
insert into tbl select generate_series(1,100),md5(random()::text);  

临时表（无索引，只包含与业务表一样的约束）

create table tmp (like tbl including CONSTRAINTS including DEFAULTS);

加载数据到临时表(demo)

insert into tmp select generate_series(1,200),md5(random()::text);

临时表切换为业务表，并加与业务表相同的索引

select exchange_table('public', 'tbl', 'tmp');  
  
postgres=# select count(*) from tbl;  
 count   
-------  
   200  
(1 row)  
  
postgres=# select * from tmp;  
ERROR:  relation "tmp" does not exist  
LINE 1: select * from tmp;  
                      ^  

postgres=# \d tbl  
                           Table "public.tbl"  
  Column  |            Type             | Collation | Nullable | Default   
----------+-----------------------------+-----------+----------+---------  
 id       | integer                     |           | not null |   
 info     | text                        |           | not null |   
 crt_time | timestamp without time zone |           |          | now()  
Indexes:  
    "i_20180827222503_1" UNIQUE, btree (id)  
    "i_20180827222503_2" btree (crt_time)  

小结

使用本文提到的方法对AB表进行切换，可以完全杜绝雪崩。同时，如果有正在使用AB表的会话，会被回退，由于切换AB表仅涉及元数据的更新，所以切换很快，影响很小。

注意，使用切换表的方法，实际上有一个问题是依赖关系的问题，如果依赖关系复杂,RENMAE的方式，需要注意依赖关系的切换，包括主外键，继承，分区等。

所以，对于依赖关系复杂的情况，更好的方法是类似pg_repack的方法，切换表的filenode(即数据文件映射关系，当然还需要注意TOAST也要切换)，而不是RENAME TABLE。

参考

《PostgreSQL 设置单条SQL的执行超时 - 防雪崩》

《PostgreSQL 锁等待监控珍藏级SQL - 谁堵塞了谁》

digoal’s 大量PostgreSQL文章入口

Twitter Facebook Google+ LinkedIn

Digoal.zhou

PostgreSQL AB表切换最佳实践 - 提高切换成功率，杜绝雪崩 - 珍藏级

背景

切换表1和表2的函数接口如下(暴力版)

例子

切换表1和表2的函数接口如下(可控版)

ab表切换时，如果其他SQL被锁等待堵塞，堵塞释放后到底操作的是a表还是b表？

插入操作

更新操作

维系同样索引、约束、默认值的AB表切换

例子

小结

参考

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

PostgreSQL plpgsql 存储过程、函数 - 状态、异常变量打印、异常捕获… - GET [STACKED] DIAGNOSTICS

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法