Enabling `ON_ERROR_STOP` doesn't work with `RAISE` statement in PostgreSQL
Asked Answered
M

0

0

First, I enabled ON_ERROR_STOP to stop the execution immediately after error as shown below:

\set ON_ERROR_STOP on

Because the doc about ON_ERROR_STOP says below:

By default, command processing continues after an error. When this variable is set to on, processing will instead stop immediately

So, I created test table with num column, then inserted the row whose num is 0 as shown below:

CREATE TABLE test (
  num INTEGER
);

INSERT INTO test (num) VALUES (0);

Then, I ran the script which raises INFO error with RAISE statement then, increments num in test table by 1 as shown below. *I also tried RAISE statement with DEBUG, LOG, NOTICE, WARNING, EXCEPTION or nothing:

DO LANGUAGE plpgsql $$
BEGIN
  RAISE INFO 'A custom error!';
  UPDATE test SET num = num + 1;
END
$$;

But, num was incremented to 1 as shown below. *num was not incremented to 1 only with EXCEPTION or nothing:

postgres=# SELECT num FROM test;
 num
-----
   1
(1 row)

So, how can I make ON_ERROR_STOP work with RAISE statement properly?

Maxey answered 7/1 at 0:0 Comment(6)
1) From Reporting Errors: EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. 2) It works for me if I use RAISE EXCEPTION.Misplace
@AdrianKlaver So, enabling ON_ERROR_STOP doesn't affect to the behaviour of INFO, DEBUG, LOG, NOTICE and WARNING errors? I mean enabling ON_ERROR_STOP doesn't stop INFO, DEBUG, LOG, NOTICE and WARNING errors immediately after error?Maxey
Yes, because as the docs state they are not errors they are just messages.Misplace
@AdrianKlaver I thought INFO, DEBUG, LOG, NOTICE and WARNING were errors because the doc says The level option specifies the error severity.Maxey
Yes but EXCEPTION is the only one that actually raises an error.Misplace
From the manual: EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. See postgresql.org/docs/current/…Pleiades

© 2022 - 2024 — McMap. All rights reserved.