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 sensor_data 
(
    id int primary key,
    p1 int,
    p1_tm timestamp,
    p2 int,
    p2_tm timestamp
);

insert into sensor_data values
(1, 12, '20200101 10:00:00', 23, '20200101 11:00:00');

CREATE TABLE



INSERT 0 1


select * from sensor_data;


id
p1
p1_tm
p2
p2_tm


1
12
2020-01-01 10:00:00
23
2020-01-01 11:00:00



SELECT 1


create or replace function trg_sensor_data()
returns trigger as 
$$
begin

  if NEW.p1 <> OLD.p1 and NEW.p1 IS NOT NULL then
      NEW.p1_tm = now();
  end if;

  if NEW.p2 <> OLD.p2 and NEW.p2 IS NOT NULL  then
      NEW.p2_tm = now();
  end if;

  return NEW;
  
end;
$$
language 'plpgsql';

CREATE FUNCTION


create trigger trg_sensor_data
before update
on sensor_data
for each row execute procedure trg_sensor_data();

CREATE TRIGGER


update sensor_data set p1 = 100;

select * from sensor_data;

UPDATE 1




id
p1
p1_tm
p2
p2_tm


1
100
2023-10-22 08:18:48.286168
23
2020-01-01 11:00:00



SELECT 1


update sensor_data set p2 = 200;

select * from sensor_data;

UPDATE 1




id
p1
p1_tm
p2
p2_tm


1
100
2023-10-22 08:18:48.286168
200
2023-10-22 08:18:48.292194



SELECT 1