From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | JanWieck(at)Yahoo(dot)com |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Problem with FK referential actions |
Date: | 2001-08-01 17:10:54 |
Message-ID: | Pine.BSF.4.21.0108011001500.6711-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
While looking at what needs to be done with some
of the referential actions to make them work
better under deferred constraints, I noticed something
which I think is a bug.
sszabo=> create table base (a int unique);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'base_a_key' for
table 'base'
CREATE
sszabo=> create table deriv (a int references base(a) on update cascade, b
int);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
sszabo=> drop index base_a_key;
DROP
/* Note: the reason I drop the unique index is because
of the brokenness of our unique constraint for the a=a+1
update below, not because I don't want the constraint. */
sszabo=> insert into base values (2);
INSERT 783232 1
sszabo=> insert into base values (3);
INSERT 783233 1
sszabo=> insert into deriv values (2,1);
INSERT 783234 1
sszabo=> insert into deriv values (3,1);
INSERT 783235 1
sszabo=> update base set a=a+1;
UPDATE 2
sszabo=> select * from deriv;
a | b
---+---
4 | 1
4 | 1
(2 rows)
The output from the select, should I believe be (3,1), (4,1)
not (4,1), (4,1). I think we're violating General Rule 4 (I think
that's it) on the referential constraint definition ("For every
row of the referenced table, its matching rows, unique matching
rows, and non-unique matching rows are determined immediately
before the execution of any SQL-statement. No new matching
rows are added during the execution of that SQL-statement.")
because when the update cascade gets done for the 2 row, we've
changed the (2,1) to (3,1) which then gets hit by the update
cascade on the 3 row.
I was wondering if you had any thoughts on an easy way around
it within what we have. :)
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2001-08-01 17:29:24 | Red Hat developers |
Previous Message | Michael Meskes | 2001-08-01 15:28:36 | Re: ECPG eror ... |