Re: Reducing lock strength of adding foreign keys

On Fri, Oct 24, 2014 at 12:07:42PM -0400, 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.

> http://www.postgresql.org/message-id/CA+TgmoY4GLsXZk0tAO29-LJtcuj0SL1xWCwQ51xb-HFYsgi5RQ@mail.gmail.com
> http://www.postgresql.org/message-id/20893.1393892127@sss.pgh.pa.us
> http://www.postgresql.org/message-id/20140306224340.GA3551655@tornado.leadboat.com
>
> 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.

pg_get_triggerdef() is fine as-is with concurrent CREATE TRIGGER. The
pg_get_constraintdef() change arose to ensure a consistent result when
concurrent ALTER TABLE VALIDATE CONSTRAINT mutates a constraint definition.
(Reducing the lock level of DROP TRIGGER or ALTER TRIGGER, however, would
create the analogous problem for pg_get_triggerdef().)

> 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.

+1