Problem with FK referential actions

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

Responses

Browse pgsql-hackers by date

  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 ...