Re: jsonb_set() strictness considered harmful to data

On 10/21/19 12:50 PM, Tomas Vondra wrote:
> 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; $$;

You can do something like the below though:

CREATE TABLE t (a int PRIMARY KEY);

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

test_(postgres)# CALL test();
CALL
test_(postgres)# select * from t;
  a
---
  1
(1 row)

test_(postgres)# CALL test();
NOTICE:  error: duplicate key value violates unique constraint "t_pkey"
CALL
test_(postgres)# select * from t;
  a
---
  2
(1 row)


> 
> 
> regards
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com