Re: Problem with disabling triggers in pg_dump

At 15:52 25/07/00 +0200, Jan Wieck wrote:
>Philip Warner wrote:
>>
>> An interesting problem has been brought to my attention in pg_dump
>> (V7.0.2+, I think).
>>
>> It uses the following code to disable triggers prior to a data load:
>>
>>     UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
>>
>> which works fine if it is currently connected as a superuser, or as the
>> datdba. However, if it is connected as anybody else, they will get the
error:
>>
>>     pg_class: Permission denied
>>
>> The obvious solution is to reconnect as the datdba before running the code.
>
>    The clean solution would be to have something like
>
>        ALTER SESSION DISABLE/ENABLE TRIGGERS

Unfortunately, I am trying to keep the new pg_dump working with 7.0.2 as
well, so I need a solution for the old hack as well as the new priv model...


But, as far as 7.1 goes, this sounds like the way to go. FWIW, we'd also
want to consider: 
   ALTER SEESION DISABLE CONSTRAINTS etc etc.

plus even,        ALTER TRIGGER <name> on <table> DISABLE 
or       ALTER TABLE <name> DISABLE TRIGGERS
or (to avoid messing with valid DDL),        ALTER SESSION DISABLE TRIGGER <trig-name>        ALTER SESSION DISABLE
TRIGGERSON <rel-name>        ALTER SESSION DISABLE ALL TRIGGERS
 

>    A  global  variable  (like  those for other session settings)
>    would cause the trigger manager to suppress their  invocation
>    at all.

Maybe it would be best stored on a per-trigger basis.


>    The new utility has to check if either the user has ALTER ALL
>    TABLES privilege,  or  has  ALTER  TABLE  privilege  for  any
>    existing user table where triggers are defined for.

Or maybe 'ALTER' priv on the individual 'TRIGGER' objects...since I would
not necessarily consider a trigger subject to the ALTER TABLE rules.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/