Hi,
I am fairly confused about the locks generated by some partitioning code I am using. The setup is that we have a partitioned table (call it P), and it has a foreign key constraint to another table F. I'm trying to figure out why when I add partitions to P, it seems that a lock is also taken on F.
To add partitions to P we run the following commands:
- create table p_partition (LIKE P INCLUDING ...)
- alter table p_partition add constraint [on the partition range]
- alter table P attach partition p_partition for values (...)
My understanding is that this operation would only take a ShareUpdateExclusive lock on table P.
What I'm seeing is that this also takes ShareRowExclusive lock on the foreign key table. Is there a reason for this? Is it taking this lock on all rows of the foreign key table? This is causing deadlock in our code, and I am not sure if there is a better practice for defining partitions or some mechanism to prevent taking the Row level lock on the FK table. We always know that the partition we are adding has no data in it at the time of attachment, if that helps. This is also being run on postgres 12.2.
Thanks,
Aaron.