citus icon indicating copy to clipboard operation
citus copied to clipboard

Non blocking shard moves with foreign keys between tables owned by different users have issues

Open JelteF opened this issue 3 years ago • 0 comments

@marcocitus noticed this here https://github.com/citusdata/citus/pull/6119#discussion_r938660989

While going over this code again, I realized an issue with foreign keys between distributed tables (unrelated to this PR):

We currently create foreign keys between reference tables in CreatePostLogicalReplicationDataLoadObjects. However, since we could have multiple subscriptions, the changes might not be synchronized and foreign keys could be violated on the target.

That might also cause issues with truncate: https://www.postgresql.org/docs/current/logical-replication-restrictions.html

In addition, we currently check the whole foreign key duration creation even though we know it to be valid on the source.

We should probably handle foreign keys between distributed tables in the same way as we do foreign keys to reference tables: Create them all the way at the end after the last catchup and and skip validation.

JelteF avatar Aug 08 '22 13:08 JelteF