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 schema shema;
create table shema.source_table(field1, field2, field3)as values (1,2,3);
create table shema.transition_table(field1, field2, field3)as values (1,2,3);

CREATE OR REPLACE FUNCTION shema.function_name()
    RETURNS trigger LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    INSERT INTO shema.source_table(field1, field2, field3)TABLE newtab;

    DELETE FROM shema.transition_table;
    
    RETURN null;
END; $BODY$;

CREATE OR REPLACE TRIGGER trigger_name
    AFTER INSERT
    ON shema.transition_table
    REFERENCING NEW TABLE AS newtab
    FOR EACH STATEMENT
    EXECUTE FUNCTION shema.function_name();

CREATE SCHEMA

SELECT 1

SELECT 1

CREATE FUNCTION

CREATE TRIGGER

insert into shema.transition_table values (11,21,31);

INSERT 0 1

select*from shema.transition_table;--it got wiped after moving the rows to source_table

field1
field2
field3

SELECT 0

select*from shema.source_table;--all rows went here

field1
field2
field3

1
2
3

11
21
31

SELECT 2

insert into shema.transition_table values (12,22,32);

INSERT 0 1

select*from shema.transition_table;--it got wiped after moving the rows to source_table

field1
field2
field3

SELECT 0

select*from shema.source_table;--all rows went here

field1
field2
field3

1
2
3

11
21
31

12
22
32

SELECT 3