Re: Fwd: question on foreign key lock

On 2012-12-05 17:05:41 -0500, Robert Haas wrote:
> On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski
> <filip(dot)rembialkowski(at)gmail(dot)com> wrote:
> > Robert, thank you for the answer.
> >
> > 1. "need exclusive lock anyway to add triggers".
> > Why adding a trigger needs exclusive lock?
> > Someone would say blocking reads is not needed (since possible trigger
> > events are: Insert/Update/Delete/Truncate).
> >
> > 2. "will create a risk of deadlock".
> > From user perspective a risk of deadlock is sometimes better than
> > excessive locking. Transactional DDL users should be prepared for
> > exceptions/retries anyway.
> >
> > 3. I made a naive test of simply changing AccessExclusiveLock to
> > ExclusiveLock, and seeing how many regression tests it breaks. It
> > breaks none :-)
> > Current Git head gives me 2 fails/133 tests regardless of this change.
>
> Sure. You could probably downgrade it quite a bit further without
> breaking the regression tests, but that doesn't mean it's safe in all
> cases. Rather than having this discussion all over again, I suggest
> that you have a look at commits
> 2dbbda02e7e688311e161a912a0ce00cde9bb6fc,
> 2c3d9db56d5d49bdc777b174982251c01348e3d8,
> a195e3c34f1eeb6a607c342121edf48e49067ea9, and the various mailing list
> discussions pertaining thereto, particularly the thread "ALTER TABLE
> lock strength reduction patch is unsafe" which was started by Tom
> Lane.

Just to give an example about the complexities surrounding this:
Lowering the lock level for foreign key creation probably would be
dangerous for query planning more precisely join removal.

S1: BEGIN TRANSACTION ISOLATION LEVEL REPATABLE READ;
S1: SELECT * FROM a;

S2: DELETE FROM a WHERE a.id IN (all_duplicate_ids);
S2: ALTER TABLE a ADD CONTSTRAINT a_unique UNIQUE (a);
S2: ALTER TABLE b ADD CONSTRAINT b_fkey FOREIGN KEY (b_id) REFERENCES a(id));

S1: SELECT b.id FROM a LEFT JOIN b ON(b.id = a.id);

The last S1 query might now remove the join to b because of the foreign
key (which it sees due to SnapshotNow semantics) although rows that
violate unique key (which is required for the foreign key) still
exist. The existance of those duplicate values would change the result
though!

(come to think of it, I think we might still hit the above case if S1
doesn't access a before the foreign key gets altered...)

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services