insert waiting

[Ulrich Wisser]

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

[John Sidney-Woollett]

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 4: Don't 'kill -9' the postmaster

[Ulrich Wisser]

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

[Bruno Wolff III]

On Fri, Aug 20, 2004 at 13:20:49 +0200,
Ulrich Wisser <ul***********@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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[John Sidney-Woollett]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster