Обсуждение: Referential integrity problem postgresql 7.2 ?
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"
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).
>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
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
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. :)
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
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