[snip]
> If I understand you right, if I were to insert the records ordered by the
> child foreign key (since the parent is unique between runs), this would
> eliminate the deadlock. I'm assuming the lock is retained until the
> transaction is complete?
>
That's correct.
> Since all 10,000 records are a single transaction (if one fails, all must
> fail), and it is almost certain that two loads will have common child
> records, it sounds like even with ordered records I have almost no
> concurrency. Once a collision occurred, process 2 would wait on process 1
> to complete. I might as well just grab an exclusive lock on the table when
> loading it?
>
That's correct too. However, if you break your import (I assume it's a
data import) in smaller chunks of transactions, say 100 inerts per
transaction, and perhaps pre-ordering the data so each chunk refers the
same parent foreign key, that could make a better concurrency.
> I'd prefer to avoid one-off patches, as in a new installation that is likely
> to be overlooked.
>
That' correct too. I have applied a patch myself for this FK issue
(which I won't publish BTW cause it's tailored to our application's
needs), and it is an extra effort to apply it to new versions when
upgrading. I'm just doing it for 7.4.2, nothing hard, but not trivial
either.
I just hope that at one time I can just drop this patch cause the issue
will be fixed properly in the postgres sources... :-)
Cheers,
Csaba.
