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

问题描述

我有一个分区表，该表带有（我认为）适当的 INSERT 触发器和一些约束。不知何故， INSERT 语句为每个 INSERT 插入两行：一行用于父级，另一行用于适当的分区。

I have a partitioned table with (what I think) the appropriate INSERT trigger and a few constraints on it. Somehow, INSERT statements insert 2 rows for each INSERT: one for the parent and one for the appropriate partition.

简要的设置如下：

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 不会就是这样。

Upon further debugging I isolated what's causing it: the fact that the INSERT trigger returns NEW as opposed to just NULL. However I do want my insert statements to return the auto-increment id and if I just return NULL that won't be the case.

有什么解决方案？为什么返回 NEW 会导致这种看似奇怪​​的行为？

What's the solution? Why does returning NEW cause this seemingly "strange" behavior?

UPDATE＃1

好吧，我知道为什么行插入两次，这从触发器的文档中可以很明显地看出来：

Well, I know why the rows got inserted twice as it is clear from the documentation of triggers:

按语句触发器调用的触发器函数应始终
返回NULL。如果每行触发器选择了
，则每行触发器调用的触发器函数可以将表行
返回到执行程序的值（HeapTuple类型的值）。在操作之前触发的行级触发器具有
以下选择：

Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row triggers can return a table row (a value of type HeapTuple) to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices:

它可以返回NULL以跳过当前行的操作。
指示执行者不要执行
调用触发器的行级操作（
特定表行的插入，修改或删除）。

It can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion, modification, or deletion of a particular table row).

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

For row-level INSERT and UPDATE triggers only, the returned row becomes the row that will be inserted or will replace the row being updated. This allows the trigger function to modify the row being inserted or updated.

但是我的问题仍然是怎么可能不会返回 NEW ，并且仍然能够获得自动递增的 id 或 ROW_COUNT 例如？

But my question is still how is it possible to not return NEW and still be able to get the auto-incremented id, or ROW_COUNT for example?

更新＃2

我找到了一种解决方案，但我当然希望有更好的解决方案。基本上，您可以添加 AFTER TRIGGER 删除插入到父表中的行。这似乎效率极低，所以如果有人有更好的解决方案，请发布它！

I found a solution, but I sure hope that there's a better one. Basically, you can add an AFTER TRIGGER to delete the row inserted into the parent table. This seems horribly inefficient, so if anyone has a better solution, please post it!

供参考的解决方案是：

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）。例如：

A simpler way is to create stored procedure instead of the triggers, for example add_foo( [parameters] ), which would decide which partition is suitable to insert a row to and return id (or the new record values, including id). For example:

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;

这篇关于插入触发器最终在分区表中插入重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！