时间戳

首页 > 解决方案 > How to suspend PostgreSQL's ON_ERROR_STOP for just part of a psql script?

postgresql - 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.

标签： postgresqlpsqlpostgresql-9.6

解决方案

我不认为你能做到这一点。

但是，您可以做的是使用 PL/pgSQL 块来运行语句并捕获并报告错误，有点像这样：

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

这将报告错误，但不会导致psql停止。

推荐阅读