Date: Tue, 22 Oct 2002 09:09:17 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Dan Langille <dan@langille.org>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: using deferred on PK/FK relationships
In-Reply-To: <3DB53D7F.6311.23BC6765@localhost>
Message-ID: <20021022090335.C87963-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 22 Oct 2002, Dan Langille wrote:

> Can deferrable etc be used when deleting primary key records (master
> table), then reinserting them without losing foreign key records
> (slave table)? I ask because in our testing we can't; we lose the
> foreign key records in the slave table. I'm guessing we are trying to
> abuse the feature.

> test=# BEGIN;
> BEGIN
> test=# SET CONSTRAINTS ALL DEFERRED;
> SET CONSTRAINTS
> test=# delete from master;
> DELETE 2
> test=# insert into master values (1);
> INSERT 20959595 1
> test=# insert into master values (2);
> INSERT 20959596 1
> test=# select * from slave;
>  id
> ----
>   1
>   1
> (2 rows)
>
> test=# commit;
> COMMIT
> test=# select * from slave;
>  id
> ----
> (0 rows)
>
> test=#
>
> Our hope was that after the commit, slave would retain the original
> rows.

As far as I can tell the above is close to right (I'd have said that
the select in the transaction should have given you 0 rows as well
but that's a matter of argument).  In case you're wondering, the
spec says for match full/unspecified something to the effect of:
when a row is marked for deletion that has not previously been marked
for deletion with on delete cascade all matching rows are marked for
deletion.  So, I don't think you can get the effect you're looking
for that way.  Someone else mentioned this recently and I was thinking
that it might be a useful extension to add another referential action
to handle it (and it wouldn't be particularly hard probably).