Postgresql – Rollback on \include error in psql

postgresqlpostgresql-9.2psqltransaction

I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this:

\include ../tables/table1.cre
\include ../tables/table2.cre
...
\include ../tables/table10.cre

I'm executing it using psql:

psql -X --set AUTOCOMMIT=off --set ON_ERROR_STOP=on -e --single-transaction -d my_db -f my_script.sql

The problem is that errors with the include meta command do not cause a transactiopn rollback. e.g. if some of tableX.cre files is missing, any changes before its include will be commited. However, if there's some SQL syntax error, everyting works as expected.

Is it possible to somehow handle include related errors and rollback the active transcation?

I'm using PostgreSQL 9.2.1.

Best Answer

Finally I've got an answer at pgsql-general mailing list by Adrian Klaver (link)

There is some kind of unexpected behavior with using both --set ON_ERROR_STOP=on and --single-transaction options. The ON_ERROR_STOP stops the processing, but does not rollback current transaction in case of \include error.

So, the solution for my initial problem will be removing the single-transaction option and wrapping explicit BEGIN/COMMIT statements around my main script.