Index created in BEFORE trigger not updated during INSERT

From: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "'pgsql-hackers(at)postgresql(dot)org'" <pgsql-hackers(at)postgresql(dot)org>
Subject: Index created in BEFORE trigger not updated during INSERT
Date: 2017-05-22 11:05:14
Message-ID: A737B7A37273E048B164557ADEF4A58B53A4DC9A@ntex2010i.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Not that it is a useful use case, but I believe that this is
a bug that causes index corruption:

CREATE TABLE mytable(
id integer PRIMARY KEY,
id2 integer NOT NULL
);

CREATE FUNCTION makeindex() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
CREATE INDEX ON mytable(id2);
RETURN NEW;
END;$$;

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

INSERT INTO mytable VALUES (1, 42);

SELECT * FROM mytable;
┌────┬─────┐
│ id │ id2 │
├────┼─────┤
│ 1 │ 42 │
└────┴─────┘
(1 row)

But 42 is not present in the index:

SET enable_seqscan = off;

SELECT * FROM mytable WHERE id2 = 42;
┌────┬─────┐
│ id │ id2 │
├────┼─────┤
└────┴─────┘
(0 rows)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kuntal Ghosh 2017-05-22 11:42:24 Re: pg_dump ignoring information_schema tables which used in Create Publication.
Previous Message Kuntal Ghosh 2017-05-22 11:02:21 Re: "create publication..all tables" ignore 'partition not supported' error