Re: Server error and deadlocks

Does anybody know if there is a plan to improve the foreign key support in
PostgreSQL?

While working with PostgreSQL 7.2.1 (Debian Linux/testing) we found out that
when a row is inserted in a table that has columns that are foreign keys,
Postgres normally locks the rows corresponding to the foreign keys (in their
original tables) both for reading and for writing. This is strange, because
it seems to me that it should allow reading from these rows (at least Oracle
8i does this) from other transactions. According to Postgres' logs, a SELECT
FOR UPDATE is executed on each of the foreign keys referenced in an INSERT,
UPDATE. Isn't this a little bit excessive?

This is a serious bottleneck in one application we've developed because we
have some basic tables from which foreign keys are referenced in a lot of
queries that cannot be executed in parallel because of this problem.


On Monday 13 January 2003 22:16, Stephan Szabo wrote:
> On Mon, 13 Jan 2003, Orr, Steve wrote:
> > Finally, there are LOTS of deadlocks and I'm thinking it's because they
> > are not doing "SELECT ... FOR UPDATE" or are explictly locking tables. Or
> > they are constantly updating the same rows. It seems our duhvelopers need
> > to get
>
> Well, if you're using foreign keys, you might be running into a deficiency
> in the foreign key implementation.  If that is it, then currently a
> partial workaround may be to make the constraints deferred which lessens
> the length of the lock, but doesn't remove the base deadlock possibility.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Juan Jose Comellas
(juanjo@comellas.com.ar)