Postgres存储过程执行期间如何锁定源表写入避免数据不一致？

我在Postgres中创建了一个存储过程，该过程通过JOIN三张源表（myschema.session、myschema.message、myschema.data）创建新表newtable，随后为新表添加created_at和source列，最后在三张源表上创建INSERT触发器，将后续写入同步到新表。我知道存储过程本身是原子事务，但担心在新表创建完成到触发器创建完成的间隙，源表发生写入会导致新表数据不一致，丢失这些写入数据。我的存储过程代码如下：

CREATE OR REPLACE PROCEDURE myschema.table_creation()
LANGUAGE plpgsql
AS $procedure$
BEGIN
create table newtable as
SELECT * FROM myschema.session a NATURAL JOIN (SELECT * FROM myschema.message b NATURAL left JOIN myschema.data) as d;
ALTER TABLE myschema.newtable ADD created_at timestamp;
ALTER TABLE myschema.newtable ADD source text;
CREATE TRIGGER mytrigger
after INSERT
ON myschema.session
FOR EACH ROW
EXECUTE PROCEDURE myschema.trigger_proc();
CREATE TRIGGER mytrigger
after INSERT
ON myschema.messages
FOR EACH ROW
EXECUTE PROCEDURE myschema.trigger_proc();
CREATE TRIGGER mytrigger
after INSERT
ON myschema.data
FOR EACH ROW
EXECUTE PROCEDURE myschema.trigger_proc();
END;
$procedure$; 

请问如何在整个table_creation()存储过程执行期间锁定源表的写入操作，推迟这些写入直到过程完成，以避免竞态条件和数据丢失？

要解决这个竞态问题，可通过在存储过程开头添加显式表级锁的方式，阻塞源表的写入操作直到整个流程完成，具体实现如下：

1. 修改后的存储过程代码 #

CREATE OR REPLACE PROCEDURE myschema.table_creation()
LANGUAGE plpgsql
AS $procedure$
BEGIN
    -- 对三张源表加排他锁，阻塞所有写入操作，允许读操作
    LOCK TABLE myschema.session, myschema.message, myschema.data IN EXCLUSIVE MODE;

    -- 创建新表并同步初始数据
    CREATE TABLE myschema.newtable AS
    SELECT * FROM myschema.session a 
    NATURAL JOIN (SELECT * FROM myschema.message b NATURAL LEFT JOIN myschema.data) as d;

    -- 添加额外字段
    ALTER TABLE myschema.newtable ADD created_at timestamp;
    ALTER TABLE myschema.newtable ADD source text;

    -- 创建触发器（修正了原代码中触发器同名的问题）
    CREATE TRIGGER mytrigger_session
    AFTER INSERT ON myschema.session
    FOR EACH ROW EXECUTE PROCEDURE myschema.trigger_proc();

    CREATE TRIGGER mytrigger_message
    AFTER INSERT ON myschema.message
    FOR EACH ROW EXECUTE PROCEDURE myschema.trigger_proc();

    CREATE TRIGGER mytrigger_data
    AFTER INSERT ON myschema.data
    FOR EACH ROW EXECUTE PROCEDURE myschema.trigger_proc();
END;
$procedure$;

2. 核心要点说明 #

• 锁模式选择：使用EXCLUSIVE锁模式，它只会阻止其他事务对源表执行INSERT/UPDATE/DELETE操作，不会影响读请求，对业务的影响相对可控。如果需要完全阻塞所有访问（包括读），可以改用ACCESS EXCLUSIVE锁，但不推荐，会大幅影响可用性。
• 触发器名称修正：原代码中三个触发器重名，PostgreSQL不允许同一模式下存在同名触发器，因此修改为带有表标识的唯一名称。
• 事务与锁的关系：存储过程的执行是一个原子事务，锁会在事务提交时自动释放。期间其他事务的写入请求会被阻塞，直到当前存储过程执行完成，此时触发器已经生效，这些延迟的写入会被正常同步到newtable，不会出现数据丢失。

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