Locks on FK Tables From Partitioning

[Aaron Sipser]

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.

Question on tablefunc extension

[Lu, Dan]

Hello,

We downloaded postgresql software from this site, 
https://www.postgresql.org/ftp/source/v12.1/, as a tar ball file 
(postgresql-12.1.tar.bz2).  So we didn't do install the 'postgresql-contrib' 
option.

Our developers asked that we add on the "tablefunc" extension to the existing 
postgresql instance.  It appears there is no source code (*.tar.gz file) for 
"tablefunc".  Is that correct?

What is the easiest way to get the "tablefunc" installed as an extension to the 
existing postgresql instance?

Thanks for your help!

Dan



IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.

Re: Question on tablefunc extension

[Adrian Klaver]

On 2/8/22 09:30, Lu, Dan wrote:

Hello,

We downloaded postgresql software from this site, 
https://www.postgresql.org/ftp/source/v12.1/ 
, as a tar ball file 
(postgresql-12.1.tar.bz2).  So we didn’t do install the 
'postgresql-contrib' option.


Our developers asked that we add on the “tablefunc” extension to the 
existing postgresql instance.  It appears there is no source code 
(*.tar.gz file) for “tablefunc”.  Is that correct?


It is there under the contrib/ directory wherever you unpacked 
postgresql-12.1.tar.bz2.





What is the easiest way to get the “tablefunc” installed as an extension 
to the existing postgresql instance?


Thanks for your help!

Dan





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Locks on FK Tables From Partitioning

[Tom Lane]

Aaron Sipser  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