Re: jsonb_set() strictness considered harmful to data
От | Tomas Vondra |
---|---|
Тема | Re: jsonb_set() strictness considered harmful to data |
Дата | |
Msg-id | 20191021195031.v4hzx5ytkjjsbfbv@development обсуждение исходный текст |
Ответ на | Re: jsonb_set() strictness considered harmful to data (Adrian Klaver <adrian.klaver@aklaver.com>) |
Ответы |
Re: jsonb_set() strictness considered harmful to data
Re: jsonb_set() strictness considered harmful to data |
Список | 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
В списке pgsql-general по дате отправления: