Postgres 11

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

DROP TABLE IF EXISTS archive;
DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS parent;
DROP FUNCTION IF EXISTS doarchive();

CREATE TABLE parent (
	id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	data VARCHAR(255)
);
CREATE TABLE child (
	id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	parentid INT,
	data VARCHAR(255),
	CONSTRAINT fk_parent_child FOREIGN KEY(parentid) REFERENCES parent(id) ON DELETE CASCADE
);
CREATE TABLE archive (
	id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	parentid INT,
	parentdata VARCHAR(255),
	childdata VARCHAR(255)
);

INSERT INTO parent(data) VALUES ('apple'),('banana'),('cherry');
INSERT INTO child(parentid,data) VALUES (1,'accordion'),(2,'banjo'),(3,'cor anglais');

CREATE FUNCTION doarchive() RETURNS trigger LANGUAGE plpgsql AS
$fn$ BEGIN
	INSERT INTO archive(parentid,parentdata,childdata)
	VALUES(
		old.id,old.data,
		(SELECT data FROM child WHERE child.parentid=old.id)
	);
	RETURN old;
END; $fn$;

CREATE TRIGGER archivetrigger
	BEFORE DELETE ON parent
	FOR EACH ROW
	EXECUTE FUNCTION doarchive();

DELETE FROM parent WHERE data='banana';
SELECT * FROM parent;
SELECT * FROM child;
SELECT * FROM archive;

3 rows affected

3 rows affected

1 rows affected

id	data
1	apple
3	cherry

id	parentid	data
1	1	accordion
3	3	cor anglais

id	parentid	parentdata	childdata
1	2	banana	banjo