Re: jsonb_set() strictness considered harmful to data

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