Database Reference
In-Depth Information
How it works...
ON_ERROR_STOP
is a psql special variable that controls the behavior of psql as it executes
in script mode. When this variable is set, an SQL error will generate an OS return code 3,
whereas other OS-related errors will return code 1.
There's more...
You can place some psql commands in a profile that will get executed when you run psql. Adding
ON_ERROR_STOP
into your profile will ensure that this setting is applied to all psql sessions:
$ $EDITOR ~/.psqlrc
\set ON_ERROR_STOP
You can forcibly override this, and request psql to execute without a profile by using
-X
, which
is probably the safest thing to do for batch execution of scripts.
Performing actions on many tables
As a database administrator, you will often need to apply multiple commands as part of the
same overall task. That is one of the following:
F
Many different actions on multiple tables
F
Same action, multiple tables
F
Same action, multiple tables, in parallel
F
Different actions, one on each table, in parallel
The first is the general case where you need to make a set of coordinated changes. The
solution is "write a script" as we've already discussed. We can also call this static scripting,
because you write the script manually, and then execute it.
The second type of task can be achieved very simply with dynamic scripts, where we write a
script that writes a script. That technique is the main topic of this recipe.
Performing actions in parallel sounds really cool, and it would be useful if it was easy. In some
ways it is, though trying to run multiple tasks concurrently and trap and understand all the
errors is much harder. And if you're thinking it won't matter if you don't check for errors, think
again. If you run tasks in parallel, then you cannot run them inside the same transaction; so
you definitely need error checking.
Don't worry; running parallel is usually much easier than that, and we'll explain how after
a few basics.
