Thread

1. RE: POSTGRES BUG - FIX IT PLEASE

   Vadim Mikheev <vmikheev@sectorbase.com> — 2000-10-10T20:07:13Z

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

2. Re: [HACKERS] RE: POSTGRES BUG - FIX IT PLEASE

   Peter Eisentraut <peter_e@gmx.net> — 2000-10-10T21:05:23Z

   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/