Thread
-
Referential integrity problem postgresql 7.2 ?
Stephen R. van den Berg <srb@cuci.nl> — 2002-06-10T21:58:33Z
Should the following piece of code cause an: ERROR: <unnamed> referential integrity violation - key referenced from b not found in a Or should it work because the check is deferred and in the end no violations are present? create table a(ia int primary key); create table b(ia int references a initially deferred); insert into a values (7); begin; insert into b values (-7); update b set ia=-ia where ia<0; commit; drop table a; drop table b; -- Sincerely, srb@cuci.nl Stephen R. van den Berg (AKA BuGless). "-- hit any user to continue" -
Re: Referential integrity problem postgresql 7.2 ?
Billy O'Connor <billy@oconnoronline.net> — 2002-06-11T17:41:40Z
>From billy Tue Jun 11 13:38:51 2002 Date: Tue, 11 Jun 2002 10:54:27 -0700 (PDT) From: Stephan Szabo <sszabo@megazone23.bigpanda.com> Cc: <pgsql-bugs@postgresql.org> Sender: pgsql-bugs-owner@postgresql.org On Mon, 10 Jun 2002, Stephen R. van den Berg wrote: > Should the following piece of code cause an: > ERROR: <unnamed> referential integrity violation - key referenced > from b not found in a > Or should it work because the check is deferred and in the > end no violations are present? It should work (and does in current sources). If you look in the archives you should be able to get info on how to patch 7.2 (it came up recently, I'm not sure which list, and Tom Lane sent the message in question). I've verified that it does work in the current CVS checkout. -- Billy O'Connor
-
Re: Referential integrity problem postgresql 7.2 ?
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-06-11T17:54:27Z
On Mon, 10 Jun 2002, Stephen R. van den Berg wrote: > Should the following piece of code cause an: > ERROR: <unnamed> referential integrity violation - key referenced > from b not found in a > Or should it work because the check is deferred and in the > end no violations are present? It should work (and does in current sources). If you look in the archives you should be able to get info on how to patch 7.2 (it came up recently, I'm not sure which list, and Tom Lane sent the message in question).
-
Re: Referential integrity problem postgresql 7.2 ?
Tom Lane <tgl@sss.pgh.pa.us> — 2002-06-11T18:43:17Z
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > On Mon, 10 Jun 2002, Stephen R. van den Berg wrote: >> ERROR: <unnamed> referential integrity violation - key referenced >> from b not found in a >> Or should it work because the check is deferred and in the >> end no violations are present? > It should work (and does in current sources). If you look in the archives > you should be able to get info on how to patch 7.2 (it came up recently, > I'm not sure which list, and Tom Lane sent the message in question). BTW, should we back-patch that into 7.2.*? I was resistant to the idea because of concern about lack of testing, but seeing that we've gotten several complaints maybe we should do it anyway. regards, tom lane
-
Re: Referential integrity problem postgresql 7.2 ?
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-06-11T19:05:42Z
On Tue, 11 Jun 2002, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > On Mon, 10 Jun 2002, Stephen R. van den Berg wrote: > >> ERROR: <unnamed> referential integrity violation - key referenced > >> from b not found in a > >> Or should it work because the check is deferred and in the > >> end no violations are present? > > > It should work (and does in current sources). If you look in the archives > > you should be able to get info on how to patch 7.2 (it came up recently, > > I'm not sure which list, and Tom Lane sent the message in question). > > BTW, should we back-patch that into 7.2.*? I was resistant to the idea > because of concern about lack of testing, but seeing that we've gotten > several complaints maybe we should do it anyway. If we're doing a 7.2.2, it may be worth it. I think that part of the patch (minus concerns about variables possibly not being reset, etc) is reasonably safe (and that part could be reasonably looked at again quickly) and did have some limited testing due to a couple of people getting the patch back during 7.2's development. As a related side note. The other part of the original patch (the NOT EXISTS in the upd/del no action trigger) was rejected. For match full and match unspecified the same result can be reached by doing another query which may be better than the subquery. Do you think that'd be better? I'd like to get the other side of this bug fixed so that at least the no action cases work reasonably correctly. :)
-
Re: Referential integrity problem postgresql 7.2 ?
Tom Lane <tgl@sss.pgh.pa.us> — 2002-06-11T19:27:22Z
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > As a related side note. The other part of the original patch (the NOT > EXISTS in the upd/del no action trigger) was rejected. For match > full and match unspecified the same result can be reached by doing another > query which may be better than the subquery. Do you think that'd be > better? No opinion offhand; can you show examples of the alternatives you have in mind? regards, tom lane
-
Re: Referential integrity problem postgresql 7.2 ?
Victor Wagner <vitus@ice.ru> — 2002-06-11T20:13:11Z
On 2002.06.11 at 14:43:17 -0400, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > It should work (and does in current sources). If you look in the archives > > you should be able to get info on how to patch 7.2 (it came up recently, > > I'm not sure which list, and Tom Lane sent the message in question). > > BTW, should we back-patch that into 7.2.*? I was resistant to the idea I would appreciate this. I doubt that I it would fix problem with update sometable set a=a+1 where there exist unique index on sometable(a), but it would make postgresql behavoir closer to standard SQL. In my (user) point of view, it is obvoisly bugfix, rather than added feature, so it has right to appear in 7.2.x release. > because of concern about lack of testing, but seeing that we've gotten > several complaints maybe we should do it anyway. -- Victor Wagner vitus@ice.ru Chief Technical Officer Office:7-(095)-748-53-88 Communiware.Net Home: 7-(095)-135-46-61 http://www.communiware.net http://www.ice.ru/~vitus
-
Re: Referential integrity problem postgresql 7.2 ?
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-06-11T20:52:21Z
On Tue, 11 Jun 2002, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > As a related side note. The other part of the original patch (the NOT > > EXISTS in the upd/del no action trigger) was rejected. For match > > full and match unspecified the same result can be reached by doing another > > query which may be better than the subquery. Do you think that'd be > > better? > > No opinion offhand; can you show examples of the alternatives you have > in mind? [guessing that -bugs is probably not appropriate anymore, moving to -hackers] An additional query of the form... SELECT 1 FROM ONLY <pktable> WHERE pkatt=<keyval1> [AND ...] to the upd/del no action triggers. Right now in either deferred constraints or when multiple statements are run in a function we can sometimes raise an error where there shouldn't be one if a pk row is modified and a new pk row that has the old values is added. The above should catch this (and in fact the first versions of the patch that I did which were only sent to a couple of people who were having problems did exactly that). When I did the later patch, I changed it to a NOT EXISTS() subquery because for match partial, the new row might not need to exactly match, but the details of how it needs to match are based on what matching rows there are in the fk table. I'm not sure in general how else (apart from doing a lower level scan of the table) how to tell if another unrelated row with the same values has been added to the table between the point of the action that caused this trigger to be added to the queue and the point the trigger runs.