Re: Transactions and constraints

On Fri, 2002-05-31 at 02:23, Tom Lane wrote:
> Emil Eifrem <emil.eifrem@windh.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;
---

Some background information is in order. The application container we're
writing supports transparent persistence updates. This means that from a
number of business logic operations, the container generates the
appropriate SQL statements for persisting those operations. We therefore
have no control over the sequence of updates that the client programmer
initiates. We do know that by COMMIT, the transaction is certain not to
violate referential integrity -- but mid-transaction, anything can
happen.

I was under the impression that with DEFERRABLE and INITIALLY DEFERRED,
PostgreSQL would not verify constraints until the transaction commits.
Are we out on a limb here?

Thanks,

--
Emil Eifrem [emil@windh.net]            /'\ ASCII Ribbon Campaign
Kernel Developer, .windh AB             \ / No HTML/RTF in email
                                         ×  No Word docs in email
                                        / \ Respect for open standards