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?.

select version();


version


PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table test_iud_tab( id     integer generated always as identity 
                         , col1   text
                         , dml_op text
                         , dml_ts timestamptz
                         ) ;
                        
create view test_iud as 
       ( select id, col1 
           from test_iud_tab
          where dml_op <> 'D') ;
create or replace function test_iud_instead()
   returns trigger 
   language plpgsql
as $$
declare 
    l_tg_op text = substr(TG_OP,1,1);  
    l_tstz  timestamptz := now();
begin 
	
    if l_tg_op = 'I' then 
       insert into test_iud_tab(col1,dml_op,dml_ts)  
            values (new.col1, l_tg_op, l_tstz) ;
           
    elsif l_tg_op = 'U' then 
       update test_iud_tab 
          set col1 = new.col1
            , dml_op = l_tg_op 
            , dml_ts = l_tstz 
        where id = new.id ; 
    else 
       update test_iud_tab 
          set dml_op = l_tg_op 
            , dml_ts = l_tstz 
        where id = old.id ;             
    end if;
   
   return new;
end ; 
$$; 
create trigger test_iud_iiud 
  instead of insert or update or delete 
       on test_iud
       for each row 
       execute procedure test_iud_instead(); 
insert into test_iud(col1) 
   values ('T1'),('T2'),('X3');
3 rows affected

select * from test_iud_tab;
select * from test_iud;


id
col1
dml_op
dml_ts


1
T1
I
2021-01-11 20:58:58.842636+00

2
T2
I
2021-01-11 20:58:58.842636+00

3
X3
I
2021-01-11 20:58:58.842636+00




id
col1


1
T1

2
T2

3
X3


update test_iud 
   set col1 = 'T2n'
 where col1 = 'T2'; 

delete 
  from test_iud
 where col1 = 'X3';
1 rows affected

select * from test_iud_tab;
select * from test_iud;


id
col1
dml_op
dml_ts


1
T1
I
2021-01-11 20:58:58.842636+00

2
T2n
U
2021-01-11 20:58:58.854933+00

3
X3
D
2021-01-11 20:58:58.854933+00




id
col1


1
T1

2
T2n

id	col1	dml_op	dml_ts
1	T1	I	2021-01-11 20:58:58.842636+00
2	T2	I	2021-01-11 20:58:58.842636+00
3	X3	I	2021-01-11 20:58:58.842636+00

id	col1	dml_op	dml_ts
1	T1	I	2021-01-11 20:58:58.842636+00
2	T2n	U	2021-01-11 20:58:58.854933+00
3	X3	D	2021-01-11 20:58:58.854933+00