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 | 14 | 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. :)