Locks on FK Tables From Partitioning

From: Aaron Sipser <ajsipser(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Locks on FK Tables From Partitioning
Date: 2022-02-08 16:51:09
Message-ID: CACjAnu3oeETOhD0tDLJoJmS7yJZBWEij3qKhxafdXKOuCYzPSw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lu, Dan 2022-02-08 17:30:41 Question on tablefunc extension
Previous Message Tom Lane 2022-02-06 22:57:10 Re: Undetected Deadlock