pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Problem with deferred referential integrity checks
2+ messages / 2 participants
[nested] [flat]

* Problem with deferred referential integrity checks
@ 2003-02-24 23:38  Bob Smith <bsmith@h-e.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Bob Smith @ 2003-02-24 23:38 UTC (permalink / raw)
  To: pgsql-sql

I'm running Postgres 7.2.1 on MacOS X Server 10.1.5.  Here's the 
problem I'm having, by way of a simplified example.  First create some 
tables and data:

   create table table1 (
     key int primary key,
     data text
   );

   create table table2 (
     table1key int references table1 deferrable initially deferred,
     moredata text
   );

   insert into table1 values (1, 'Item 1');
   insert into table1 values (2, 'Item 2');

   insert into table2 values (1, 'References Item 1');
   insert into table2 values (2, 'References Item 2');

Now try to make a change:

   begin work;
   delete from table1 where key = 1;
   insert into table1 values (1, 'Changed Item 1');
   commit;

   ERROR:  <unnamed> referential integrity violation - key in table1 
still
   referenced from table2

I don't understand this, it doesn't seem like there should be a 
referential violation.  At the end of the transaction, there is a row 
in table1 which satisfies every reference from table2.  On the other 
hand, the following does work:

   begin work;
   delete from table1 where key = 1;
   insert into table1 values (3, 'Changed Item 1');
   update table2 set table1key = 3 where table1key = 1;
   commit;

No error.  In this transaction the deferred referential check sees the 
inserted row, but in the previous one it does not.  Is this intentional 
for some reason, a limitation of Postgres, a bug, or am I just not 
"getting it"?

Obviously in this example the change could be done as an update, 
avoiding the problem entirely.  But the real database I'm working with 
is much more complicated and an update isn't possible.  I can solve the 
problem by generating new primary keys for all rows in table1 each time 
there is any change, thus making all transactions look like the second 
one above, but that causes other problems which I'd like to avoid.  Any 
other suggestions?

Thanks for any help!

Bob Smith
Hammett & Edison, Inc.
bsmith@h-e.com




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Problem with deferred referential integrity checks
@ 2003-02-25 07:09  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Bob Smith <bsmith@h-e.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2003-02-25 07:09 UTC (permalink / raw)
  To: Bob Smith <bsmith@h-e.com>; +Cc: pgsql-sql


On Mon, 24 Feb 2003, Bob Smith wrote:

> I'm running Postgres 7.2.1 on MacOS X Server 10.1.5.  Here's the

You'll need to update.  There were some intermediate state bugs that were
fixed in 7.3.

> Now try to make a change:
>
>    begin work;
>    delete from table1 where key = 1;
>    insert into table1 values (1, 'Changed Item 1');
>    commit;

Including this one I believe.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-02-25 07:09 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-24 23:38 Problem with deferred referential integrity checks Bob Smith <bsmith@h-e.com>
2003-02-25 07:09 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox