Re: Possible to prevent transaction abort?

I'm intrigued by this solution, Johan.=C2=A0 It might be just the ticket!=
=C2=A0
I'll do some benchmarks when I have time in a week or so.

Johan Nel wrote:
Adam
B wrote:

  Hello all,


Is it possible to prevent Postgre from aborting the transaction upon a
constraint violation?


=46rom 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

=C2=A0=C2=A0=C2=A0 LOOP

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- first try to update the key

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 UPDATE db SET b =3D data WHERE a=
 =3D key;

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 IF found THEN

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 RETURN;

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 END IF;

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- not there, so try to insert t=
he key

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- if someone else inserts the s=
ame key concurrently,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- we could get a unique-key fai=
lure

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 BEGIN

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 INSERT I=
NTO db(a,b) VALUES (key, data);

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 RETURN;

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 EXCEPTION WHEN unique_violation =
THEN

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- do no=
thing, and loop to try the UPDATE again

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 END;

=C2=A0=C2=A0=C2=A0 END LOOP;

END;

$$

LANGUAGE plpgsql;


HTH,


Johan Nel

Pretoria, South Africa.







Videx Inc. 1105 N. E. Circle Blvd. Corva=
llis OR 97330 (541) 758-0521
CONFIDENTIAL COMMUNICATION: The email message and&=
nbsp;any attachments are intended only for th=
e addressee.  They may be privileged, co=
nfidential, and protected from disclosure. If =
;you are not the intended recipient, any =
;dissemination, distribution, or copying is expres=
sly prohibited.  If you received this em=
ail message in error, please notify the =
sender immediately by replying to this e-mail=
 message or by telephone