From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Dan Langille <dan(at)langille(dot)org> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: using deferred on PK/FK relationships |
Date: | 2002-10-22 16:09:17 |
Message-ID: | 20021022090335.C87963-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Galbavy | 2002-10-22 16:10:31 | 'fake' join and performance ? |
Previous Message | Stephan Szabo | 2002-10-22 16:03:13 | Re: 7.2 date/time format function problems |