Postgres 14

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 test (id INT, val1 INT, val2 INT);
INSERT INTO test VALUES (1,11,111), (2,22,222);

CREATE TABLE

INSERT 0 2

CREATE FUNCTION fn_test() 
RETURNS TRIGGER
AS $fn_test$
BEGIN
    IF OLD.val1 <> NEW.val1 THEN
        NEW.id := NEW.id * 10;
    END IF;
    RETURN NEW;
END;
$fn_test$ LANGUAGE plpgsql;

CREATE FUNCTION

CREATE TRIGGER tr_test
BEFORE UPDATE ON test
FOR EACH ROW
EXECUTE FUNCTION fn_test();

CREATE TRIGGER

SELECT * FROM test;
UPDATE test SET val1 = 33 WHERE id = 1;
UPDATE test SET val2 = 444 WHERE id = 2;
SELECT * FROM test;

id
val1
val2

1
11
111

2
22
222

SELECT 2

UPDATE 1

UPDATE 1

id
val1
val2

10
33
111

2
22
444

SELECT 2