Re: Current transaction is aborted, commands ignored until end of transaction block

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