From: Steve Crawford <scrawford@pinpointresearch.com>
Organization: Pinpoint Research
To: sad <sad@bankir.ru>, pgsql-sql@postgresql.org
Subject: Re: locks and triggers. give me an advice please
Date: Tue, 20 Jul 2004 11:30:19 -0700
User-Agent: KMail/1.5.1
References: <000001c46ddd$491501d0$c201a8c0@borderveygqj37>
	<37d451f70407191529610a8eba@mail.gmail.com>
	<200407200936.44292.sad@bankir.ru>
In-Reply-To: <200407200936.44292.sad@bankir.ru>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200407201130.19676.scrawford@pinpointresearch.com>

> 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