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"

Mon, 10 Jun 2002 23:58:33 +0200
srb@cuci.nl (Stephen R. van den Berg)
Referential integrity problem postgresql 7.2 ?
   >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

Tue, 11 Jun 2002 13:41:40 -0400 (EDT)
Billy O'Connor <billy@oconnoronline.net>
Re: Referential integrity problem postgresql 7.2 ?

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).


Tue, 11 Jun 2002 10:54:27 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Referential integrity problem postgresql 7.2 ?
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

Tue, 11 Jun 2002 14:43:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Referential integrity problem postgresql 7.2 ? 
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. :)



Tue, 11 Jun 2002 12:05:42 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Referential integrity problem postgresql 7.2 ? 
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

Tue, 11 Jun 2002 15:27:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Referential integrity problem postgresql 7.2 ? 
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

Wed, 12 Jun 2002 00:13:11 +0400
Victor Wagner <vitus@ice.ru>
Re: Referential integrity problem postgresql 7.2 ?
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.


Tue, 11 Jun 2002 13:52:21 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Referential integrity problem postgresql 7.2 ? 