From: | Johan Nel <johan555(dot)nel555(at)xsinet555(dot)co(dot)za> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Possible to prevent transaction abort? |
Date: | 2009-05-01 19:09:23 |
Message-ID: | gtfhei$gs9$1@news.motzarella.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Adam B wrote:
> Hello all,
>
> Is it possible to prevent Postgre from aborting the transaction upon a
> constraint violation?
From the help files maybe the following could get you on the right track:
This example uses exception handling to perform either UPDATE or INSERT,
as appropriate:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
HTH,
Johan Nel
Pretoria, South Africa.
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2009-05-01 19:40:32 | Re: Possible to prevent transaction abort? |
Previous Message | Chris Spotts | 2009-05-01 18:47:54 | Re: Handling large number of OR/IN conditions |