On Dec 10, 2007, at 4:48 PM, Ted Byers wrote:
--- Vivek Khera <khera@xxxxxxxxxxx> wrote:
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
For what it's worth, the real algorithm would be
as follows. I
hadn't had enough coffee yet, and I forgot the
UPDATE bit.
IF
(a query matching your old data returns rows)
THEN
UPDATE with your new data
ELSE
INSERT your new data
Still exists race condition. Your race comes from
testing existence,
then creating/modifying data afterwards. You need
to make the test/
set atomic else you have race.
Yes, but how do you do that in a stored function or
procedure or in a trigger. It would be obvious to me
if I were writing this in C++ or Java, but how do you
do it using SQL in an RDBMS?
I saw something about table locks, but that doesn't
seem wise, WRT performance.
The classic example of a race condition, involving a
bank account, was used in the manual to introduce the
idea of a transaction, but we can't use a transaction
in a trigger, can we?
It is one thing to point out a race condition, but a
pointer to a solution that would work in the context
of the problem at hand would be useful and
appreciated.
Thanks all.
In a stored procedure you'd just execute the UPDATE and then check
the FOUND variable to see if it found a row to update:
UPDATE table_name SET foo='bar' WHERE id=5;
IF NOT FOUND THEN
INSERT INTO table_name (id, foo) VALUES (5, 'bar');
END IF;
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
