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)
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 |