首页 文章

postgres触发器创建索引：BEFORE INSERT ON隐藏一行

提问于
浏览 次
0

我有一个调用函数的触发器 AFTER INSERT ON mytable

CREATE OR REPLACE FUNCTION myfunction() RETURNS trigger AS
$BODY$
    DECLARE
        index TEXT;
    BEGIN
        index := 'myIndex_' || NEW.id2::text;
        IF to_regclass(index::cstring) IS NULL THEN
            EXECUTE 'CREATE INDEX ' || index || ' ON mytable(id) WITH (FILLFACTOR=100) WHERE id2=' || NEW.id2|| ';';
            RAISE NOTICE 'Created new index %',index;
        END IF;
        RETURN NULL;
    END;
$BODY$
LANGUAGE plpgsql VOLATILE
SECURITY DEFINER
COST 100;
ALTER FUNCTION myfunction()
    OWNER TO theadmin;

这非常有效 . 对于每个不同的 id2 我创建一个索引 . 加快相关查询速度 .

如上所述，我触发了这个 AFTER INSERT ON . 在此之前，我将触发器设置为 BEFORE INSERT ON . 功能做了一些奇怪的事情 . （是的，我已将 RETURN NULL 更改为 RETURN NEW ）

  • 插入新行 insert into mytable VALUES(1391, 868, 0.5, 0.5);

  • 创建相应的索引 myIndex_868

  • 在执行选择时在mytable中插入行 does not appear :(

  • 尝试插入同一行导致 ERROR: duplicate key value violates unique constraint "mytable_pkey" ，因为当然 DETAIL: Key (id, id2)=(1391, 868) already exists.

  • 为同一个id2插入其他行按预期工作:)

  • DELETE FROM mytable WHERE id = 1391 and id2 = 868 什么也没做

  • DROP INDEX myIndex_868; 删除索引 . 突然间，表中从未出现的最初一行突然出现了！

为什么 BEFORE INSERT ON 表现得如此不同？这是postgres 9.4中的一个错误还是我忽略了什么？

为了完整起见：

CREATE TRIGGER mytrigger
AFTER INSERT ON mytable
FOR EACH ROW EXECUTE PROCEDURE myfunction();

与

CREATE TRIGGER mytrigger
BEFORE INSERT ON mytable
FOR EACH ROW EXECUTE PROCEDURE myfunction();
postgresql triggers postgresql-9.4

1 回答

  • 2

    我认为这是PostgreSQL中的一个错误 . 我可以用9.6重现它 .

    很明显，该行不包含在索引中，因为它是在 BEFORE 触发器中创建的，但是在插入行时索引未更新的事实在我看来是一个错误 .

    我有written to pgsql-hackers要求发表意见 .

    但除此之外，我没有看到整个演习的重点 . 创建一个巨大的索引比创建一个单一的索引更好：

    CREATE INDEX ON mytable(id2, id);
    回复于

相关问题