PostgreSQL异常：失败INSERT时NEW.id仍递增，行数未变

调试PostgreSQL触发器函数时遇到两个核心问题：

• 插入数据触发预期内的调试错误，所有表行数仍为0，但NEW.id随每次失败测试递增。主键本应仅在成功插入后自增，为何失败插入时仍会递增？
• 报错显示外键约束违反，errors表插入时引用的processes_id不存在于processes表。

触发器函数代码 #

-- FUNCTION: public.distribute_process_data()

-- DROP FUNCTION IF EXISTS public.distribute_process_data();

CREATE OR REPLACE FUNCTION public.distribute_process_data()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    time_stamp timestamp;
    source_id int;
    medium_id int;
    country_id int;
    request_id int;
    error_id int;
    success bool;
BEGIN
    select CURRENT_TIMESTAMP into time_stamp;

    INSERT INTO
        sources(source, category)
        VALUES(NEW.source, NEW.category) ON CONFLICT (source) DO NOTHING
        RETURNING id INTO source_id;

    INSERT INTO
        mediums(sources_id, medium)
        VALUES(source_id, NEW.medium) ON CONFLICT (sources_id, medium) DO NOTHING
        RETURNING id INTO medium_id;

    INSERT INTO
        countries(country)
        VALUES(NEW.country) ON CONFLICT (country) DO NOTHING
        RETURNING id INTO country_id;

    INSERT INTO
        requests(sources_id, request_jsonstring, request_md5hash)
        VALUES(source_id, NEW.request, md5(NEW.request::text)) ON CONFLICT (request_md5hash) DO NOTHING
        RETURNING id INTO request_id;

    CASE
        WHEN NEW.error IS NOT NULL THEN
            INSERT INTO
                errors(timestamp, processes_id, error)
                VALUES(time_stamp, NEW.id, NEW.error)
                RETURNING id INTO error_id;
            select FALSE INTO success;
        ELSE
            select NULL INTO error_id;
            select TRUE INto success;
    END CASE;

    INSERT INTO
        processes(
            id, timestamp, requests_id, errors_id, sources_id, mediums_id, countries_id, successful,
            date, web_visits, conversions, ad_impressions, ad_clicks, ad_cost, companygo_ad_visits,
            companynetwork_ad_visits, companygo_ad_visits_bounce_rate, downloads_desktop, downloads_mobile,
            initiated_registrations, completed_registrations, paid_users, revenue
        )
        VALUES(
            NEW.id, time_stamp, request_id, error_id, medium_id, country_id, success,
            NEW.date, NEW.web_visits, NEW.conversions, NEW.ad_impressions, NEW.ad_clicks, NEW.ad_cost,
            NEW.companygo_ad_visits, NEW.companynetwork_ad_visits, NEW.companygo_ad_visits_bounce_rate, 
            NEW.downloads_desktop, NEW.downloads_mobile, NEW.initiated_registrations, completed_registrations, 
            NEW.paid_users, NEW.revenue
        );
    RETURN trigger;
END;
$BODY$;

ALTER FUNCTION public.distribute_process_data()
    OWNER TO postgres;

报错信息 #

---------------------------------------------------------------------------
ForeignKeyViolation                       Traceback (most recent call last)
Cell In [4], line 1
----> 1 cursor.execute(sql)

ForeignKeyViolation: insert or update on table "errors" violates foreign key constraint "processes_fkey"
DETAIL:  Key (processes_id)=(13) is not present in table "processes".
CONTEXT:  SQL statement "INSERT INTO
                errors(timestamp, processes_id, error)
                VALUES(time_stamp, NEW.id, NEW.error)
                RETURNING id"
PL/pgSQL function distribute_process_data() line 34 at SQL statement

注意：报错中(processes_id)=(13)，初始值为1，每次失败INSERT后递增2，按预期因插入失败行数为0，NEW.id应保持为1。

1. NEW.id递增的原因 #

PostgreSQL的序列（serial/bigserial类型主键依赖的序列）是预分配且不会回滚的。当你尝试插入记录时，序列会先被调用生成下一个值并赋值给NEW.id，即便后续插入操作因触发器报错而回滚，序列的已生成值不会被撤销。这是序列的设计特性，避免并发场景下的锁竞争，保证序列值的唯一性，所以哪怕插入失败，序列值依然会递增。

如果只是测试阶段需要重置序列，可以用以下命令：

-- 假设processes表的主键序列是processes_id_seq，替换为实际序列名
ALTER SEQUENCE processes_id_seq RESTART WITH 1;

2. 外键约束报错的解决 #

当前触发器逻辑是先插入errors表（引用NEW.id作为processes_id），再插入processes表。但外键约束要求processes_id必须存在于processes表中，此时processes表还没有对应的记录，必然触发约束错误。

调整逻辑顺序：

• 先插入processes表，确保NEW.id对应的记录存在后，再插入errors表；
• 插入errors后，需要更新processes表的errors_id字段，关联新增的错误记录。

同时注意触发器最后返回值错误：应该返回NEW而不是trigger，否则可能导致触发行为异常。

修改后的触发器核心逻辑片段：

-- 先初始化error_id和success的值
error_id := NULL;
success := TRUE;

-- 先插入processes表
INSERT INTO
    processes(
        id, timestamp, requests_id, errors_id, sources_id, mediums_id, countries_id, successful,
        date, web_visits, conversions, ad_impressions, ad_clicks, ad_cost, companygo_ad_visits,
        companynetwork_ad_visits, companygo_ad_visits_bounce_rate, downloads_desktop, downloads_mobile,
        initiated_registrations, completed_registrations, paid_users, revenue
    )
    VALUES(
        NEW.id, time_stamp, request_id, error_id, medium_id, country_id, success,
        NEW.date, NEW.web_visits, NEW.conversions, NEW.ad_impressions, NEW.ad_clicks, NEW.ad_cost,
        NEW.companygo_ad_visits, NEW.companynetwork_ad_visits, NEW.companygo_ad_visits_bounce_rate, 
        NEW.downloads_desktop, NEW.downloads_mobile, NEW.initiated_registrations, completed_registrations, 
        NEW.paid_users, NEW.revenue
    );

-- 再处理errors插入
IF NEW.error IS NOT NULL THEN
    INSERT INTO
        errors(timestamp, processes_id, error)
        VALUES(time_stamp, NEW.id, NEW.error)
        RETURNING id INTO error_id;
    -- 更新processes表的errors_id字段
    UPDATE processes SET errors_id = error_id, successful = FALSE WHERE id = NEW.id;
END IF;

-- 修正返回值
RETURN NEW;

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