This question is for postgres 12 or 13.
I'm working on a large database where we need to partition several tables. The problem is that there are foreign keys between those tables.
To make it easy to understand, let's imagine the application is like stackoverflow. We have a Questions table, Answers table that refer to questions and a Comments table that refer to answers.
Question table will have monthly partitions. Answers and comments could be added several months after the related question have been created. When we detach the Question table, we want to remove all related answers and comments at the same time to keep data consistency. Therefore we have to insert comments and answers to a partition based on the related question timestamp. So we have Questions_2021_03 Answer_2021_03 Comments_2021_03 (even if answers and comments could be for a later date).
This could be done by adding a question_date field in Answers and Comments tables and handling that value on application side.
Is it possible to handle this transparently on database side with triggers ? The documentation for pg13 says that before row trigger could not change the field upon which partition is based. But it also says that row will move to another partition when partition key is updated (is documentation up to date).
Also I'm wondering how to ensure partitions for the 3 tables are detached in same atomic operation. So there are no dangling pointers, even for brief period of time.
Edit: So far I have made some tests. I confirm it's not possible to copy the timestamp in a before insert trigger. So we must handle this on application side. Another solution would be to drop the autoincrement ID and use only timestamp as a primary key, but it looks like it's a really bad idea.