Problem with FK referential actions
От | Stephan Szabo |
---|---|
Тема | Problem with FK referential actions |
Дата | |
Msg-id | Pine.BSF.4.21.0108011001500.6711-100000@megazone23.bigpanda.com обсуждение исходный текст |
Ответы |
Re: Problem with FK referential actions
|
Список | 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 | 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. :)
В списке pgsql-hackers по дате отправления: