> often, I am turning triggers off and on to perform a mass operation
> on a table, and i am interested how should i care of another user
> operations.
>
> the scene is:
> table t1 with user defined triggers
> and many tables reference t1, (so FK triggers defined on t1)
>
> the operation i want to perform on t1 makes a great load to a
> server and have no use in triggers at all.
> the best way to perform this operation is to delete all records,
> modify, and insert them back without changing any adjuscent table.
> (this way takes a few seconds.)
> so i turn off triggers on t1 completely (updating
> pg_class.reltriggers) operate
> and turn on triggers on t1.
>
> it works fine.
>
> the question is:
>
> what should i do to prevent other users of data modification on the
> t1 and the adjuscent tables while triggers is off ?
If I understand your question correctly you should use a transaction
and lock the table;
begin transaction;
lock t1 in access exclusive mode;
Turn off triggers and do your updates.
(Note, "truncate t1" is faster than "delete from t1" followed by a
"vacuum full" and you might consider running "reindex table t1" after
your mass update or if appropriate drop your indexes, load the data,
then recreate them.)
Re-establish triggers.
commit; --end of transaction unlocks the table
Cheers,
Steve
