Handle foreign keys to dropped child tables

[keithf4]

MAINTAINER NOTE: Creating a new issue to discuss/track this

I am still extending my partitioning process and I found a new issue more or less related to this ticket.

When I detach my partitions (without dropping them) in this situation, it works but breaks my foreign keys because they still target the main table but the partitions are no longer present. I think I will call a function after detaching to remove all foreign keys from detached partitions.

If you want to handle this issue in a future version, it might be good to (optionally?) drop foreign keys on detached partitions.

_Originally posted by @axelfaure in https://github.com/pgpartman/pg_partman/issues/497#issuecomment-1363012216_

[keithf4]

So just to clarify, you had foreign keys coming INTO the partition set that were broken when child tables in that referenced partition set were removed? Or were the foreign keys FROM the partition set to another table?

[axelfaure]

In my case it was actually both. I was in a situation where table A referenced B and B referenced C (and so on). A, B and C were all partitioned by date an I periodically created new partitions and detached old ones.

In this case, I started by triggering run_maintenance on A (no problem at that point I ended up with A still referencing B and the now detached partitions from A also referencing B). Then I triggered run_maintenance on B and that is were things starting to be a bit more ugly. A was still referencing B and since the partitioning scheme was the same on both tables that part was coherent. But detached partitions from A also referenced B and that is the issue. The data actually referenced by these partitions was now in the detached partitions from B and no longer in B so the referential integrity was broken. And as I kept running run_maintenance on more tables the problem was becoming worse and worse.

I don't think there is actually an easy "catch all" solution to fully solve this problem since the issue mostly comes from the fact that I had FK coming INTO the maintained table and I don't think pg_partman should change these. However, I also think that statically speaking this partitioning scheme (partitioning several tables using the same key so that partition 1 in A actually references partition 1 in B) is not uncommon. Even more so when we focus on systems where partitions need to be automatically created/removed. As a result I think offering a way to automatically remove all FK from the detached partitions could help many users of pg_partman keeping their db structure coherent.

[keithf4]

So do you have the FK being done through the parent table? Or are you doing FK references directly between the children and not referencing the parent at all?

You are correct that this is not an easy situation and I've been trying to find ways to deal with this. One issue is that if you have FK references TO the partition set and the child table is dropped, if you do a DROP CASCADE, it completely drops the entire FK reference all together. This is something internally within PG, not something that partman does.

The only solutions I've found thus far to cleanly handling FK references TO a partition set is to first ensure all referencing data is deleted and then drop the child table. That is the only way to keep the partition-wide FK reference intact and avoid either FK integrity check errors or the entire reference being dropped when CASCADE is enabled.

[axelfaure]

In my case the FK were declared from a parent table to a parent table. Creating FK between specific partitions might be a solution to this issue but it does not seem that easy to automate.

The solution I found to my issue was to detach partition in the correct order (in my example first A, then B, then C) and to drop all FKs on the newly detached partition before detaching the next one.

That is not a good generic solution since it only work if :

1. there are no other references to this data in unpartitioned tables
2. there is no cyclic reference (in which case there wouldn't be a correct order)

But I think (and I might very well be wrong) that it could work 80% of the time on real world systems. Combined with an integrity check preventing the user to break its data for the remaining 20% it could still be an improvement.

In terms of new features, pg_partman would only need the ability to perform maintenance in a specified order (which you already implemented I think) and a flag that, if set, would drop the FK when detaching a partition (plus the integrity check if you want to add it).

[keithf4]

I do have the fixed maintenance order committed for the next release. I'll have to see about the FK option and how well it works in a general sense. Thanks for reporting the issues!