Postgres 12

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 userMessage (
    message_id BIGINT PRIMARY KEY,
    creator_id BIGINT NOT NULL,
    created_at timestamp NULL,
    modified_at timestamp NULL,
    content TEXT NOT NULL
);

CREATE TABLE userMessageLog (
    entry_id BIGSERIAL,
    message_id BIGINT,
    modified_by BIGINT NOT NULL,
    modified_at timestamp NOT NULL,
    content TEXT NOT NULL,
    
    PRIMARY KEY (message_id, entry_id),

    foreign key (message_id) references userMessage (message_id)
);
CREATE OR REPLACE FUNCTION new_message() RETURNS trigger AS $$
BEGIN

    /* Always add a new entry to logs table */
    INSERT INTO userMessageLog(message_id, modified_by, modified_at, content) 
    VALUES (NEW.message_id, NEW.creator_id, current_timestamp, NEW.content);

    IF TG_OP = 'UPDATE' THEN
        UPDATE userMessage
        SET    modified_at = current_timestamp
        WHERE  message_id = NEW.message_id;
    END IF;

    RETURN NEW;
    
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER new_message 
AFTER INSERT OR UPDATE
ON userMessage 
    FOR EACH ROW 
    WHEN (pg_trigger_depth() = 0)
    EXECUTE PROCEDURE new_message();
INSERT INTO userMessage VALUES (1, 100, current_timestamp, null, 'some text');
INSERT INTO userMessage VALUES (2, 200, current_timestamp, null, 'some text');
1 rows affected

1 rows affected

UPDATE userMessage 
SET    content = 'another text'
WHERE  message_id = 1;
1 rows affected

SELECT * FROM userMessage;
SELECT * FROM userMessageLog;


message_id
creator_id
created_at
modified_at
content


2
200
2020-06-16 08:27:24.378863
null
some text

1
100
2020-06-16 08:27:24.378863
2020-06-16 08:27:24.381258
another text




entry_id
message_id
modified_by
modified_at
content


1
1
100
2020-06-16 08:27:24.378863
some text

2
2
200
2020-06-16 08:27:24.378863
some text

3
1
100
2020-06-16 08:27:24.381258
another text

message_id	creator_id	created_at	modified_at	content
2	200	2020-06-16 08:27:24.378863	null	some text
1	100	2020-06-16 08:27:24.378863	2020-06-16 08:27:24.381258	another text