By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE prices (
prices_id serial PRIMARY KEY
, id int
, price int
, eff_from date
, eff_to date
);
CREATE TABLE
CREATE OR REPLACE FUNCTION prices_set_all_eff_to()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
UPDATE prices p
SET eff_to = COALESCE(p1.next_eff_to, 'infinity')
FROM news n
LEFT JOIN LATERAL (
SELECT p1.eff_from - 1 AS next_eff_to
FROM prices p1
WHERE p1.id = n.id
AND p1.eff_from > n.eff_from
ORDER BY p1.eff_from
LIMIT 1
) p1 ON true
WHERE n.eff_to IS NULL
AND p.prices_id = n.prices_id;
RETURN NULL; -- for statement-level trigger
END
$func$;
CREATE TRIGGER after_insert_prices
AFTER INSERT ON prices
REFERENCING NEW TABLE AS news -- transition table
FOR EACH STATEMENT
EXECUTE FUNCTION prices_set_all_eff_to();
CREATE FUNCTION
CREATE TRIGGER
-- multi-row (!) inserts work as desired: all rows are visible to all
INSERT INTO prices(id, price, eff_from) VALUES
(1, 100, '2017-01-12')
, (1, 150, '2017-02-09')
, (1, 125, '2017-01-27')
RETURNING *; -- NOTE how RETURNING does not show effects of AFTER trigger, yet!
| prices_id | id | price | eff_from | eff_to |
|---|---|---|---|---|
| 1 | 1 | 100 | 2017-01-12 | null |
| 2 | 1 | 150 | 2017-02-09 | null |
| 3 | 1 | 125 | 2017-01-27 | null |
INSERT 0 3
-- ... even when mixing with pre-existing rows
INSERT INTO prices(id, price, eff_from) VALUES
(1, 200, '2017-01-13')
, (1, 250, '2017-02-08')
, (1, 225, '2017-01-28')
;
INSERT 0 3
-- But pre-existing rows are not changed!
INSERT INTO prices(id, price, eff_from) VALUES
(1, 350, '2017-02-10') -- voids old "infinity"
;
INSERT 0 1
-- But single-row INSERTs have no way of anticipating later rows:
INSERT INTO prices(id, price, eff_from) VALUES
(2, 100, '2017-01-12');
INSERT INTO prices(id, price, eff_from) VALUES
(2, 150, '2017-02-09');
INSERT INTO prices(id, price, eff_from) VALUES
(2, 125, '2017-01-27');
INSERT 0 1
INSERT 0 1
INSERT 0 1
TABLE prices
ORDER BY id, eff_from;
| prices_id | id | price | eff_from | eff_to |
|---|---|---|---|---|
| 1 | 1 | 100 | 2017-01-12 | 2017-01-26 |
| 4 | 1 | 200 | 2017-01-13 | 2017-01-26 |
| 3 | 1 | 125 | 2017-01-27 | 2017-02-08 |
| 6 | 1 | 225 | 2017-01-28 | 2017-02-07 |
| 5 | 1 | 250 | 2017-02-08 | 2017-02-08 |
| 2 | 1 | 150 | 2017-02-09 | infinity |
| 7 | 1 | 350 | 2017-02-10 | infinity |
| 8 | 2 | 100 | 2017-01-12 | infinity |
| 10 | 2 | 125 | 2017-01-27 | 2017-02-08 |
| 9 | 2 | 150 | 2017-02-09 | infinity |
SELECT 10