Postgres 13

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 users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE notes (
  id SERIAL PRIMARY KEY,
  user_id INT,
  content TEXT,
  CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE



CREATE TABLE


INSERT INTO users(name) VALUES 
  ('Alice'),
  ('Bob');
INSERT INTO notes(user_id, content) VALUES
  (1, 'Alice Note 1'),
  (1, 'Alice Note 2'),
  (2, 'Bob Note');

INSERT 0 2



INSERT 0 3


SELECT notes.*, users.name
FROM notes
INNER JOIN users
ON notes.user_id = users.id


id
user_id
content
name


1
1
Alice Note 1
Alice

2
1
Alice Note 2
Alice

3
2
Bob Note
Bob



SELECT 3


BEGIN;
UPDATE users SET id=3 WHERE id=1;
UPDATE notes SET user_id=3 WHERE user_id=1;
COMMIT;

BEGIN



ERROR:  update or delete on table "users" violates foreign key constraint "fk_user" on table "notes"
DETAIL:  Key (id)=(1) is still referenced from table "notes".