DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:Mime-Version:In-Reply-To:X-Apple-Yahoo-Original-Message-Folder:Content-Type:Content-Transfer-Encoding:Message-Id:Cc:X-Mailer:From:X-Apple-Yahoo-Replied-Msgid:Subject:Date:To;
	b=lUbc0TpePQ0IvtIkSB2Avi5b1nYcvF8f9AKyZ0ygVj5a0jvyzAHMz6Vaphsbs+1oFzl5no9LHq5Oob3mLCo/pw3hrsMSvBJrnColzycWc71SdNLNwGuvT1N6rIztDeDLIMyIMZZwfxSTu7ryLk1LTPDSfIq0PJcIs3UwYd/JZos=
	;
References: <4EFD3D32.9060902@greenpeace.org>
Mime-Version: 1.0 (iPad Mail 8J2)
In-Reply-To: <4EFD3D32.9060902@greenpeace.org>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <13131805-BCBB-42DF-953B-27EE36AAF213@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: Current transaction is aborted,
	commands ignored until end of transaction block
Date: Thu, 29 Dec 2011 23:44:00 -0500
To: Jan Bakuwel <jan.bakuwel@greenpeace.org>

On Dec 29, 2011, at 23:25, Jan Bakuwel <jan.bakuwel@greenpeace.org> wrote:

> Hi,
>=20
> Maybe there is a simple solution for PostgreSQL behaviour that is
> annoying me...
>=20
> I've got users making updates to a master table and a number of detail
> tables. All changes to the master record and related detail records are
> encapsulated in a transaction so everything can be rolled back if
> necessary and also to lock those master and related records for the user
> making the changes.
>=20
> When they do something that violates a constraint (for example adding a
> duplicate detail record where that is not allowed), PostgreSQL aborts
> the transaction. What I would much rather have is that PostgreSQL
> returns an error but does not cancel the transaction as it's perfectly
> OK (from a user's point of view) to try to do something that violates a
> constraint.
>=20
> What annoys me is that I don't think that a constraint violation made by
> a user should result in an aborted transaction. There is probably a very
> good reason to do that however the logic escapes me...
>=20
> Of course I can start testing existing values in the database before
> accepting them in the user interface but that's putting the horse behind
> the cart. I much rather use the constraints at the database level to
> tell me a particular update can't be done and do that without loosing
> everything else I happened to have done in that transaction until that
> point.
>=20
> Any suggestions?
>=20
> Jan
>=20
>=20

Start a "savepoint" before each sub-update and rollback to the savepoint if t=
he update fails, and then try again with different data.  If it succeeds you=
 then release the savepoint anad move on.

David J.=