Postgres 10

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 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

SELECT 1

create table table_a (id int, name varchar, description varchar, type varchar, shared_column varchar);
create table table_b (id int, table_a_id int, price decimal, shared_columns varchar);

CREATE TABLE

CREATE TABLE

create view legacy_table as 
select t1.*,t2.price,t2.shared_columns
from table_a t1 inner join table_b t2 on t1.id=t2.table_a_id

CREATE VIEW

CREATE FUNCTION update_legacy_table_trigger()
RETURNS trigger AS $$
  BEGIN
    UPDATE table_a SET name=new.name, description=new.description,type=new.type, shared_column=new.shared_column WHERE id = NEW.id;
    UPDATE table_b SET price=new.price, shared_columns=new.shared_columns WHERE table_a_id = NEW.id; 
    return null;
  END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

create trigger trg_update_legacy 
INSTEAD of update
on legacy_table
for each row 
execute procedure update_legacy_table_trigger();

CREATE TRIGGER

insert into table_a values(1,'A','B','C','D');
insert into table_a values(2,'A1','B1','C1','D1');
insert into table_a values(3,'A2','B2','C2','D2');
insert into table_b values(1,1,1.2,'D');
insert into table_b values(2,2,1.3,'D1');
insert into table_b values(3,3,1.4,'D2');

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

update legacy_table set price=0 where name ilike '%1%';

UPDATE 0

select * from table_a;
select * from table_b;

id
name
description
type
shared_column

1
A
B
C
D

3
A2
B2
C2
D2

2
A1
B1
C1
D1

SELECT 3

id
table_a_id
price
shared_columns

1
1
1.2
D

3
3
1.4
D2

2
2
0
D1

SELECT 3