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

Re: locks and triggers. give me an advice please

From: sad <sad(at)bankir(dot)ru>
To: Steve Crawford <scrawford(at)pinpointresearch(dot)com>,pgsql-sql(at)postgresql(dot)org
Subject: Re: locks and triggers. give me an advice please
Date: 2004-07-21 05:01:13
Message-ID: 200407210901.13421.sad@bankir.ru (view raw or flat)
Thread:
Lists: pgsql-sql
thnx.

i try to sound the idea to ensure myself that you are right.

> 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

in case another user inserts a record into an adjuscent table with the value 
of reference field NOT IN t1.
the constraint causes reading of t1 to look up FK value IN t1.
so my EXCLUSIVE lock prevents even reading and this user operation will be 
queued.




In response to

pgsql-sql by date

Next:From: Josh BerkusDate: 2004-07-21 07:00:59
Subject: Re: surrogate key or not?
Previous:From: azah azahDate: 2004-07-21 03:53:14
Subject: Re: date_format in postresql

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