While working on the thread 'Rollback on include error in psql' I ran across something I am not sure with regards to ON_ERROR_ROLLBACK:
aklaver@panda:~> psql -d test -U aklaver -p 5452 --single-transaction --set ON_ERROR_STOP=on --set AUTOCOMMIT=off -f test_script.sql UPDATE 1 psql:test_script.sql:2: some_missing_file.sql: No such file or directory aklaver-2014-12-29 08:44:32.443 PST-0LOG: statement: BEGIN aklaver-2014-12-29 08:44:32.443 PST-0LOG: statement: UPDATE testtbl SET col = 'some other value'; aklaver-2014-12-29 08:44:32.444 PST-129436LOG: statement: COMMIT aklaver@panda:~> psql -d test -U aklaver -p 5452 --single-transaction --set ON_ERROR_ROLLBACK=1 --set AUTOCOMMIT=off -f test_script.sql UPDATE 1 psql:test_script.sql:2: some_missing_file.sql: No such file or directory UPDATE 1 aklaver-2014-12-29 08:44:42.656 PST-0LOG: statement: BEGIN aklaver-2014-12-29 08:44:42.657 PST-0LOG: statement: SAVEPOINT pg_psql_temporary_savepoint aklaver-2014-12-29 08:44:42.657 PST-0LOG: statement: UPDATE testtbl SET col = 'some other value'; aklaver-2014-12-29 08:44:42.658 PST-129437LOG: statement: RELEASE pg_psql_temporary_savepoint aklaver-2014-12-29 08:44:42.658 PST-129437LOG: statement: SAVEPOINT pg_psql_temporary_savepoint aklaver-2014-12-29 08:44:42.658 PST-129437LOG: statement: UPDATE testtbl SET col = 'yet another value'; aklaver-2014-12-29 08:44:42.659 PST-129437LOG: statement: RELEASE pg_psql_temporary_savepoint aklaver-2014-12-29 08:44:42.659 PST-129437LOG: statement: COMMIT aklaver@panda:~> psql -d test -U aklaver -p 5452 --single-transaction --set ON_ERROR_ROLLBACK=0 --set AUTOCOMMIT=off -f test_script.sql UPDATE 1 psql:test_script.sql:2: some_missing_file.sql: No such file or directory UPDATE 1 aklaver-2014-12-29 08:46:23.113 PST-0LOG: statement: BEGIN aklaver-2014-12-29 08:46:23.114 PST-0LOG: statement: SAVEPOINT pg_psql_temporary_savepoint aklaver-2014-12-29 08:46:23.114 PST-0LOG: statement: UPDATE testtbl SET col = 'some other value'; aklaver-2014-12-29 08:46:23.115 PST-129440LOG: statement: RELEASE pg_psql_temporary_savepoint aklaver-2014-12-29 08:46:23.115 PST-129440LOG: statement: SAVEPOINT pg_psql_temporary_savepoint aklaver-2014-12-29 08:46:23.115 PST-129440LOG: statement: UPDATE testtbl SET col = 'yet another value'; aklaver-2014-12-29 08:46:23.116 PST-129440LOG: statement: RELEASE pg_psql_temporary_savepoint aklaver-2014-12-29 08:46:23.116 PST-129440LOG: statement: COMMIT aklaver@panda:~> psql -d test -U aklaver -p 5452 --single-transaction --set ON_ERROR_ROLLBACK=off --set AUTOCOMMIT=off -f test_script.sql UPDATE 1 psql:test_script.sql:2: some_missing_file.sql: No such file or directory UPDATE 1 aklaver-2014-12-29 08:46:57.344 PST-0LOG: statement: BEGIN aklaver-2014-12-29 08:46:57.345 PST-0LOG: statement: UPDATE testtbl SET col = 'some other value'; aklaver-2014-12-29 08:46:57.346 PST-129443LOG: statement: UPDATE testtbl SET col = 'yet another value'; aklaver-2014-12-29 08:46:57.346 PST-129443LOG: statement: COMMIT So it seems you can turn ON_ERROR_ROLLBACK on with either 1 or 'on', but you can only turn it off with 'off'. With ON_ERROR_STOP 1/on and 0/off both seem to work. Is this expected? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
