Re: Transactions and constraints
От | Emil Eifrem |
---|---|
Тема | Re: Transactions and constraints |
Дата | |
Msg-id | 1022849300.29316.39.camel@palpatine обсуждение исходный текст |
Ответ на | Re: Transactions and constraints (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Transactions and constraints
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: