> 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
> 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;
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.)
commit; --end of transaction unlocks the table
In response to
pgsql-sql by date
|Next:||From: Lars Erik Thorsplass||Date: 2004-07-20 22:03:32|
|Subject: Re: [GENERAL] Stored procedures and "pseudo" fields..|
|Previous:||From: Peter Wang||Date: 2004-07-20 18:24:57|
|Subject: fail to compare between bytea output in plpgsql|