Postgres 14

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

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


    create table chained_records (
       id uuid not null primary key default gen_random_uuid(),
       -- direct ancestor
       direct_ancestor_id uuid not null,
       -- top ancestor
       first_in_chain_id uuid not null,
       first_in_chain_ancestor uuid GENERATED ALWAYS AS (first_in_chain_id) STORED,
       -- UNIQUE constraint needed for the following FK
       constraint transitive_closure_UQ
       unique 
           (id, direct_ancestor_id, first_in_chain_id),
       -- transitive closure FK
       constraint transitive_closure_FK 
       foreign key 
           (direct_ancestor_id, first_in_chain_id, first_in_chain_ancestor)
       references chained_records
           (id, first_in_chain_id, direct_ancestor_id)
       -- snip
    );

CREATE TABLE


with new_id (aid, bid, tid) as (
  values (gen_random_uuid(), gen_random_uuid(), gen_random_uuid())
)
insert into chained_records (id, direct_ancestor_id, first_in_chain_id)
select tid, tid, tid from new_id
union all
select gen_random_uuid(), bid, tid from new_id
union all
select bid, tid, tid from new_id ;

INSERT 0 3


select * from chained_records ;
delete from chained_records ;


id
direct_ancestor_id
first_in_chain_id
first_in_chain_ancestor


afd27d85-3c25-4169-a3a8-6bba6d44b555
afd27d85-3c25-4169-a3a8-6bba6d44b555
afd27d85-3c25-4169-a3a8-6bba6d44b555
afd27d85-3c25-4169-a3a8-6bba6d44b555

4a4c8bd4-18c8-4c38-9d9e-649fccd64a6e
d3e5d74f-e20f-4cf4-abb8-53c9d1f86265
afd27d85-3c25-4169-a3a8-6bba6d44b555
afd27d85-3c25-4169-a3a8-6bba6d44b555

d3e5d74f-e20f-4cf4-abb8-53c9d1f86265
afd27d85-3c25-4169-a3a8-6bba6d44b555
afd27d85-3c25-4169-a3a8-6bba6d44b555
afd27d85-3c25-4169-a3a8-6bba6d44b555



SELECT 3



DELETE 3


with new_id (aid, bid, tid) as (
  values (gen_random_uuid(), gen_random_uuid(), gen_random_uuid())
)
insert into chained_records (id, direct_ancestor_id, first_in_chain_id)
select tid, tid, tid from new_id
union all
select aid, bid, tid from new_id
union all
select bid, aid, tid from new_id ;

ERROR:  insert or update on table "chained_records" violates foreign key constraint "transitive_closure_fk"
DETAIL:  Key (direct_ancestor_id, first_in_chain_id, first_in_chain_ancestor)=(f0273696-e7ee-45ac-a89c-5cf8510d0113, 0fb19975-aff1-43cd-bfc7-393f400b2407, 0fb19975-aff1-43cd-bfc7-393f400b2407) is not present in table "chained_records".


select * from chained_records ;
delete from chained_records ;


id
direct_ancestor_id
first_in_chain_id
first_in_chain_ancestor



SELECT 0



DELETE 0


with new_id (aid, bid) as (
  values (gen_random_uuid(), gen_random_uuid())
)
insert into chained_records (id, direct_ancestor_id, first_in_chain_id)
select aid, bid, aid from new_id
union all
select bid, aid, aid from new_id ;

ERROR:  insert or update on table "chained_records" violates foreign key constraint "transitive_closure_fk"
DETAIL:  Key (direct_ancestor_id, first_in_chain_id, first_in_chain_ancestor)=(c9597bc0-39cd-4640-84df-47567b30d93f, c9597bc0-39cd-4640-84df-47567b30d93f, c9597bc0-39cd-4640-84df-47567b30d93f) is not present in table "chained_records".


select * from chained_records ;


id
direct_ancestor_id
first_in_chain_id
first_in_chain_ancestor



SELECT 0

id	direct_ancestor_id	first_in_chain_id	first_in_chain_ancestor
afd27d85-3c25-4169-a3a8-6bba6d44b555	afd27d85-3c25-4169-a3a8-6bba6d44b555	afd27d85-3c25-4169-a3a8-6bba6d44b555	afd27d85-3c25-4169-a3a8-6bba6d44b555
4a4c8bd4-18c8-4c38-9d9e-649fccd64a6e	d3e5d74f-e20f-4cf4-abb8-53c9d1f86265	afd27d85-3c25-4169-a3a8-6bba6d44b555	afd27d85-3c25-4169-a3a8-6bba6d44b555
d3e5d74f-e20f-4cf4-abb8-53c9d1f86265	afd27d85-3c25-4169-a3a8-6bba6d44b555	afd27d85-3c25-4169-a3a8-6bba6d44b555	afd27d85-3c25-4169-a3a8-6bba6d44b555