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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
