CODEWITHSUNDEEP
transactionspostgresql
dland
dland

Reputation: 4419

Detecting errors to decide whether to rollback or commit

I'm trying to be as lazy as possible by generating a series of SQL commands in a file to feed to psql for processing. In a nutshell, I'm loading a series of import tables from outside sources (already done, via COPY), and then in a final step, deleting/updating/inserting records into the primary tables (which is functionally also done).

The only thing from preventing me from succeeding (and being able to do everything from a series of commands in a shell script) is the fact that sometimes the operation has referential integrity errors, and thus I have to roll back everything until the source can be identified and corrected.

So is there any way of knowing, from within a script processed by psql, if an error has occurred and to perform a rollback? And if there were no errors, commit.

I can always solve the problem by switching to a higher-level language, open a connection and run each statement and check for errors, but it's just all that more make-work code that I'd like to avoid if possible.

Upvotes: 2

Views: 399

Answers (1)

Bridgier
Bridgier

Reputation:

If you're using psql, could you use the -1 command line option, and start your file with:

\set ON_ERROR_STOP

That should wrap the whole session in a transaction, and bail immediately once an error is encountered.

Upvotes: 2

Related Questions