Обсуждение: insert waiting
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
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
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
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.
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
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.