Re: jsonb_set() strictness considered harmful to data

[Tomas Vondra]

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