Thread: Transactions and constraints
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
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
-----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]
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
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
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.