|From:||Andreas Karlsson <andreas(at)proxel(dot)se>|
|Subject:||Reducing lock strength of adding foreign keys|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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.
|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|