Postgres 15

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 IF NOT EXISTS table1 (
    id serial PRIMARY KEY,
    parent_id integer,
    stime timestamptz NOT NULL DEFAULT NOW(),
    etime timestamptz DEFAULT NULL,
    time_spent interval DEFAULT NULL,
    rate numeric DEFAULT NULL,
    cost numeric DEFAULT NULL
);

CREATE OR REPLACE FUNCTION trigger1()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    --...
    NEW.time_spent = NEW.etime - NEW.stime;
    NEW.cost := date_part('epoch',NEW.time_spent)
               /date_part('epoch','1 hour'::interval)
               *NEW.rate; -- field "cost" has been changed!
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER before_100_calc_cost 
  BEFORE INSERT OR UPDATE OF stime, etime 
  ON table1 
  FOR EACH ROW EXECUTE PROCEDURE trigger1();
--The second trigger "trigger2", which for some reason is not executed:

CREATE OR REPLACE FUNCTION trigger2()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RAISE NOTICE 'trigger "trigger2" did not start!((';
    CREATE TABLE IF NOT EXISTS trigger2fired(when_)
      as values(clock_timestamp());
    update trigger2fired set when_=clock_timestamp();
    RETURN NEW;
    -- some actions...
END;
$BODY$;

CREATE TRIGGER after_104_calc_parent_cost 
  AFTER INSERT OR UPDATE OF cost 
  OR DELETE ON table1 
  FOR EACH ROW EXECUTE PROCEDURE trigger2();

INSERT INTO table1 (stime) 
  VALUES (NOW() - INTERVAL '1 hour')
  RETURNING *;

CREATE TABLE



CREATE FUNCTION



CREATE TRIGGER



CREATE FUNCTION



CREATE TRIGGER




id
parent_id
stime
etime
time_spent
rate
cost


1
null
2024-12-27 17:32:03.409727+00
null
null
null
null



INSERT 0 1


table trigger2fired;
drop table trigger2fired;


when_


2024-12-27 18:32:03.429865+00



SELECT 1



DROP TABLE


UPDATE table1 
  SET etime = NOW()
  RETURNING *; -- trigger "trigger2" did not start!
--When "trigger1" is executed and the "cost" fiel


id
parent_id
stime
etime
time_spent
rate
cost


1
null
2024-12-27 17:32:03.409727+00
2024-12-27 18:32:03.434025+00
01:00:00.024298
null
null



UPDATE 1


table trigger2fired;

ERROR:  relation "trigger2fired" does not exist
LINE 1: table trigger2fired;
              ^


UPDATE table1 
  SET etime = NOW(), rate=1, cost=null
  RETURNING *; -- trigger "trigger2" did not start!


id
parent_id
stime
etime
time_spent
rate
cost


1
null
2024-12-27 17:32:03.409727+00
2024-12-27 18:32:03.435504+00
01:00:00.025777
1
1.00000716027778



UPDATE 1


table trigger2fired;
drop table trigger2fired;


when_


2024-12-27 18:32:03.436236+00



SELECT 1



DROP TABLE


drop trigger after_104_calc_parent_cost on table1;
CREATE TRIGGER after_104_calc_parent_cost 
  AFTER UPDATE ON table1 
  FOR EACH ROW WHEN (NEW.cost is distinct from OLD.cost)
  EXECUTE PROCEDURE trigger2();

DROP TRIGGER



CREATE TRIGGER


UPDATE table1 
  SET etime = NOW(), rate=2
  RETURNING *; -- trigger "trigger2" did not start!


id
parent_id
stime
etime
time_spent
rate
cost


1
null
2024-12-27 17:32:03.409727+00
2024-12-27 18:32:03.440233+00
01:00:00.030506
2
2.00001694777778



UPDATE 1


table trigger2fired;


when_


2024-12-27 18:32:03.44117+00



SELECT 1