truncate partitions with foreign keys without cascade in postgresql

Question

I have a schema like this:

create table thing
(
    section  uuid not null,
    thing_id uuid not null,
    alias_of uuid default null,
    
    constraint alias_ref foreign key (section, alias_of)
        references thing (section, thing_id),
        
    primary key (section, thing_id)
) partition by list (section);

create table dependent_thing
(
    section            uuid not null,
    dependent_thing_id uuid not null,
    depends_on_thing   uuid not null,
    alias_of           uuid default null,

    constraint thing_ref foreign key (section, depends_on_thing)
        references thing (section, thing_id),
    constraint alias_ref foreign key (section, alias_of)
        references dependent_thing (section, dependent_thing_id),

    primary key (section, dependent_thing_id)
) partition by list (section);

So there are dependent_things which may reference things, and both things and dependent_things may reference themselves.

These tables are partitioned and as can be seen from the foreign key definitions they only ever reference things within their same partition.

However if I `truncate _thing_partitition_63df23f7b60d3345824bfe726cade143 cascade' it will take down all data from all sections from all partitions.

I know that if I were to remove foreign keys, detach the partitions, truncate their contents – I'd be fine. However I am wondering whether there is a smarter way to do this? Theoretically postgres should be able to prove that I am just pruning a partition. If I were to truncate _thing_partition_123 _dependent_thing_partition_123 it should remove all data for section 123, not for any other section; as the foreign keys are limited to the sections by which we partition.

Answer 1

If you have a foreign key pointing to a partitioned table, you cannot truncate, drop or detach a partition. But you can do all of these things if you have foreign key constraints between the individual partitions...