4 ms·
The UPSERT implementations in this article are simply wrong (he should call them "DELSERT" perhaps). A simple Postgres function like the following works for me
by lazyjones 13y ago
The UPSERT implementations in this article are simply wrong (he should call them "DELSERT" perhaps).
A simple Postgres function like the following works for me:
CREATE OR REPLACE FUNCTION upsert_my_table(key text, value text) RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
UPDATE my_table SET value=$2 WHERE key=$1;
IF NOT FOUND THEN
INSERT INTO my_table (key,value) VALUES ($1,$2);
END IF;
-- add PERFORM pg_sleep(10) for concurrency testing
END
$$;
(according to my tests, it has no concurrent UPDATE problems ... duplicate keys are another matter and best handled with restarting transactions / savepoints)
- einhverfr 13y agoYour issue is actually two concurrent inserts. Once you have inserted rows, there are no concurrency problems beyond those of ordinary updates.
- the_mitsuhiko 13y ago> according to my tests, it has no concurrent UPDATE problems You have concurrency updates with deletes as well as inserts. That's literally the worst way to implement upserts currently. Even CTE's are superior to that.
- spacemanmatt 13y agoThis function will not work concurrently.