From: | "Postgres User" <postgres(dot)developer(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to capture and handle failed INSERT |
Date: | 2007-03-04 20:24:16 |
Message-ID: | b88c3460703041224s15179ffcye4a8c6ecb0ee008a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I'm using this code to increment a counter table:
IF Exists (SELECT keyfld FROM counter_tbl WHERE key_fld = 'key_val') THEN
UPDATE counter_tbl SET counter_fld = counter_fld + 1
WHERE key_fld = 'key_val';
ELSE
INSERT INTO counter_tbl(key_fld, counter_fld) VALUES('key_val', 1);
END IF;
Now, I assume that it's possible for another session to INSERT a row
in the microseconds that exist between the Select and Insert
statements above.
I also assume that I can wrap the above code in a transaction, and if
the transaction fails (because another session's Insert causes my
Insert to fail), then I simply need to re-execute it once. (Updates
should never fail.)
Does anyone have a simple example of the best way to code this type of
transaction- and the best way to re-execute the same code on failure?
I could use a loop but I'm not sure if that's the best solution.
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Moran | 2007-03-04 20:30:13 | Re: real multi-master replication? |
Previous Message | Raymond O'Donnell | 2007-03-04 19:34:17 | Re: date format |