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 TRIGGERS ON <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 |/