5

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.

1
  • I would not generally expect psql meta command errors to cause a transaction rollback. I can see why you might in this case, but it's not clear that's the generally correct behaviour. Interesting issue though. You may have more luck raising this one on the pgsql-general mailing list; if you do so, include a link to this post please, but make sure your mailing list post is a complete explanation its self. Dec 27, 2014 at 0:40

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.