Process locked in INSERT: how to debug?

[Philippe Lang]

Hello,

What is the best method to debug a database that *sometimes* gets locked
in the INSERT mode? ('ps -afx | grep postgres' shows me a process that
is freezed in INSERT mode.)

Thanks

Philippe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Csaba Nagy]

Hi Philippe,

I can't answer your actual question, but just a hint: postgres is
deadlock-prone on inserts into tables referencing other tables via
foreign keys. This is a known limitation of postgres, it places an
exclusive lock on the referenced rows. For details search the archives
for "foreign key deadlock", there's plenty of discussions about it.
So my personal suspect nr. 1. would be the foreign keys.
If that's the problem, you can solve it in a number of ways:
- serialize your inserts, as the problem appears only for concurrent
inserts - this is probably the least viable option;
- make sure you order your inserts so that they insert the rows in the
same order of referenced parent rows (damn hard to do if you have many
foreign keys);
- be prepared to retry each transaction involving the inserts (this is
actually the best way to do it with postgres, at least in pre-8.0
versions, as it also solves the problem of "one failed SQL rolls back
the whole transaction");
- if you use 8.0 then you could use the new save point feature to retry
just the last failed insert, but I have no idea if that's not bundled
with some performance penalties (actually I didn't try at all 8.0, so I
just suppose it would work);
- use a patched postgres version which does not have the foreign key
deadlock problem, but it also do not have 100% referential integrity
guarrantied for foreign key constraints. I think you can find the patch
somewhere referenced in a past mail;

HTH,
Csaba.
On Mon, 2004-08-30 at 14:26, Philippe Lang wrote:

Hello,

What is the best method to debug a database that *sometimes* gets locked
in the INSERT mode? ('ps -afx | grep postgres' shows me a process that
is freezed in INSERT mode.)

Thanks

Philippe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

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