Re: Reducing lock strength of adding foreign keys

Thanks for weighing in, Noah.

On Sat, Oct 25, 2014 at 2:00 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
>> 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().)

Maybe so, but I'd favor changing it anyway and getting it over with.
The current situation seems to have little to recommend it; moreover,
it would be nice, if it's possible and safe, to weaken the lock levels
for all three of those commands at the same time. Do you see any
hazards for ALTER or DROP that do not exist for CREATE?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company