postgresqlexceptionstored-proceduresrollbacksavepoints

How to retry transaction after exception in postgreSQL


In different parts of my code I have to retry transactions after exceptions. But I cant figure out how to do it. Here is my test function:

CREATE OR REPLACE FUNCTION f() RETURNS VOID AS $$
DECLARE
    user_cur CURSOR FOR SELECT * FROM "user" WHERE id < 50 limit 10;
    row RECORD;
    counter INTEGER DEFAULT 0;
    dummy INTEGER DEFAULT 0;
BEGIN
    RAISE INFO 'Start... ';
    OPEN user_cur;
    LOOP
      FETCH user_cur INTO row;
      EXIT WHEN row IS NULL;

      BEGIN
        UPDATE "user" SET dummy = 'dummy' WHERE id = row.id;
        counter := counter + 1;
        dummy := 10 / (5 % counter);
        RAISE NOTICE 'dummy % , user_id %', (5 % counter), row.id;

      EXCEPTION WHEN division_by_zero THEN
          --What should I do here to retry transaction?
      END;
    END LOOP;
    RAISE INFO 'Finished.';

    RETURN;
END;
$$ LANGUAGE plpgsql;

Solution

  • Thanks to the above comments I've found the solution:

    CREATE OR REPLACE FUNCTION f() RETURNS VOID AS $$
    DECLARE
        row RECORD;
        counter INTEGER DEFAULT 0;
        dummy INTEGER DEFAULT 0;
    BEGIN
        -- Clear user rating
        RAISE INFO 'Start... ';
        FOR row IN SELECT * FROM user_prop WHERE id < 50 limit 10 LOOP
          LOOP
            BEGIN
              UPDATE user_prop SET some_field = 'whatever' WHERE id = row.id;
              counter := counter + 1;
              dummy := 10 / (5 % counter);
    
              -- exit nested loop if no exception
              EXIT; 
    
            EXCEPTION 
              WHEN division_by_zero THEN
                -- do nothing, just repeat the loop
              WHEN deadlock_detected THEN
                -- do nothing, just repeat the loop
            END;
          END LOOP;
        END LOOP;
        RAISE INFO 'Finished.';
    
        RETURN;
    END;
    $$ LANGUAGE plpgsql;