Search Postgresql Archives

Re: jsonb_set() strictness considered harmful to data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux