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