Message-ID: <45EDAAE4.7000500@mochima.com>
Date: Tue, 06 Mar 2007 12:54:44 -0500
From: Carlos Moreno <moreno_pg@mochima.com>
User-Agent: Mozilla Thunderbird 1.0.8-1.1.fc4 (X11/20060501)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Insert performance
References: <1172596221.393824.41130@j27g2000cwj.googlegroups.com>	
	<45ED1996.7050008@archonet.com>
	<1173167607.4917.8.camel@hatman>	 <45ED217D.30404@archonet.com>
	<1173172748.4917.23.camel@hatman>	
	<45ED37D1.3000503@archonet.com> <45ED8EFD.5030908@mochima.com>
	<1173196944.9058.25.camel@coppola.muc.ecircle.de>
In-Reply-To: <1173196944.9058.25.camel@coppola.muc.ecircle.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Csaba Nagy wrote:

>I only know to answer your no. 2:
>  
>
>>2) What about the issue with excessive locking for foreign keys when
>>inside a transaction?  Has that issue disappeared in 8.2?  And if not,
>>would it affect similarly in the case of multiple-row inserts?
>>    
>>
>
>The exclusive lock is gone already starting with 8.0 IIRC, a
>non-exclusive lock on the parent row is used instead. Thing is that this
>is still too strong ;-)
>
>The proper lock would be one which only prevents modification of the
>parent key, other updates would be safe on the same row.
>
>In any case, the current behavior is much better than what was before.
>  
>

*Much* better, I would say --- though you're still correct in that it is 
still
not the right thing to do.

In particular, with the previous approach. there was a serious performance
hit when concurrent transactions reference the same keys --- that is, after
having taken measures to avoid deadlocks, some transactions would have
to *wait* (for no good reason) until the other transaction is completed and
the exclusive-access lock is released.  For high-traffic databases this 
can be
a quite severe performance hit.  I'm glad it has been fixed, even if only
partially.

Thanks,

Carlos
--