Re: Foreign key deadlocks

On Fri, 8 Nov 2002, Csaba Nagy wrote:

> I'm currently porting an application from Oracle to Postgres.
> Most of the things are working fine, but I still couldn't sort the problems
> with foreign keys.
> When creating the foreign key constraints as they are in the Oracle schema,
> the application deadlocks quite often.
> The reason is AFAICT the update lock placed on the referenced row when
> inserting in a table with foreign key constraints.
Yes, that lock is too strong.

> I can't believe this is a deliberate way to handle row locks, it just
> doesn't make sense. Am I missing some crucial design issue which makes the
> shared row locks impossible ?
The fact that AFAICT there isn't a way to get a shared row lock through
an sql statement right now in PostgreSQL.  The locking is there to make
the constraint not allow things through that shouldn't.

> Is there any guidline about how to solve this ? Rewriting the code is not an
> option, it relies too heavily on how foreign key constraints work in Oracle.

Depending on your needs for timing, I've been working (only partially
successfully) at lowering the number of deadlocks in the foreign key
stuff.  It's currently only as a patch to 7.3b2 (I've got to update to
most recent soon) and is not nearly production ready, but if you've got
development time, we could use help testing it.