Thread: Referential integrity problem

Denis Sbragion (d.sbragion@infotecna.it) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Referential integrity problem

Long Description
It looks like performing operation in two steps on tables with multiple deferred referential integrity constraints
betweenthem is not allowed. Not sure this is a bug, may be it is required by the standards, but sure it is rater
counterintuitive(may be, of course, I'm completely wrong). Most of the times this is just annoying, sometimes it
doesn'tlet you do updates, i.e. whenever you cannot perform the update in a single step (I found at least one case).
Testedboth on 7.0.3 and 7.1.2, with same results. 

P.S. Great job guys. 7.0.3 was very good, but 7.1.2 is simply wonderful.

Sample Code
Here it is an oversimplyfied example:

create table tablea
 (
  fielda integer,
  fieldb varchar(128),
  constraint tablea_pkey primary key (fielda)
 );

create table tableb
 (
  fielda integer,
  fieldb integer,
  fieldc varchar(128),
  fieldd integer,
  constraint tableb_pkey primary key (fielda, fieldb),
  constraint tablea_to_tableb foreign key (fielda) references
   tablea (fielda) on delete restrict on update restrict
   deferrable initially deferred,
  constraint scndtablea_to_tableb foreign key (fieldd) references
   tablea (fielda) on delete restrict on update restrict
   deferrable initially deferred
 );

insert into tablea (fielda, fieldb) values (1, 'Key 1');
insert into tableb (fielda, fieldb, fieldc, fieldd) values (1, 1, 'Ref to Key 1', 1);

Performing:

begin;
update tableb set fielda = 2, fieldd = 2 where fielda = 1;
update tablea set fielda = 2 where fielda = 1;
commit;

it's ok as it should. If you do the same thing above (now reversed, of course) in two steps, i.e:

begin;
update tableb set fielda = 1 where fielda = 2;
update tableb set fieldd = 1 where fieldd = 2;
update tablea set fielda = 1 where fielda = 2;
commit;

gives:

ERROR:  scndtablea_to_tableb referential integrity violation - key referenced from tableb not found in tablea

I hope it's clear.

No file was uploaded with this report

Please take my name off of this email list.

Thank you!

-----Original Message-----
From: pgsql-bugs@postgresql.org [mailto:pgsql-bugs@postgresql.org]
Sent: Monday, June 18, 2001 12:49 PM
To: pgsql-bugs@postgresql.org
Subject: [BUGS] Referential integrity problem


Denis Sbragion (d.sbragion@infotecna.it) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Referential integrity problem

Long Description
It looks like performing operation in two steps on tables with multiple
deferred referential integrity constraints between them is not allowed. Not
sure this is a bug, may be it is required by the standards, but sure it is
rater counterintuitive (may be, of course, I'm completely wrong). Most of
the times this is just annoying, sometimes it doesn't let you do updates,
i.e. whenever you cannot perform the update in a single step (I found at
least one case). Tested both on 7.0.3 and 7.1.2, with same results.

P.S. Great job guys. 7.0.3 was very good, but 7.1.2 is simply wonderful.

Sample Code
Here it is an oversimplyfied example:

create table tablea
 (
  fielda integer,
  fieldb varchar(128),
  constraint tablea_pkey primary key (fielda)
 );

create table tableb
 (
  fielda integer,
  fieldb integer,
  fieldc varchar(128),
  fieldd integer,
  constraint tableb_pkey primary key (fielda, fieldb),
  constraint tablea_to_tableb foreign key (fielda) references
   tablea (fielda) on delete restrict on update restrict
   deferrable initially deferred,
  constraint scndtablea_to_tableb foreign key (fieldd) references
   tablea (fielda) on delete restrict on update restrict
   deferrable initially deferred
 );

insert into tablea (fielda, fieldb) values (1, 'Key 1');
insert into tableb (fielda, fieldb, fieldc, fieldd) values (1, 1, 'Ref to
Key 1', 1);

Performing:

begin;
update tableb set fielda = 2, fieldd = 2 where fielda = 1;
update tablea set fielda = 2 where fielda = 1;
commit;

it's ok as it should. If you do the same thing above (now reversed, of
course) in two steps, i.e:

begin;
update tableb set fielda = 1 where fielda = 2;
update tableb set fieldd = 1 where fieldd = 2;
update tablea set fielda = 1 where fielda = 2;
commit;

gives:

ERROR:  scndtablea_to_tableb referential integrity violation - key
referenced from tableb not found in tablea

I hope it's clear.

No file was uploaded with this report


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Mon, 18 Jun 2001 pgsql-bugs@postgresql.org wrote:

> Denis Sbragion (d.sbragion@infotecna.it) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> Referential integrity problem
>
> Long Description It looks like performing operation in two steps on
> tables with multiple deferred referential integrity constraints
> between them is not allowed. Not sure this is a bug, may be it is
> required by the standards, but sure it is rater counterintuitive (may
> be, of course, I'm completely wrong). Most of the times this is just
> annoying, sometimes it doesn't let you do updates, i.e. whenever you
> cannot perform the update in a single step (I found at least one
> case). Tested both on 7.0.3 and 7.1.2, with same results.
>
> P.S. Great job guys. 7.0.3 was very good, but 7.1.2 is simply wonderful.

Yep.  AFAICT it's currently broken for such cases.  I've been working
(slowly :() on fixing it so it doesn't try the checks on rows that are no
longer valid (such as the intermediate states of multiple updates).