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@xxxxxxxxxxx
