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 TYPE state AS (last_active timestamp);
CREATE TABLE devices (state state);

CREATE OR REPLACE FUNCTION update_last_active_on_position_update_hook()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.state IS NOT NULL THEN
      NEW.state.last_active := now();
   END IF;
   RETURN NEW;
END
$func$;

CREATE TRIGGER foo
BEFORE UPDATE ON devices
FOR EACH ROW
-- WHEN (NEW.state IS NOT NULL)
EXECUTE FUNCTION update_last_active_on_position_update_hook();

CREATE TYPE

CREATE TABLE

CREATE FUNCTION

CREATE TRIGGER

INSERT INTO devices VALUES ('("2021-11-11 11:11:11")')
RETURNING *;

state

("2021-11-11 11:11:11")

INSERT 0 1

UPDATE devices
SET    state.last_active = '2000-12-04 15:10:29'
RETURNING *;

state

("2023-05-29 14:22:53.311173")

UPDATE 1