471,090 Members | 1,321 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

FKs and deadlocks

Hello,

As the amount of simultaneous users of my database grows (25 users
sometimes, PGSQL 7.4.5), deadlocks are unfortunately more and more
frequent. I guess this is due to the FKs problem with Postgresql.

I have tried to correct that by adding a "SET CONSTRAINTS ALL DEFERRED"
in every trigger and function, hoping it would solve my problem. Maybe
it helped, but it did not solve anything.

I don't know if anyone has a better idea, but I would like to try taking
away some FKs in my schema. My problem is that I really don't know which
one to delete. There are over 40 tables. Are there rules to do that? Or
maybe can I simply wait on the next deadlock, and try understanding who
got locked by who? OK, but how can I do that?

Thanks for your help!

Philippe

Note: I have read about a patch for FK's, is that something that can
really be used in production?

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

Nov 23 '05 #1
2 1941
On Fri, 22 Oct 2004, Philippe Lang wrote:
I have tried to correct that by adding a "SET CONSTRAINTS ALL DEFERRED"
in every trigger and function, hoping it would solve my problem. Maybe
it helped, but it did not solve anything.
Note that set constraints all deferred does nothing unless you made the
constraint deferrable which is not the default. If your constraints
aren't then you won't see any effect, and you'll probably want to change
them and see if that does help (and possibly making them initially
deferred at the same time).
I don't know if anyone has a better idea, but I would like to try taking
away some FKs in my schema. My problem is that I really don't know which
one to delete. There are over 40 tables. Are there rules to do that? Or
maybe can I simply wait on the next deadlock, and try understanding who
got locked by who? OK, but how can I do that?


I think you may be able to do this if you turn on statement locking and
try to resurrect the state from the logs. If you want to send a possibly
slightly sanitized typical sequence of events, we might be able to help
with that part.

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

Nov 23 '05 #2
Stephan Szabo <ss****@megazone.bigpanda.com> writes:
On Fri, 22 Oct 2004, Philippe Lang wrote:
I don't know if anyone has a better idea, but I would like to try taking
away some FKs in my schema. My problem is that I really don't know which
one to delete. There are over 40 tables. Are there rules to do that? Or
maybe can I simply wait on the next deadlock, and try understanding who
got locked by who? OK, but how can I do that?
I think you may be able to do this if you turn on statement locking and
try to resurrect the state from the logs.


Also look in pg_locks and pg_activity.

regards, tom lane

---------------------------(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

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by AKS | last post: by
3 posts views Thread by Hasan | last post: by
1 post views Thread by Matt White | last post: by
4 posts views Thread by T Dubya | last post: by
7 posts views Thread by Marcus | last post: by
9 posts views Thread by Mike Carr | last post: by
4 posts views Thread by John Rivers | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.