插入触发器最终会在分区表中插入重复的行[英] Insert trigger ends up inserting duplicate rows in partitioned table

本文是小编为大家收集整理的关于插入触发器最终会在分区表中插入重复的行的处理方法,想解了插入触发器最终会在分区表中插入重复的行的问题怎么解决?插入触发器最终会在分区表中插入重复的行问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有一个带有(我认为的)适当的 INSERT 触发器和一些约束的分区表.不知何故,INSERT 语句为每个 INSERT 插入 2 行:一个用于父级,一个用于适当的分区.

简单的设置如下:

CREATE TABLE foo (
id SERIAL NOT NULL,
d_id INTEGER NOT NULL,
label VARCHAR(4) NOT NULL);

CREATE TABLE foo_0 (CHECK (d_id % 2 = 0)) INHERITS (foo);
CREATE TABLE foo_1 (CHECK (d_id % 2 = 1)) INHERITS (foo);

ALTER TABLE ONLY foo ADD CONSTRAINT foo_pkey PRIMARY KEY (id);
ALTER TABLE ONLY foo_0 ADD CONSTRAINT foo_0_pkey PRIMARY KEY (id);
ALTER TABLE ONLY foo_1 ADD CONSTRAINT foo_1_pkey PRIMARY KEY (id);

ALTER TABLE ONLY foo ADD CONSTRAINT foo_d_id_key UNIQUE (d_id, label);
ALTER TABLE ONLY foo_0 ADD CONSTRAINT foo_0_d_id_key UNIQUE (d_id, label);
ALTER TABLE ONLY foo_1 ADD CONSTRAINT foo_1_d_id_key UNIQUE (d_id, label);

CREATE OR REPLACE FUNCTION foo_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.id IS NULL THEN
       NEW.id := nextval('foo_id_seq');
    END IF;

    EXECUTE 'INSERT INTO foo_' || (NEW.d_id % 2) || ' SELECT $1.*' USING NEW;
    RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_foo_trigger
    BEFORE INSERT ON foo
    FOR EACH ROW EXECUTE PROCEDURE foo_insert_trigger();

在进一步调试后,我隔离了导致它的原因:INSERT 触发器返回 NEW 而不是 NULL.但是,我确实希望我的插入语句返回自动增量 id,如果我只返回 NULL,情况就不会如此.

解决办法是什么?为什么返回 NEW 会导致这种看似"奇怪"​​的行为?

更新 #1

嗯,我知道为什么行被插入两次,从触发器的文档中可以清楚地看出:

<块引用>

每个语句触发器调用的触发器函数应始终返回 NULL.每行触发器调用的触发器函数可以返回调用执行程序的表行(HeapTuple 类型的值),如果他们选择.在操作之前触发的行级触发器以下选择:

它可以返回NULL来跳过当前行的操作.这指示执行程序不执行行级操作调用触发器(插入、修改或删除特定的表格行).

仅对于行级 INSERT 和 UPDATE 触发器,返回的行成为将被插入或将替换正在被插入的行的行更新.这允许触发器函数修改正在执行的行插入或更新.

但我的问题仍然是如何不返回 NEW 并且仍然能够获得自动递增的 id 或 ROW_COUNT 例如?

更新 #2

我找到了解决方案,但我当然希望有更好的解决方案.基本上,您可以添加一个 AFTER TRIGGER 来删除插入到父表中的行.这似乎非常低效,所以如果有人有更好的解决方案,请发布!

供参考的解决方案是:

CREATE TRIGGER insert_foo_trigger
    BEFORE INSERT ON foo
    FOR EACH ROW EXECUTE PROCEDURE foo_insert_trigger();


CREATE OR REPLACE FUNCTION foo_delete_master() 
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM ONLY foo WHERE id = NEW.id;
    RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER after_insert_foo_trigger
    AFTER INSERT ON foo
    FOR EACH ROW EXECUTE PROCEDURE foo_delete_master();

推荐答案

更简单的方法是创建存储过程而不是触发器,例如 add_foo( [parameters] ),它会决定哪个分区适合插入行并返回 id(或新的记录值,包括 id).例如:

CREATE OR REPLACE FUNCTION add_foo(
    _d_id   INTEGER
,   _label  VARCHAR(4)
) RETURNS BIGINT AS $$
DECLARE
    _rec    foo%ROWTYPE;
BEGIN
    _rec.id := nextval('foo_id_seq');
    _rec.d_id := _d_id;
    _rec.label := _label;
    EXECUTE 'INSERT INTO foo_' || ( _d_id % 2 ) || ' SELECT $1.*' USING _rec;
    RETURN _rec.id;
END $$ LANGUAGE plpgsql;

本文地址:https://www.itbaoku.cn/post/1764199.html