Re: [Tm4j-developers] database deadlocks

[Pete Lewin-H. <pe...@il...>]

Kal,

I made the changes to the schema generated by Hibernate so I do agree with
you that the way forward isn't clear.

The problem is fundamentally to do with postgres and the way it locks for
update all foreign keys. However in our base product we (seem to?) have
solved this problem by always creating the foreign keys in the same order
with in the database creation script and by avoiding as much as possible
tables that have foreign keys back onto themselves.

In the same way I have taken the tm4j generated hibernate schema and changed
the order in which the constraints are defined as well as removing the
foreign key from the topicMap column on tmobjects. This has stopped the any
deadlocks appearing. I realise that removing foreign keys  indiscriminately
is not the answer but for the moment I can't come up with anything better.

To be honest this is really more of an FYI post than an explicit issue.
Maybe something to keep in mind if you are bored on a wet Wednesday
afternoon and decide to re-acrchitect the entire schema ;-)

cheers, Pete


----- Original Message ----- 
From: "Kal Ahmed" <ka...@te...>
To: "Pete Lewin-Harris" <pe...@il...>
Cc: <tm4...@li...>
Sent: Wednesday, August 18, 2004 9:28 AM
Subject: Re: [Tm4j-developers] database deadlocks


> Hi Pete,
>
> Did you make these changes to the schema generated by Hibernate or did
> you change the Hibernate mapping files ? If you did the latter, would it
> be possible to get a copy of your changes ? If you had to hack the
> schema generated by Hibernate then I'm a little less clear on the best
> way to proceed - on the one hand this is an issue we should try to
> address, but on the other hand I don't want to have to create and
> maintain a set of schemas for different databases (that was one of the
> reasons for using Hibernate in the first place!) - perhaps it could be
> reported as an issue to the Hibernate folks though ?
>
> Cheers,
>
> Kal

>