Skip site navigation (1) Skip section navigation (2)

Re: insert waiting

From: John Sidney-Woollett <johnsw(at)wardbrook(dot)com>
To: Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)se>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: insert waiting
Date: 2004-08-20 15:43:14
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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?

In response to

pgsql-general by date

Next:From: OlegDate: 2004-08-20 15:46:05
Subject: problem with postgresql-dump while upgrading to 7.4 format
Previous:From: the inquirerDate: 2004-08-20 15:33:23
Subject: Authentication problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group