pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing deferred on PK/FK relationships
2+ messages / 2 participants
[nested] [flat]
* using deferred on PK/FK relationships
@ 2002-10-22 15:58 Dan Langille <dan@langille.org>
2002-10-22 16:09 ` Re: using deferred on PK/FK relationships Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dan Langille @ 2002-10-22 15:58 UTC (permalink / raw)
To: pgsql-sql
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.
here's a test script we tried:
drop table master;
CREATE TABLE master (
id integer NOT NULL,
Primary Key (id)
);
insert into master values (1);
insert into master values (2);
drop table slave;
create table slave (
id int,
foreign key (id)references master (id) on update restrict on
delete cascade INITIALLY DEFERRED)
;
insert into slave values (1);
insert into slave values (1);
Then:
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.
cheers
--
Dan Langille
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: using deferred on PK/FK relationships
2002-10-22 15:58 using deferred on PK/FK relationships Dan Langille <dan@langille.org>
@ 2002-10-22 16:09 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2002-10-22 16:09 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: 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).
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-10-22 16:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-22 15:58 using deferred on PK/FK relationships Dan Langille <dan@langille.org>
2002-10-22 16:09 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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