Skip site navigation (1) Skip section navigation (2)

Re: locks and triggers. give me an advice please

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: sad <sad(at)bankir(dot)ru>, pgsql-sql(at)postgresql(dot)org
Subject: Re: locks and triggers. give me an advice please
Date: 2004-07-20 18:30:19
Message-ID: 200407201130.19676.scrawford@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-sql
> 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


In response to

Responses

pgsql-sql by date

Next:From: Lars Erik ThorsplassDate: 2004-07-20 22:03:32
Subject: Re: [GENERAL] Stored procedures and "pseudo" fields..
Previous:From: Peter WangDate: 2004-07-20 18:24:57
Subject: fail to compare between bytea output in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group