From: David Johnston <polobo@yahoo.com>
To: Jan Bakuwel <jan.bakuwel@greenpeace.org>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Current transaction is aborted, commands ignored until end of transaction block
Date: Thu, 29 Dec 2011 23:44:00 -0500
Message-ID: <13131805-BCBB-42DF-953B-27EE36AAF213@yahoo.com> (raw)
In-Reply-To: <4EFD3D32.9060902@greenpeace.org>
References: <4EFD3D32.9060902@greenpeace.org>
On Dec 29, 2011, at 23:25, Jan Bakuwel <jan.bakuwel@greenpeace.org> wrote:
> Hi,
>
> Maybe there is a simple solution for PostgreSQL behaviour that is
> annoying me...
>
> 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.
>
> 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.
>
> 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...
>
> 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.
>
> Any suggestions?
>
> Jan
>
>
Start a "savepoint" before each sub-update and rollback to the savepoint if the update fails, and then try again with different data. If it succeeds you then release the savepoint anad move on.
David J.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, jan.bakuwel@greenpeace.org
Subject: Re: Current transaction is aborted, commands ignored until end of transaction block
In-Reply-To: <13131805-BCBB-42DF-953B-27EE36AAF213@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox