Re: RE: [BUGS] POSTGRES BUG - FIX IT PLEASE - Mailing list pgsql-hackers

From Peter Eisentraut
Subject Re: RE: [BUGS] POSTGRES BUG - FIX IT PLEASE
Date
Msg-id Pine.LNX.4.21.0010102252410.759-100000@peter.localdomain
Whole thread Raw
In response to RE: [BUGS] POSTGRES BUG - FIX IT PLEASE  ("Mikheev, Vadim" <vmikheev@SECTORBASE.COM>)
List pgsql-hackers
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
fromthe value to which that           site was previously updated within the same SQL-statement,           then an
exceptioncondition is raised: triggered data change           violation.       10) If a site in an object row is an
<objectcolumn> of an <update           statement: positioned> or <update statement: searched>, and           there is
anyattempt within the same SQL-statement to delete the           row containing that site, then an exception condition
israised:           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/



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: ORDER BY and UNION
Next
From: Tom Lane
Date:
Subject: Re: [INTERFACES] Announcing PgSQL - a Python DB-API 2.0 compliant interface to PostgreSQL