Postgres 10

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 ( x int PRIMARY KEY );
CREATE TABLE bar (
  x int,
  CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x) ON DELETE CASCADE
);
INSERT INTO foo VALUES (3), (45), (7);
3 rows affected

INSERT INTO bar VALUES(3), (3), (45), (7), (7);
5 rows affected

SELECT * FROM foo;

x

3

45

7

SELECT * FROM bar;

x

3

3

45

7

7

DELETE FROM foo WHERE x = 3;
1 rows affected

DROP TABLE foo;
ERROR:  cannot drop table foo because other objects depend on it
DETAIL:  constraint asdf on table bar depends on table foo
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

SELECT * FROM foo;

x

45

7

SELECT * FROM bar;

x

45

7

7