Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Have you read the article Erwin linked in your previous question? It explains all these things. –  dezso Jan 23 '13 at 9:41
add comment

2 Answers 2

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.

share|improve this answer
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.