From: Carlos Moreno <moreno_pg@mochima.com>
To: pgsql-performance@postgresql.org
Subject: Re: Insert performance
Date: Tue, 06 Mar 2007 12:54:44 -0500
Message-ID: <45EDAAE4.7000500@mochima.com> (raw)
In-Reply-To: <1173196944.9058.25.camel@coppola.muc.ecircle.de>
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>
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
--
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-performance@postgresql.org
Cc: moreno_pg@mochima.com
Subject: Re: Insert performance
In-Reply-To: <45EDAAE4.7000500@mochima.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