0

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.

scravy
  • 11,904
  • 14
  • 72
  • 127
  • I think you can use "DISABLE TRIGGER ALL;", but I don't like this method :( – user_0 Sep 05 '22 at 09:23
  • @user_0 There's a zillion reasons why I don't like this approach and why I event can't do it. First of all I'm on AWS/RDS and it doesn't allow me to do so as I am not `rds_superuser`. Second I would have to to `ALTER TABLE DISABLE TRIGGER ...` on all tables which reference my table. This in turn requires acquiring locks for all those tables and the way the declarative partitioning works it requires me to do that on the parent – boom. This way I am blocking (or am blocked) by basically any other process (and there is always smth running - however, there's not always smth running on my section). – scravy Sep 06 '22 at 12:20

1 Answers1

0

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

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    Hm. I see your point. I tried to drop the foreign key of just one of the partitions but it didn't work as it is constraint from the parent table. We're already creating the partitions automatically, so we could probably also take care of the foreign keys programatically – is that what you are suggesting? It's a bit ugly and something which I'd really expect postgres to handle. I am one step away from just creating different schemas per `section` and replicating it from a template schema or something like that. – scravy Sep 06 '22 at 12:08
  • Yes, you should drop the foreign key constraint on the partitioned table and create foreign keys on the partitions. You can do without partitioning, but remember the advantages: you can query from all partitions in one simple query, and with the correct `WHERE` condition PostgreSQL can automatically figure out which partitions to scan. – Laurenz Albe Sep 06 '22 at 15:24