Re: [PATCHES] enable/disable trigger (Re: Fwd: Open items)

Satoshi Nagayasu wrote:
> Bruce Momjian wrote:
> > I am not sure what to do with this patch. It is missing dump
> > capability, there is no clause to disable all triggers on a table, and
> > it uses a table owner check when a super user check is required (because
> > of referential integrity).
> >
> > Satoshi, are you still working on it? If not does someone else want to
> > complete it? I realized you just started on it but the deadline is
> > soon.
>
> I've already implemented 'ENABLE/DISABLE TRIGGER ALL',
> and the superuser check has been added. Please check it.
>
> And, I'm going to have a business trip to Sydney this weekend,
> so I'll complete pg_dump stuffs while my flight.

There are several issues with disabling triggers, and I thought I would
outline them before we add something to 8.1. The functionality that has
been discussed is:

o disable a single trigger (in patch)
o disable all user-defined triggers
o disable all referential integrity constraint triggers
o disable all triggers on a table (in patch)
o disable triggers on all tables

The first four are via ALTER TABLE ENABLE/DISABLE TRIGGER, and the last
would be via SET. (Some think the last item is too powerful.)

There is also the issue of whether table owners can perform these
operations, or just super-users. There is general agreement that table
owners should be able to disable user-defined triggers, but not
referential integrity triggers, because they might not own the
referenced table.

The current patch makes no disinction between user-defined triggers and
referential integrity triggers. It allows either a single trigger to be
disabled, or all triggers on a table, and it can be done only by the
super-user, via ALTER TABLE.

The big question is what functionality we want in 8.1, and whether
enhancements in later releases will conflict with our chosen syntax.

The current super-user only behavior can be relaxed in later releases as
appropriate. The ALL keyword applying to all triggers is probably good.
We might add a USER later (or in 8.1) like this:

ALTER TABLE ENABLE/DISABLE TRIGGER USER

as distinct from

ALTER TABLE ENABLE/DISABLE TRIGGER ALL

Oh, and one trick for disabling triggers in a single session is to do
this:

BEGIN WORK;
ALTER TABLE xx DISABLE TRIGGER ALL
...
ALTER TABLE xx ENABLE TRIGGER ALL
COMMIT WORK;

In this case, the triggers are disabled just for that session. I think
this usage should be documented in our manual.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073