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 a(i integer PRIMARY KEY);
CREATE TABLE b(
  j integer
, CONSTRAINT fkey_ij FOREIGN KEY (j) REFERENCES a (i) 
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE

CREATE TABLE

-- simple fix 1.: INSERT in order
INSERT INTO a(i) VALUES(2);
INSERT INTO b(j) VALUES(2);

INSERT 0 1

INSERT 0 1

-- simple fix 2: single command
WITH ins1 AS (
   INSERT INTO b(j) VALUES(3)
   )
INSERT INTO a(i) VALUES(3);

INSERT 0 1

-- ... without repeating input values
WITH ins1 AS (
   INSERT INTO b(j) VALUES(4)
   RETURNING j
   )
INSERT INTO a(i)
SELECT j FROM ins1;

INSERT 0 1

TABLE b;

j

2

3

4

SELECT 3

BEGIN;  -- original: fails
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO b(j) VALUES(2);
INSERT INTO a(i) VALUES(2);
COMMIT;

BEGIN

SET CONSTRAINTS

INSERT 0 1

ERROR:  duplicate key value violates unique constraint "a_pkey"
DETAIL:  Key (i)=(2) already exists.

ROLLBACK;

ROLLBACK

DROP TABLE b,a;
CREATE TABLE a(i integer PRIMARY KEY);
CREATE TABLE b(
  j integer
, CONSTRAINT fkey_ij FOREIGN KEY (j) REFERENCES a (i) 
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE  -- !!!
);

DROP TABLE

CREATE TABLE

CREATE TABLE

-- now original works, too
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO b(j) VALUES(2);
INSERT INTO a(i) VALUES(2);
COMMIT;

BEGIN

SET CONSTRAINTS

INSERT 0 1

INSERT 0 1

COMMIT

TABLE b;

j

2

SELECT 1