Thread: insert waiting

insert waiting

From
Ulrich Wisser
Date:
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


Re: insert waiting

From
John Sidney-Woollett
Date:
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

Re: insert waiting

From
Ulrich Wisser
Date:
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



Re: insert waiting

From
Bruno Wolff III
Date:
On Fri, Aug 20, 2004 at 13:20:49 +0200,
  Ulrich Wisser <ulrich.wisser@relevanttraffic.se> 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.

Once a lock is taken by a transaction, it is held until the end of that
transaction.

Re: insert waiting

From
John Sidney-Woollett
Date:
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

Re: insert waiting

From
Stephan Szabo
Date:
On Fri, 20 Aug 2004, Ulrich Wisser wrote:

> but how long is the exclusive lock taken? For the whole transaction?

Until transaction end.

> My jobs are quite big, and transactions usually take several minutes.
> I can not afford have all my other jobs stalled for that time.

Generally right now your options are:
 Remove the constraint
 Edit the code to remove the FOR UPDATE clauses used in the constraint
 Defer the constraint (thus the locks are only taken at the end for
  pretty much the time to check the constraints).  This however means
  that you're accumulating the deferred constraint information which
  takes RAM.

None of these are complete fixes.  The complete fix is changing the
constraint to use a different locking mechanism (that doesn't currently
exist). Another partial fix that's been described to me would be to see if
strengthening the locks on actions on the referenced table would allow
weakening of the locks on the referencing table.