From: wieck@debis.com
To: Taral <taral@taral.net>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Concurrency problem
Date: Fri, 4 Feb 2000 13:10:24 +0100 (CET)
Message-ID: <m12GhZA-0003kbC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <Pine.LNX.4.10.10002040529580.10043-100000@r149127-2815.dobiecenter.com>
> I have a small problem, and was wondering if there was a better fix than
> the one I have.
>
> Here goes: I have tables A and B, both with primary keys. A has a field
> which refers to rows of B, and vice versa. When these fields are modified,
> I want to ensure referential integrity, but without using triggers. Is it
> possible to avoid deadlock and preserve integrity in all situations
> without serializing the updates?
>
> Details: Without any locking, an update of A concurrent with a delete on B
> will cause an integrity breach. With locking, concurrent updates of A and
> B can cause a deadlock...
You cannot use a regular trigger because of your circular
dependency. What you really need to insure integrity is the
FOREIGN KEY support coming with 7.0. It is implemented as
triggers too, but they are handled a little different by the
trigger manager and their execution can be delayed until
COMMIT.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wieck@debis.com, taral@taral.net
Subject: Re: [SQL] Concurrency problem
In-Reply-To: <m12GhZA-0003kbC@orion.SAPserv.Hamburg.dsh.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox