Re: Transactions and constraints

On 31 May 2002, Emil Eifrem wrote:

> On Fri, 2002-05-31 at 02:23, Tom Lane wrote:
> > Emil Eifrem <emil(dot)eifrem(at)windh(dot)com> writes:
> > > Upon commit, we get the following error message:
> > > "ERROR: my_ref referential integrity violation - key referenced from
> > > table_b not found in table_a"
> > > We believe this to be valid SQL. We have successfully executed the
> > > equivalent statements on an Informix Dynamic Server 9.20.
> >
> > I do not get an error with current sources. I believe this was fixed
> > by Stephan Szabo's recent patch:
> >
> > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ri_triggers.c.diff?r1=1.32&r2=1.33
>
> That is correct. With the patch, the sequence outlined in my previous
> mail works fine. However, the following (equally probable, in our case)
> sequence gives an identical error:
>
> ---
> BEGIN;
> insert into table_b (a_id,value) values (1,1);
> insert into table_a (id) values (1);
> delete from table_a where id=1;
> insert into table_a (id) values (1);
> COMMIT;
> ---

It's a bug. I think that was the other part of the original patch the
above patch came from that was rejected. Let me see if I can find it.
It involves putting either another query or an exists into the no action
checks to make sure that another row wasn't made with the same key
values. I'd done the exists because that was similar to what'd be needed
for match partial, but the expected speed hit seemed to turn people off.
There's got to be a better way, but at least it'll get you moving.