Race Condition Between Select And Insert For Multiple Columns

Note: This is a question which is a follow up of this solution. You need to read the link to get context for this question. Also, this is for postgres v9.4 If we want to return mul

Solution 1:

The wrench in the works is SELECT f_insert_tag(tag_p_id, _tag) instead of

SELECT * FROMf_insert_tag(tag_p_id, _tag)

For Postgres 9.4

CREATEFUNCTION f_insert_tag(_tag_id int, _tag text, OUT _tag_id_ int, OUT _tag_ text) 
AS
$func$
 BEGININSERTINTO t(tag_id, tag)
   VALUES (_tag_id, _tag)
   RETURNING t.tag_id, t.tag
   INTO  _tag_id_, _tag_;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN-- catch exception, return NULLEND
$func$  LANGUAGE plpgsql;


CREATEFUNCTION f_tag_id(_tag_id int, _tag text, OUT _tag_id_ int, OUT _tag_ text) AS
$func$
BEGIN
LOOP
   SELECT t.tag_id, t.tag
   FROM   t
   WHERE  t.tag = _tag

   UNIONALLSELECT*-- !!!FROM   f_insert_tag(_tag_id, _tag)
   LIMIT  1INTO _tag_id_, _tag_;

   EXIT WHEN _tag_id_ ISNOTNULL;  -- else keep loopingEND LOOP;
END
$func$ LANGUAGE plpgsql;

db<>fiddle here

For Postgres 9.5 or later:

CREATE FUNCTION f_tag_id(_tag_id int, _tag text, OUT _tag_id_ int, OUT _tag_ text) AS
$func$
BEGIN
LOOPSELECT t.tag_id, t.tag
   FROM   t
   WHERE  t.tag = _tag
   INTO   _tag_id_, _tag_;

   EXITWHEN FOUND;

   INSERT INTO t (tag_id, tag)
   VALUES (_tag_id, _tag)
   ON     CONFLICT (tag) DONOTHING
   RETURNING t.tag_id, t.tag
   INTO   _tag_id_, _tag_;

   EXITWHEN FOUND;
ENDLOOP;
END
$func$  LANGUAGE plpgsql;

db<>fiddle here

Basics here:

• Is SELECT or INSERT in a function prone to race conditions?