Reducing lock strength of adding foreign keys

From: Andreas Karlsson <andreas(at)proxel(dot)se>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Reducing lock strength of adding foreign keys
Date: 2014-10-22 07:06:52
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


I have been thinking about why we need to grab an AccessExclusiveLock on
the table with the PK when we add a foreign key. Adding new tables with
foreign keys to old ones is common so it would be really nice if the
lock strength could be reduced.

A comment in the code claims that we need to lock so no rows are deleted
under us and that adding a trigger will lock in AccessExclusive anyway.
But with MVCC catalog access and the removal of SnapshotNow I do not see
why adding a trigger would require an exclusive lock. Just locking for
data changes should be enough.

Looking at the code the only see the duplicate name check use the fact
that we have grabbed an exclusive lock and that should work anyway due
to the unique constraint, but since I am pretty new to the code base I
could be missing something obvious. I have attached a proof of concept
patch which reduces the lock strength to ShareLock.

What do you say? Am I missing something?

My gut feel also says that if we know it is a RI trigger we are adding
then AccessShare should be enough for the PK table, since we could rely
on row locks to prevent rows from being deleted. It would be really nice
though if this was possible since this would make it possible to add a
new table with foreign keys and data without locking anything more than
the referred rows.


Attachment Content-Type Size
add-fk-lock-strength.patch text/x-patch 6.3 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Meskes 2014-10-22 08:00:23 Re: Proposal for better support of time-varying timezone abbreviations
Previous Message Heikki Linnakangas 2014-10-22 06:56:02 Re: wal-size limited to 16MB - Performance issue for subsequent backup