Re: Insert performance

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
--