From: Steve Crawford <scrawford@pinpointresearch.com>
To: sad <sad@bankir.ru>
To: pgsql-sql@postgresql.org
Subject: Re: locks and triggers. give me an advice please
Date: Tue, 20 Jul 2004 11:30:19 -0700
Message-ID: <200407201130.19676.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <200407200936.44292.sad@bankir.ru>
References: <000001c46ddd$491501d0$c201a8c0@borderveygqj37>
<37d451f70407191529610a8eba@mail.gmail.com>
<200407200936.44292.sad@bankir.ru>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, sad@bankir.ru
Subject: Re: locks and triggers. give me an advice please
In-Reply-To: <200407201130.19676.scrawford@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox