Postgres 16

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 notes (
  note_id int not null, 
  version int not null,
  content text default 'some_text',
  primary key (note_id,version) );

CREATE TABLE


create index on notes (note_id,version);

create function tgf_notes_version() returns trigger language plpgsql as $f$
begin 
  NEW.version=(select coalesce(max(version),0)+1 
               from notes n
               where n.note_id=NEW.note_id );
  return NEW;
end $f$;

create trigger tg_a_gen before insert on notes
for each row execute function tgf_notes_version();

CREATE INDEX



CREATE FUNCTION



CREATE TRIGGER


insert into notes values 
  (1,999,'my_own_text11'), 
  (1,999,'my_own_text12'), 
  (2,999,'my_own_text21'), 
  (2,999,'my_own_text22'), 
  (3,999,'my_own_text31'), 
  (3,999,'my_own_text32'), 
  (3,999,'my_own_text33')
returning *;


note_id
version
content


1
1
my_own_text11

1
2
my_own_text12

2
1
my_own_text21

2
2
my_own_text22

3
1
my_own_text31

3
2
my_own_text32

3
3
my_own_text33



INSERT 0 7

note_id	version	content
1	1	my_own_text11
1	2	my_own_text12
2	1	my_own_text21
2	2	my_own_text22
3	1	my_own_text31
3	2	my_own_text32
3	3	my_own_text33