Looping through a CURSOR in PL/PGSQL without locking the table

Question

I have a simple PL/PGSQL block Postgres 9.5 that loops over records in a table and conditionally updates some of the records.

Here's a simplified example:

DO $$
  DECLARE

    -- Define a cursor to loop through records
    my_cool_cursor CURSOR FOR
    SELECT
      u.id          AS user_id,
      u.name        AS user_name,
      u.email       AS user_email
    FROM users u
    ;

  BEGIN

    FOR record IN my_cool_cursor LOOP

      -- Simplified example: 
      -- If user's first name is 'Anjali', set email to NULL
      IF record.user_name = 'Anjali' THEN
        BEGIN
          UPDATE users SET email = NULL WHERE id = record.user_id;
        END;
      END IF;

    END LOOP;
  END;

$$ LANGUAGE plpgsql;

I'd like to execute this block directly against my database (from my app, via the console, etc...). I do not want to create a FUNCTION() or stored procedure to do this operation.

The Issue

The issue is that the CURSOR and LOOP create a table-level lock on my users table, since everything between the outer BEGIN...END runs in a transaction. This blocks any other pending queries against it. If users is sufficiently large, this locks it up for several seconds or even minutes.

What I tried

I tried to COMMIT after each UPDATE so that it clears the transaction and the lock periodically. I was surprised to see this error message:

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.

I'm not quite sure how this is done. Is it asking me to raise an EXCEPTION to force a COMMIT? I tried reading the documentation on Trapping Errors but it only mentions ROLLBACK, so I don't see any way to COMMIT.

1. How do I periodically COMMIT a transaction inside the LOOP above?
2. More generally, is my approach even correct? Is there a better way to loop through records without locking up the table?

Answer 1

If you want to avoid locking rows for a long time, you could also define a cursor WITH HOLD, for example using the DECLARE SQL statement.

Such cursors can be used across transaction boundaries, so you could COMMIT after a certain number of updates. The price you are paying is that the cursor has to be materialized on the database server.

Since you cannot use transaction statements in functions, you will either have to use a procedure or commit in your application code.