Interactive INSERT / UPDATE function to implement UPSERT

Question

I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 record successfully added" or " Record with PK [wherever] was successfully updated" or "An error occurred, field X cannot be null" or "any error that it gets".

I want to avoid an expensive CURSOR and an even more expensive EXCEPTION clause. I also want it to be optimized for speed. The function should consider concurrency because there will be more than 50 clients connected to the database at the same time, each updating/inserting (calling the same function). This is what I have currently:

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.test)
 RETURNS text AS
$BODY$
DECLARE 
myoutput text :='Nothing has occured';
BEGIN
 update netcen.test set 
 tes=myobj.tes,
 testname=myobj.testname WHERE testkey=myobj.testkey;
IF FOUND THEN
 myoutput:= 'Record with PK[' || myobj.testkey || '] successfully updated';
 RETURN myoutput;
 END IF;
 BEGIN
 INSERT INTO netcen.test values(myobj.testkey,
 myobj.tes,
 myobj.testname);
 myoutput:= 'Record successfully added';
 EXCEPTION WHEN null_value_not_allowed THEN
 RAISE NOTICE 'something has just occured';
 myoutput:= 'A field was null';
 WHEN not_null_violation THEN
 RAISE NOTICE 'something has just occured';
 myoutput:= 'A field was null';
 WHEN unique_violation THEN
 RAISE NOTICE 'something has just occured';
 myoutput:= 'A field was null';
 END;
 RETURN myoutput;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;

I know this function can be simplified to a simple and shorter writable CTE function of SQL language. But those have a race condition. And I don't know how to capture errors and report the operation done as output from it.

Can this function be simplified or complicated but handle all my concerns?

• András Váczi: Have you read the article Erwin linked in your previous question? It explains all these things.

Answer 1

If you want to catch NULL violations as well you have to cover the UPDATE as well, which makes the function a little more expensive.

SET search_path = netcen;
CREATE OR REPLACE FUNCTION fun_test(myobj test, OUT myoutput text)
 RETURNS text AS
$func$
BEGIN
 UPDATE test
 SET tes = myobj.tes,
 testname = myobj.testname
 WHERE testkey = myobj.testkey;
 IF FOUND THEN
 myoutput := 'UPDATE successfull. testkey = ' || myobj.testkey;
 RETURN;
 END IF;
 INSERT INTO test
 SELECT (myobj).*;
 myoutput := 'INSERT successfull. testkey = ' || myobj.testkey;
EXCEPTION
WHEN null_value_not_allowed THEN
 RAISE NOTICE 'null_value_not_allowed occurred.';
 myoutput := 'A field was null.';
WHEN not_null_violation THEN
 RAISE NOTICE 'not_null_violation occurred.';
 myoutput:= 'A field was null.';
WHEN unique_violation THEN
 RAISE NOTICE 'unique_violation occurred.';
 myoutput:= 'Duplicate value.';
END
$func$ LANGUAGE plpgsql;

Using an OUT parameter and made some simplifications and clarifications.

I would just let the regular EXCEPTION happen. People are not supposed to enter NULL values for NOT NULL columns. This way, the UPDATE can run outside the EXCEPTION block.

Answer 2

Reading through your code it isn't clear what you are trying to do regarding the possible race condition. The standard race condition one worries about is there. You are just raising a notice and, I believe, continuing. Now, in the event you have multiple unique constraints, you need to have some way of detecting this and raising the exception. So something like:

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.test, is_retry bool) 
RETURNS text AS $BODY$ 
DECLARE myoutput text :='Nothing has occured'; 
BEGIN
 WITH my_update (tes, testname, testkey) AS (
 update netcen.test 
 SET tes=myobj.tes,
 testname=myobj.testname 
 WHERE testkey=myobj.testkey
 RETURNING tes, testname, testkey)
 INSERT INTO netcen.test (tes, testname, testkey)
 SELECT myobj.tes, myobj.testname, myobj.testkey
 FROM my_update WHERE testkey IS NULL; 
 EXCEPTION 
 WHEN null_value_not_allowed THEN
 RAISE NOTICE 'something has just occured';
 return 'A field was null';
 WHEN not_null_violation THEN
 RAISE NOTICE 'something has just occured';
 RETURN 'A field was null';
 WHEN unique_violation THEN
 IF is_retry THEN
 RAISE NOTICE 'actual unique conflict';
 return 'unique key conflict';
 ELSE
 return fun_test(my_obj, true); --retry
 END IF;
 END;
 RETURN 'Success';
END; 
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

I haven't tested this exactly so it may have typos, etc. but the basic concept should work pretty well and fix the race condition you are concerned about (but still currently have).