PostgreSQL恢复时ALTER TABLE触发事件触发器报tuple concurrently updated问题

核心问题分析 #

你遇到的tuple concurrently updated错误，确实是因为ALTER TABLE（添加主键）和GRANT操作同时修改pg_class目录表导致的。原函数每次触发都对public下所有序列执行GRANT，完全没必要，还会放大冲突概率——尤其是加主键的ALTER TABLE根本不会创建序列，原函数错误触发了GRANT操作，这才是冲突的主要诱因。

解决方案 #

1. 缩小GRANT范围，仅操作目标序列 #

只针对当前DDL操作实际创建/关联的序列授权，避免批量修改目录表：

• CREATE SEQUENCE：直接对新创建的序列授权
• CREATE TABLE/ALTER TABLE（添加IDENTITY列）：仅对表关联的IDENTITY序列授权

2. 添加异常重试机制 #

由于RDS无法手动锁定目录表，通过捕获并发异常并短暂重试，降低失败概率。

修改后的函数代码 #

CREATE OR REPLACE FUNCTION shared_extensions.foo_grant_r_seq_function()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    obj record;
    seq_regclass regclass;
    retry_count int := 3; -- 可调整的重试次数
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        -- 处理CREATE SEQUENCE操作
        IF obj.command_tag = 'CREATE SEQUENCE' THEN
            seq_regclass := obj.objid::regclass;
            WHILE retry_count > 0 LOOP
                BEGIN
                    EXECUTE format('GRANT SELECT ON %s TO foo', seq_regclass);
                    EXIT; -- 授权成功则退出重试循环
                EXCEPTION WHEN OTHERS THEN
                    IF SQLERRM LIKE '%tuple concurrently updated%' THEN
                        retry_count := retry_count - 1;
                        PERFORM pg_sleep(0.1); -- 短暂等待后重试
                    ELSE
                        RAISE; -- 非并发异常直接抛出
                    END IF;
                END;
            END LOOP;
        -- 处理涉及IDENTITY列的CREATE/ALTER TABLE操作
        ELSIF obj.command_tag IN ('CREATE TABLE', 'ALTER TABLE') THEN
            -- 查找当前表关联的IDENTITY序列
            SELECT col.attidentityseq::regclass INTO seq_regclass
            FROM pg_class tbl
            JOIN pg_attribute col ON tbl.oid = col.attrelid
            WHERE tbl.oid = obj.objid
              AND col.attidentity IS NOT NULL;
            
            IF seq_regclass IS NOT NULL THEN
                WHILE retry_count > 0 LOOP
                    BEGIN
                        EXECUTE format('GRANT SELECT ON %s TO foo', seq_regclass);
                        EXIT;
                    EXCEPTION WHEN OTHERS THEN
                        IF SQLERRM LIKE '%tuple concurrently updated%' THEN
                            retry_count := retry_count - 1;
                            PERFORM pg_sleep(0.1);
                        ELSE
                            RAISE;
                        END IF;
                    END;
                END LOOP;
            END IF;
        END IF;
    END LOOP;
END;
$function$;

额外优化：精准触发授权 #

原函数对所有ALTER TABLE操作都执行GRANT，包括加主键这种不涉及序列的操作，这是冲突的根源之一。可以进一步限制触发条件，仅当ALTER TABLE是添加IDENTITY列时才执行授权：

在ALTER TABLE的分支中添加判断：

ELSIF obj.command_tag = 'ALTER TABLE' THEN
    -- 检查是否为添加IDENTITY列的操作
    IF EXISTS (
        SELECT 1 FROM pg_event_trigger_get_ddl_command(obj.objid)
        WHERE command LIKE '%ADD GENERATED ALWAYS AS IDENTITY%'
    ) THEN
        -- 后续的序列查找与授权逻辑
    END IF;

这样可以彻底避免在加主键的ALTER TABLE时触发无用的GRANT，从源头减少冲突。

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