Re: insert waiting

Ulrich

Either remove the referential integrity constraint, or ask the list if
there is likely to be a way to make the lock non-exclusive, or some
other workaround.

I know that this has been mentioned as an issue before, and I can't
remember what the solution was.

John Sidney-Woollett

Ulrich Wisser wrote:

> Hi John,
>
> but how long is the exclusive lock taken? For the whole transaction?
> My jobs are quite big, and transactions usually take several minutes.
> I can not afford have all my other jobs stalled for that time.
>
> Ulrich
>
>> I believe foreign key constraints take an exclusive lock on the
>> parent. If you are inserting two child records that reference the same
>> parent (at the same time) one insert will block.
>>
>> John Sidney-Woollett
>>
>> Ulrich Wisser wrote:
>>
>>> Hi,
>>>
>>> after some more debugging I found that my application does on average
>>> 3 inserts per second. Which is not very fast, but fast enough for
>>> now. But now and then the whole insert proces will be stalled and
>>> drop to 0.1 inserts per second. In the output of "ps aux Op" I see
>>> postgres in the state "INSERT WAITING".
>>>
>>> I have some other insert/update process running, but not on the same
>>> table. I am absolutely sure, that my insert process is the only one
>>> working on that table.
>>>
>>> Can foreign keys block inserts?
>>>
>>> TIA
>>>
>>> Ulrich
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org