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/