postgres trigger creates index: BEFORE INSERT ON hides one row

Question

I have a trigger AFTER INSERT ON mytable that calls a function

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;

This works wonderfully. For each distinct id2 I create an index. Speeds up relevant queries by a lot.

As mentioned above I trigger this AFTER INSERT ON. Before doing that however I had the trigger set to BEFORE INSERT ON. And the function did some strange things. (Yes, I had changed the RETURN NULL to RETURN NEW)

insert of a new row insert into mytable VALUES(1391, 868, 0.5, 0.5);
creates the corresponding index myIndex_868
the inserted row does not appear in mytable when doing a select :(
trying to insert the same row results in ERROR: duplicate key value violates unique constraint "mytable_pkey" because of course DETAIL: Key (id, id2)=(1391, 868) already exists.
inserting other rows for the same id2 works as expected :)
DELETE FROM mytable WHERE id = 1391 and id2 = 868 does nothing
DROP INDEX myIndex_868; drops the index. And suddenly the initial row that never appeared in the table is suddenly there!

Why does BEFORE INSERT ON behave so differently? Is this a bug in postgres 9.4 or did I overlook something?

Just for completeness' sake:

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

vs.

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

Laurenz Albe · Accepted Answer · 2017-05-22 12:05:58Z

2

I'd argue that this is a bug in PostgreSQL. I could reproduce it with 9.6.

It is clear that the row is not contained in the index as it is created in the BEFORE trigger, but the fact that the index is not updated when the row is inserted is a bug in my opinion.

I have written to pgsql-hackers to ask for an opinion.

But apart from that, I don't see the point of the whole exercise. Better than creating a gazillion indexes would be to create a single one:

CREATE INDEX ON mytable(id2, id);

answered May 22, 2017 at 12:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bodison Over a year ago

would that cover all my queries for id2?SELECT * FROM mytable WHERE id2=$1 used to be very slow, now with an index for each WHERE clause it is fast.

Laurenz Albe Over a year ago

For that, an even simpler index would be better: CREATE INDEX ON mytable(id2).

bodison Over a year ago

Are you sure about that? I was under the impression that I need one index per WHERE clause. Also I'm sorting my results by id. SELECT * FROM mytable WHERE id2=$1 ORDER BY id; and the index speeds that up immensely.

Laurenz Albe Over a year ago

Well, then use the index I gave in my answer. Try it, you'll see that it is just as fast. And those many indexes will create a headache for you during query planning and other things.

bodison Over a year ago

Thank you! That's so much easier :)

Collectives™ on Stack Overflow

postgres trigger creates index: BEFORE INSERT ON hides one row

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related