Re: Reducing lock strength of adding foreign keys - Mailing list pgsql-hackers

On 10/24/2014 06:07 PM, Robert Haas wrote:
> I think instead of focusing on foreign keys, we should rewind a bit
> and think about the locking level required to add a trigger.

Agreed.

> As far as triggers are concerned, the issue of skew between the
> transaction snapshot and what the ruleutils.c snapshots do seems to be
> the principal issue.  Commit e5550d5fec66aa74caad1f79b79826ec64898688
> changed pg_get_constraintdef() to use an MVCC snapshot rather than a
> current MVCC snapshot; if that change is safe, I am not aware of any
> reason why we couldn't change pg_get_triggerdef() similarly. Barring
> further hazards I haven't thought of, I would expect that we could add
> a trigger to a relation with only ShareRowExclusiveLock.

Thanks for the info. This is just the kind of issues I was worrying about.

> Anything
> less than ShareRowExclusiveLock would open up strange timing races
> around the firing of triggers by transactions writing the table: they
> might or might not notice that a trigger had been added before
> end-of-transaction, depending on the timing of cache flushes, which
> certainly seems no good.  But even RowExclusiveLock seems like a large
> improvement over AccessExclusiveLock.

Would not ShareLock give the same result, except for also allowing 
concurrent CREATE INDEX and concurrent other CREATE TRIGGER which does 
not look dangerous to me?
From a user point of view ShareRowExclusiveLock should be as useful as 
ShareLock.

> When a constraint trigger - which is used to implement a foreign key -
> is added, there are actually TWO tables involved: the table upon which
> the trigger will actually fire, and some other table which is
> mentioned in passing in the trigger definition.  It's possible that
> the locking requirements for the secondary table are weaker since I
> don't think the presence of the trigger actually affects runtime
> behavior there.  However, there's probably little point in try to
> weaken the lock to less than the level required for the main table
> because a foreign key involves adding referential integrity triggers
> to both tables.
>
> So I tentatively propose (and with due regard for the possibility
> others may see dangers that I've missed) that a reasonable goal would
> be to lower the lock strength required for both CREATE TRIGGER and ADD
> FOREIGN KEY from AccessExclusiveLock to ShareRowExclusiveLock,
> allowing concurrent SELECT and SELECT FOR SHARE against the tables,
> but not any actual write operations.

Agreed.. But I think reducing the lock level of the secondary table is 
much more important than doing the same for the primary table due to the 
case where the secondary table is an existing table which is hit by a 
workload of long running queries and DML while the primary is a new 
table which is added now. In my dream world I could add the new table 
without any disruption at all of queries using the secondary table, no 
matter the duration of the transaction adding the table (barring 
insertion of actual data into the primary table, which would take row 
locks).

This is just a dream scenario though, and focusing on triggers is indeed 
the reasonable goal for 9.5.

-- 
Andreas Karlsson