Locks on FK Tables From Partitioning
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.
Aaron Sipser <ajsipser@gmail.com> writes:
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.
Addition of a foreign key constraint implies adding triggers to both sides
of the FK. Adding a new partition also adds a child foreign key
constraint, which I'm pretty sure requires its own trigger. So the F
table is going to need whatever lock strength is involved in CREATE
TRIGGER. I don't recall offhand what we use, but it would at least need
to block operations that might fire such a trigger.
regards, tom lane
On Tue, 8 Feb 2022 at 23:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Aaron Sipser <ajsipser@gmail.com> writes:
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.Addition of a foreign key constraint implies adding triggers to both sides
of the FK. Adding a new partition also adds a child foreign key
constraint, which I'm pretty sure requires its own trigger. So the F
table is going to need whatever lock strength is involved in CREATE
TRIGGER. I don't recall offhand what we use, but it would at least need
to block operations that might fire such a trigger.
Agreed that table F needs a trigger, but only for the first partition.
The trigger exists just once from the referenced table to the
referencing partitioned table, so we don't need to add that once per
partition.
ATAddForeignKeyConstraint() takes a ShareRowExclusiveLock to add the
needed triggers, but just assumes that it is needed for all cases.
ISTM that we could take a temporary/not-for-whole-transaction lock to
inspect whether the required trigger is already there and if so, avoid
taking the lock on the referenced table each time.
--
Simon Riggs http://www.EnterpriseDB.com/