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 ab ( name text );

create table ab_deleted( name text );

create view abc as select * from ab;

  create or replace function abc_delete_0()
  returns trigger as $fn$
  begin
  insert into ab_deleted values ( OLD.* );
  OLD.name =  OLD.name || '--trig1';
  RETURN OLD;
  END;
  $fn$ language plpgsql;

  create or replace function abc_delete_1()
  returns trigger as $fn$
  begin
  insert into ab_deleted values ( OLD.* );
  OLD.name =  OLD.name || '--trig2';
  RETURN OLD;
  END;
  $fn$ language plpgsql;

  create trigger abc_delete_0
  instead of delete on abc
  for each row execute procedure abc_delete_0();

  create trigger abc_delete_1
  instead of delete on abc
  for each row execute procedure abc_delete_1();
insert into ab ( name ) values ( 'a1' ), ( 'b2' );
select * from abc;
2 rows affected

name

a1

b2

WITH t1 AS( delete from abc returning *)
select * from t1;
select * from ab_deleted;

name

a1

b2

name

a1

a1

b2

b2

-- expected
select * from ( values( 'a1--trig1--trig2' ), ( 'b2--trig1--trig2' ) ) as t( name );
select * from ( values( 'a1'), ('a1--trig1' ), ( 'b2' ), ('b2--trig1' ) ) as t( name );
-- because
-- In the case of BEFORE and INSTEAD OF triggers, the possibly-modified row 
-- returned by each trigger becomes the input to the next trigger
-- https://www.postgresql.org/docs/current/trigger-definition.html

name

a1--trig1--trig2

b2--trig1--trig2

name

a1

a1--trig1

b2

b2--trig1