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 foo (foo_id serial PRIMARY KEY, foo text);
CREATE TABLE fooshadow (LIKE foo);

INSERT INTO foo (foo) VALUES ('foo_txt'), ('bar_txt');

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger AS
$func$
BEGIN
    EXECUTE format('INSERT INTO %I.%I VALUES ($1.*)', TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
    USING OLD;

    RETURN OLD;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER delbef
BEFORE DELETE ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_before();

CREATE TABLE

CREATE TABLE

INSERT 0 2

CREATE FUNCTION

CREATE TRIGGER

DELETE FROM foo WHERE foo_id = 1;

DELETE 1

SELECT * FROM foo;

foo_id
foo

2
bar_txt

SELECT 1

SELECT * FROM fooshadow;

foo_id
foo

1
foo_txt

SELECT 1