如何避免PostgreSQL中CREATE TABLE IF NOT EXISTS因duplicate_table报错？

问题原因 #

你遇到的问题源于CREATE TABLE IF NOT EXISTS在并发场景下的竞态窗口：客户端完成“分区是否存在”的检查后，到实际执行创建操作的间隙中，其他客户端可能已经创建了该分区。PostgreSQL的IF NOT EXISTS仅做语句级检查，并非原子性操作，因此无法完全避免这类并发冲突。

可行解决方案 #

1. 用PL/pgSQL函数捕获异常 #

将创建逻辑封装到PL/pgSQL函数中，直接捕获42P07（duplicate_table）错误并忽略，这是最简便的处理方式：

CREATE OR REPLACE FUNCTION create_prc_partition(p_partition_name text, p_from_value int, p_to_value int)
RETURNS void AS $$
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF prc FOR VALUES FROM (%L) TO (%L);',
        p_partition_name, p_from_value, p_to_value
    );
EXCEPTION
    WHEN duplicate_table THEN
        -- 分区已存在，直接忽略错误
        NULL;
END;
$$ LANGUAGE plpgsql;

客户端只需调用该函数即可：

SELECT create_prc_partition('prc_739214', 1000, 2000);

即使多个客户端同时调用，重复创建的错误会被函数内部捕获，不会返回给客户端。

2. 加表锁确保原子性 #

通过先锁定主分区表，再执行检查和创建操作，彻底消除竞态窗口：

BEGIN;
-- 锁定主表，阻止其他并发的分区创建操作
LOCK TABLE prc IN EXCLUSIVE MODE;

-- 检查分区是否已存在
IF NOT EXISTS (
    SELECT 1 FROM pg_tables 
    WHERE schemaname = current_schema() AND tablename = 'prc_739214'
) THEN
    CREATE TABLE prc_739214 PARTITION OF prc FOR VALUES FROM (...) TO (...);
END IF;
COMMIT;

注意：EXCLUSIVE MODE锁会阻止主表的其他写入和DDL操作，若并发量较高，可能影响整体性能。

3. 使用advisory锁（轻量级并发控制） #

针对特定分区名加一个advisory锁，仅允许拿到锁的客户端执行创建操作，避免冲突：

-- 基于分区名生成唯一锁键
SELECT pg_advisory_lock(hashtext('prc_739214'));

BEGIN;
CREATE TABLE IF NOT EXISTS prc_739214 PARTITION OF prc FOR VALUES FROM (...) TO (...);
COMMIT;

-- 释放锁
SELECT pg_advisory_unlock(hashtext('prc_739214'));

advisory锁是应用层可控的轻量级锁，不会影响主表的其他正常操作，适合高并发场景。

内容的提问来源于stack exchange，提问作者Dániel Szoboszlay