Re: Reducing lock strength of adding foreign keys

On Mon, Oct 27, 2014 at 08:24:15AM -0400, Robert Haas wrote:
> 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?

ALTER TRIGGER is not bad; like you say, change pg_get_triggerdef_worker() the
way commit e5550d5 changed pg_get_constraintdef_worker(). DROP TRIGGER is
more difficult. pg_constraint.tgqual of a dropped trigger may reference other
dropped objects, which calls for equipping get_rule_expr() to use the
transaction snapshot. That implicates quite a bit of ruleutils.c code.