RE: POSTGRES BUG - FIX IT PLEASE
create table t1
(
f1 integer,
f2 integer
);create table t2
(
f1 integer references t1(f1),
f2 integer
);begin transaction;
insert into t1(f1,f2) values(1,1);
delete from t1 where f1=1;ERROR: triggered data change violation on relation "t1"
You cannot change data twice within a transaction if there's a RI
constraint on the table. This is per SQL, nothing we can do about it.
^^^^^^^^^^^^^^^
Is it true?! *Any reasons* for this? DELETE doesn't break integrity rules.
Just tested it in Oracle - deletion is allowed!
But yes, I know that Oracle doesn't always follow standards -:)
Can someone test it under Informix, others?
Vadim
Mikheev, Vadim writes:
You cannot change data twice within a transaction if there's a RI
constraint on the table. This is per SQL, nothing we can do about it.^^^^^^^^^^^^^^^
Is it true?!
Actually, it's not. Jan Wieck first explained this on July 23 in "Re:
[GENERAL] failed Delete after Insert in a transaction", and we've been
believing it ever since, but I just found out that it's wrong.
The standard reads
11.8 <referential constraint definition>
9) If any attempt is made within an SQL-statement to update some
site to a value that is distinct from the value to which that
site was previously updated within the same SQL-statement,
then an exception condition is raised: triggered data change
violation.
10) If a site in an object row is an <object column> of an <update
statement: positioned> or <update statement: searched>, and
there is any attempt within the same SQL-statement to delete the
row containing that site, then an exception condition is raised:
triggered data change violation.
(also 11.8 GR 8 b) i) 2), but it's too boring to quote...)
Note that it talks about "statements", not "transactions".
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/