PostgreSQL单脚本多BEGIN冲突及存储过程执行顺序问题求助

核心思路 #

问题出在存储过程内的事务控制语句（BEGIN/COMMIT/ROLLBACK）无法在已有事务上下文里执行，同时你需要保证表创建完成并提交后再处理存储过程。下面是两种直接可行的实现方式：

方案一：用显式事务拆分表创建与存储过程逻辑 #

把表创建放在独立的显式事务中，提交后再创建/调用存储过程，完全隔离两个阶段的事务上下文：

-- 开启事务创建所有表
BEGIN;

CREATE TABLE IF NOT EXISTS licenses (
    license_id smallint NOT NULL,
    name varchar(25) NOT NULL,
    PRIMARY KEY (license_id)
);
-- 其他表创建语句...

COMMIT; -- 表创建完成并持久化

-- 表已存在，现在创建带事务控制的存储过程
CREATE OR REPLACE PROCEDURE manage_licenses(p_id smallint, p_name varchar(25))
LANGUAGE plpgsql
AS $$
BEGIN
    -- 存储过程内部的事务逻辑
    BEGIN
        INSERT INTO licenses (license_id, name) VALUES (p_id, p_name);
        COMMIT;
    EXCEPTION
        WHEN UNIQUE_VIOLATION THEN
            ROLLBACK;
            RAISE NOTICE 'License ID % already exists', p_id;
    END;
END$$;

-- 调用存储过程（按需执行）
CALL manage_licenses(1, 'Commercial');

方案二：保留DO块创建表，后续单独处理存储过程 #

DO语句本身是独立执行单元，默认执行完毕后会自动提交事务，因此直接将存储过程的创建/调用放在DO块之后即可：

-- DO块创建表，执行完自动提交
DO $$
BEGIN
    CREATE TABLE IF NOT EXISTS licenses (
        license_id smallint NOT NULL,
        name varchar(25) NOT NULL,
        PRIMARY KEY (license_id)
    );
    -- 其他表创建语句...
END$$;

-- 表已提交，创建存储过程
CREATE OR REPLACE PROCEDURE delete_license(p_id smallint)
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        DELETE FROM licenses WHERE license_id = p_id;
        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            ROLLBACK;
            RAISE NOTICE 'License ID % not found', p_id;
    END;
END$$;

-- 调用存储过程
CALL delete_license(2);

为什么原来的方式失败 #

如果把CREATE PROCEDURE放在DO块的BEGIN/END内部，或者在DO块里调用带事务控制的存储过程，会因为DO块本身处于一个事务上下文里，而存储过程内的BEGIN/COMMIT试图在已有事务中开启/提交子事务，PostgreSQL不允许这种嵌套事务操作，因此报错。

内容的提问来源于stack exchange，提问作者Master