UPSERT statement never completes, infinite loop?

Question

Based on some of the stackoverflow answers I wrote the following UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would be possible in this case for the insert and update statements to be mismatched and therefore enter an infinite loop):

CREATE FUNCTION upsert(sql_insert TEXT, sql_update TEXT) RETURNS VOID AS
$$
DECLARE
    i integer := 0;
BEGIN
    LOOP
        -- terminate if loop reaches 2 runs
        IF i >= 2 THEN
            RETURN;
        END  IF;
        BEGIN
            -- try update
            EXECUTE sql_update;
            -- terminate if update successful
            IF found THEN
                RETURN;
            END IF;
            BEGIN
                -- try insert
                EXECUTE sql_insert;
                RETURN;
            -- insert violated unique constraint, try loop again
            EXCEPTION WHEN unique_violation THEN
            END;
        END;
        i := i+1;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a particularly big table with around 11,000 rows.

However, this sample input never finishes running:

SELECT upsert(
$$
INSERT INTO accounts 
VALUES (
    '0012000000wa7WzAAI', 'Random company', NULL, 
    'Tier 2', 'Joe BLoggs', 'Joe Bloggs', 'US', 
    TIMESTAMP '2012-12-14 00:00:00', NULL, 'Ex-Customer')
$$, 
$$
UPDATE accounts 
SET account_name='Random Companyh', country=NULL, account_tier='Tier 2', 
    account_owner='Joe Bloggs', sales_person='Joe Bloggs', 
    office_managed='US', date_first_opportunity=TIMESTAMP '2012-12-14 00:00:00', 
    industry=NULL, account_type='Ex-Customer' 
WHERE account_id='0012000000wa7WzAAI'
$$
)

I can't work out why this isn't working, any help would be appreciated.

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

The immediate cause of the infinite loop is this. Per documentation:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

But there's more:

CREATE FUNCTION upsert(_ins text, _upd text)
  RETURNS int AS
$func$
DECLARE
   loop_ct int := 0;
   row_ct  int;
BEGIN
LOOP
   IF loop_ct > 1 THEN   -- terminate on 2nd loop
      RETURN 0;
   END  IF;

   EXECUTE _upd;         -- try update
   GET DIAGNOSTICS row_ct = ROW_COUNT;
   IF row_ct > 0 THEN    -- terminate if update successful
      RETURN row_ct;
   END IF;

   BEGIN                 -- try insert
      EXECUTE _ins;
      GET DIAGNOSTICS row_ct = ROW_COUNT;
      RETURN row_ct;
   EXCEPTION WHEN unique_violation THEN
      -- insert violated unique constraint, keep looping
   END;
   loop_ct := loop_ct + 1;
END LOOP;
END
$func$  LANGUAGE plpgsql;

Major points

GET DIAGNOSTICS instead of FOUND, as explained at the outset.
Don't start another block around the UPDATE. Expensive and no use.
I added a feature: on success, the query returns the number of rows affected - at no additional cost since we get the row count anyway. May or may not be useful.

The rest is unchanged, just reformatted.

I have to add a warning about SQL injection. Passing code as text is very susceptible. The code cannot be sanitized. A function like this must be handled with care. Certainly should not be created with SECURITY DEFINER. Related answer:

Issue with composite type in an UPSERT function

UPSERT statement never completes, infinite loop?

1 Answers1

Major points