Re: jsonb_set() strictness considered harmful to data - Mailing list pgsql-general

On Mon, Oct 21, 2019 at 08:06:46AM -0700, Adrian Klaver wrote:
>On 10/20/19 11:07 PM, Tomas Vondra wrote:
>>On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote:
>
>>
>>True. And AFAIK catching exceptions is not really possible in some code,
>>e.g. in stored procedures (because we can't do subtransactions, so no
>>exception blocks).
>>
>
>Can you explain the above to me as I thought there are exception 
>blocks in stored functions and now sub-transactions in stored 
>procedures.
>

Sorry for the confusion - I've not been particularly careful when
writing that response.

Let me illustrate the issue with this example:

    CREATE TABLE t (a int);

    CREATE OR REPLACE PROCEDURE test() LANGUAGE plpgsql AS $$
    DECLARE
       msg TEXT;
    BEGIN
      -- SAVEPOINT s1;
      INSERT INTO t VALUES (1);
      -- COMMIT;
    EXCEPTION
      WHEN others THEN
        msg := SUBSTR(SQLERRM, 1, 100);
        RAISE NOTICE 'error: %', msg;
    END; $$;

    CALL test();

If you uncomment the SAVEPOINT, you get

    NOTICE:  error: unsupported transaction command in PL/pgSQL

because savepoints are not allowed in stored procedures. Fine.

If you uncomment the COMMIT, you get

    NOTICE:  error: cannot commit while a subtransaction is active

which happens because the EXCEPTION block creates a subtransaction, and
we can't commit when it's active.

But we can commit outside the exception block:

    CREATE OR REPLACE PROCEDURE test() LANGUAGE plpgsql AS $$
    DECLARE
       msg TEXT;
    BEGIN
      BEGIN
        INSERT INTO t VALUES (1);
      EXCEPTION
        WHEN others THEN
          msg := SUBSTR(SQLERRM, 1, 100);
          RAISE NOTICE 'error: %', msg;
       END;
       COMMIT;
    END; $$;


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services