CODEWITHSUNDEEP
postgresqlpsqlpostgresql-9.6
das-g
das-g

Reputation: 9994

How to suspend PostgreSQL's ON_ERROR_STOP for just part of a psql script?

I'm maintaining a psql script which I usually want to immediately abort with a non-zero exit code when any part of it fails.

Thus I'm considering to either place

\set ON_ERROR_STOP on

at the beginning, or to instruct users to run the script with

 psql -v ON_ERROR_STOP=on -f my_script.sql

However, there is a part of the script that deliberately fails (and gets rolled back). As the script is for education and demonstration purposes, and as that part demonstrates a CONSTRAINT actually working as it should (by making a subsequent constraint-violating INSERT fail), I can't really "fix" that part to not fail, so the accepted answer from How to save and restore value of ON_ERROR_STOP? doesn't solve my problem.

Thus, I'd like to disable ON_ERROR_STOP before that part and restore the setting after the part. If I know that ON_ERROR_STOP is enabled in general, this is easy:

\set ON_ERROR_STOP off
BEGIN;
-- [ part of the script that purposfully fails ]
ROLLBACK;
\set ON_ERROR_STOP on

or

\unset ON_ERROR_STOP
BEGIN;
-- [ part of the script that purposefully fails ]
ROLLBACK;
\set ON_ERROR_STOP on

However, this blindly (re-)enables ON_ERROR_STOP, whether it was enabled before or not.

\set previous_ON_ERROR_STOP :ON_ERROR_STOP
\unset ON_ERROR_STOP
BEGIN;
-- [ part of the script that purposefully fails ]
ROLLBACK;
\set ON_ERROR_STOP :previous_ON_ERROR_STOP

works if ON_ERROR_STOP has previously been explicitly disabled (e.g., set to off) but fails if it was unset (and thus just implicitly disabled).

I'd like the script to remain backwards compatible to PostgreSQL 9.x, so I can't yet use the \if meta commands introduced in PostgreSQL 10.

Upvotes: 4

Views: 5612

Answers (2)

cabbage
cabbage

Reputation: 1

\set CACHE_ON_ERROR_STOP :ON_ERROR_STOP

SELECT CASE 
  WHEN :'CACHE_ON_ERROR_STOP' = ':CACHE_ON_ERROR_STOP' THEN 'off'
  WHEN :'CACHE_ON_ERROR_STOP'::BOOLEAN is true THEN 'on'
  ELSE 'off'
END::boolean AS CACHE_ON_ERROR_STOP
\gset

\set ON_ERROR_STOP off

-- your code here

\set ON_ERROR_STOP :CACHE_ON_ERROR_STOP

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247235

I don't think you can do that.

What you could do, however, is to use a PL/pgSQL block to run the statement and catch and report the error, somewhat like this:

DO
$$BEGIN
   INSERT INTO mytab VALUES (...);
EXCEPTION
   WHEN integrity_constraint_violation THEN
      RAISE NOTICE 'Caught error: %', SQLERRM;
END;$$;

That will report the error, but it won't cause psql to stop.

Upvotes: 4

Related Questions