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"
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
Import Notes
Reply to msg id not found: 20020611105152.V80282-100000@megazone23.bigpanda.comReference msg id not found: 20020611105152.V80282-100000@megazone23.bigpanda.com | Resolved by subject fallback
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).
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. :)
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
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
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.