Do PL/pgSQL functions autocommit on their own?

Question

I have a PL/pgSQL function as follows on PostgreSQL 11:

create or replace function public.process_single_usage(usage_p "UsagePs")
  returns text
  language plpgsql
as $function$
declare
return_msg text;
   -- Variables used for business logic
begin
   -- Business logic and error handling, return_msg variable is assigned to an error message on failure 
   -- or to an empty string on success.

   -- A bunch of insert statements if no error arises.
   return return_msg;
end
$function$;

Could I use a second function to run the first one on every row of the table and rollback every insert if any one row results with an error message or should I need to use a single function controlling the transaction?

The function is always part of the transaction of the _caller_. And you can't use commit or rollback in a function — , Apr 22 '21 at 20:26
So then, since one functions is calling, would that mean there a two transactions on a given time, one nested the other one? — Catalina Cuellar, Apr 22 '21 at 20:37
No, everything runs in a single transaction which is defined by the caller of the first function — , Apr 22 '21 at 20:39
@a_horse_with_no_name: While that's true, I don't think it stands in the way of what the OP is trying to achieve. I added an answer. — Erwin Brandstetter, Apr 24 '21 at 10:09

Erwin Brandstetter · Answer 1 · 2021-04-24T10:37:09.867

Could I use a second function to run the first one on every row of the table and rollback every insert if any one row results with an error message

A FUNCTION always runs in a single transaction. But I don't think that stands in the way of what you are trying to do. A block with an EXCEPTION clause can trap exceptions and roll back subtransactions. Depending on what "every insert" is supposed to mean exactly, that might work for you.

If "every row" means every row in the whole operation (multiple calls of the child function), then you don't need to do anything extra. It's default behavior to roll back everything if an exception occurs.

If "every row" means every row affected by a single call of the child function, then consider the manual on Trapping Errors:

When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back.

Bold emphasis mine. Related:

PostgreSQL cannot begin/end transactions in PL/pgSQL

To actually COMMIT work at any point in the process, you need a PROCEDURE, introduced with Postgres 11. Starting with the same version, transaction control statements are also allowed in anonymous code blocks executed with the DO command.

Basically:

DO
$do$
DECLARE
  _rec "UsagePs";
BEGIN
   FOR _rec IN
      SELECT * FROM "UsagePs" u WHERE u.foo = 'bar' ORDER BY u.id
   LOOP
      BEGIN 
         PERFORM public.process_single_usage(_rec);
         -- COMMIT; -- ? -- requires Postgres 11
      EXCEPTION
         WHEN OTHERS THEN
            RAISE WARNING 'This error happened: %!', SQLERRM;
      END;
   END LOOP;
END
$do$;

I don't think you even need or want to COMMIT inside the loop. You just want to roll back subtransactions - or everything.

Note the use of SQLERRM to propagate any error message from the child function out, downgraded to WARNING. The manual:

The special variable SQLERRM contains the error message associated with the exception.

Do PL/pgSQL functions autocommit on their own?

1 Answers1