Thread: Transactions and constraints

Transactions and constraints

From
Emil Eifrem
Date:
Hello,

We have a problem related to transactions and constraints. Our tables
look like this:

---
CREATE TABLE table_a
(
        id      INT NOT NULL PRIMARY KEY
);

CREATE TABLE table_b
(
        a_id    INT NOT NULL CONSTRAINT my_ref
                REFERENCES table_a(id)
                DEFERRABLE INITIALLY DEFERRED,
        value   INT NOT NULL
);
---

In psql we run the following SQL statements:

---
BEGIN;

INSERT INTO table_a (id) VALUES (1);
INSERT INTO table_b (a_id,value) VALUES (1,1);
DELETE FROM table_b WHERE a_id=1;
DELETE FROM table_a WHERE id=1;

COMMIT;
---

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.

Our environment:

        Linux palpatine 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686
unknown
        PostgreSQL 7.2.1 built from yesterday's source RPMS on
www.postgresql.org

Does anyone know if the error is on ours or on PostgreSQL's side?

TIA,

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


Re: Transactions and constraints

From
Tom Lane
Date:
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

I am hesitant to back-patch this in for 7.2.2, however, since it has
received essentially no testing.  (Two things clearly wrong with the
original patch are that it doesn't close pk_rel again, and it neglects
to reset ReferentialIntegritySnapshotOverride before returning.)  But
if you care to apply it locally with those repairs, it may hold you
until 7.3 ...

            regards, tom lane

Re: Transactions and constraints

From
Emil Eifrém
Date:
-----Original Message-----
From:    Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent:    Fri 5/31/2002 02:23
Subject:    Re: [GENERAL] Transactions and constraints

>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"

>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
[snip]
>if you care to apply it locally with those repairs, it may hold you
>until 7.3 ...

Ok, thanks a lot for your prompt reply. I just got back from work, but I'll apply the patch to my vanilla 7.2.1
tomorrowand see how it works out. 

And the obvious question: If we do manage to successfully patch it, how long do we need to hold out? Aka, do you know
anytentative release date for 7.3?  

-EE [emil@windh.net]

Re: Transactions and constraints

From
Neil Conway
Date:
On Fri, 31 May 2002 03:00:24 +0200
"Emil Eifr" <emil.eifrem@windh.com> wrote:
> And the obvious question: If we do manage to successfully patch it,
> how long do we need to hold out? Aka, do you know any tentative
> release date for 7.3?

I believe the current plan is to start the beta period in late
August / early September, but I don't know how set in stone
that is.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: Transactions and constraints

From
Emil Eifrem
Date:
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


Re: Transactions and constraints

From
Stephan Szabo
Date:
On 31 May 2002, Emil Eifrem wrote:

> 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;
> ---

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.