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