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(dot)klaver(at)aklaver(dot)com