title: Can a Postgres Commit Exist in Procedure that has an Exception Block?
body: <p>I am having a difficult time understanding transactions in Postgres. I have a procedure that may encounter an exception. There are parts of the procedure where I might want to commit my work so-far so that it won't be rolled back if an exceptions ensues.</p>
  
  <p>I want to have an exception handling block at the end of the procedure where I catch the exception and insert the information from the exception into a logging table.</p>
  
  <p>I have boiled the problem down to a simple procedure, below, which fails on PostgreSQL 11.2 with</p>
  
  <pre><code>2D000 cannot commit while a subtransaction is active
  PL/pgSQL function x_transaction_try() line 6 at COMMIT
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>    drop procedure if exists x_transaction_try;
      create or replace procedure x_transaction_try()
          language plpgsql
      as $$
      declare
      begin
           raise notice 'A';
           -- TODO A: do some insert or update that I want to commit no matter what
           commit;
           raise notice 'B';
           -- TODO B: do something else that might raise an exception, without rolling
           -- back the work that we did in "TODO A".
      exception when others then
        declare
          my_ex_state text;
          my_ex_message text;
          my_ex_detail text;
          my_ex_hint text;
          my_ex_ctx text;
        begin
            raise notice 'C';
            GET STACKED DIAGNOSTICS
              my_ex_state   = RETURNED_SQLSTATE,
              my_ex_message = MESSAGE_TEXT,
              my_ex_detail  = PG_EXCEPTION_DETAIL,
              my_ex_hint    = PG_EXCEPTION_HINT,
              my_ex_ctx     = PG_EXCEPTION_CONTEXT
            ;
            raise notice '% % % % %', my_ex_state, my_ex_message, my_ex_detail, my_ex_hint, my_ex_ctx;
            -- TODO C: insert this exception information in a logging table and commit
        end;
      end;
      $$;
  
      call x_transaction_try();
  </code></pre>
  
  <p>Why doesn't this stored procedure work? Why is it that we never see the output of <code>raise notice 'B'</code> and instead we go into the exception block? Is it possible to do what I have described above with a Postgres 11 stored procedure?</p>
  
  <p>Edit: This is a complete code sample. Paste the above complete code sample (including both the <code>create procedure</code> and <code>call</code> statements) into a sql file and run it in a Postgres 11.2 database to repro. The desired output would be for the function to print <code>A</code> then <code>B</code>, but instead it prints <code>A</code> then <code>C</code> along with the exception information.</p>
  
  <p>Also notice that if you comment out all of the exception handling block such that the function does not catch exceptions at all, then the function will output 'A' then 'B' without an exception occurring. This is why I titled the question the way that I did 'Can a Postgres Commit Exist in Procedure that has an Exception Block?'</p>