From: Leif Biberg Kristensen <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Re: Current transaction is aborted,
	commands ignored until end of transaction block
Date: Fri, 30 Dec 2011 12:25:08 +0100
User-Agent: KMail/1.13.7 (Linux/3.0.6-gentoo; KDE/4.7.3; x86_64; ; )
References: <4EFD3D32.9060902@greenpeace.org>
In-Reply-To: <4EFD3D32.9060902@greenpeace.org>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <201112301225.08946.leif@solumslekt.org>

 Fredag 30. desember 2011 05.25.22 skrev Jan Bakuwel :
> 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.

Here's an example from a plpgsql function I wrote, where a possible violation 
of unique constraint on (parent_id, source_text) is checked within the 
transaction:

SELECT source_id FROM sources WHERE parent_id = par_id AND source_text = txt 
INTO x;
IF NOT FOUND THEN
    INSERT INTO sources (parent_id, source_text, sort_order, source_date, 
part_type)
        VALUES (par_id, txt, srt, true_date_extract(txt), pt) RETURNING 
source_id INTO src_id;
ELSE
    RAISE NOTICE 'Source % has the same parent id and text as you tried to 
enter.', x;
    RETURN -x; -- abort the transaction and return the offended source id as a 
negative number.
END IF;

I don't know if it's considered good form to issue a RETURN in the middle of a 
function on an error condition, but the main point is that you can take an 
alternate action when the violation is about to happen. Before I introduced 
this test, the PHP interface just barfed all over the place with "transaction 
aborted" messages.

Here's another test from the same function, where the alternate action is 
basically a no-op:

-- don't violate unique constraint on (source_fk, event_fk) in the 
event_citations table.
-- if this source-event association already exists, it's rather pointless to 
repeat it.
PERFORM * FROM event_citations WHERE event_fk = event AND source_fk = src_id;
    IF NOT FOUND THEN
        INSERT INTO event_citations (event_fk, source_fk) VALUES (event, 
src_id);
    ELSE
        RAISE NOTICE 'citation exists';
    END IF;

regards, Leif