Database Reference
In-Depth Information
Please note the return messages in the following few commands:
postgres=# BEGIN;
BEGIN
postgres=# BEGIN;
WARNING: there is already a transaction in progress
BEGIN
postgres=# COMMIT;
COMMIT
postgres=# COMMIT;
WARNING: there is no transaction in progress
COMMIT
PostgreSQL takes the first or
outermost
BEGIN
but also the first, which in this case is the
innermost
COMMIT
. So, if you have used transaction-control commands in your script, then
wrapping them again in a higher level script or command can cause problems.
Warning : PostgreSQL accepts, but does not act on nested transactional control
commands. The commands after the first commit will be assumed to be
transactions in their own right and will persist, should the script fail. Be careful.
There's more...
The following commands cannot be included in a script that uses transactions in the way
described:
F
CREATE
DATABASE
/
DROP
DATABASE
F
CREATE
TABLESPACE
/
DROP
TABLESPACE
F
CREATE
INDEX
CONCURRENTLY
F
VACUUM
None of those actions need to be run manually on a regular basis within complex programs,
so shouldn't be a problem for you.
Writing a psql script that exits on first error
The default mode for the psql script tool is to continue processing when it finds an error.
That sounds dumb, though it exists for historical compatibility only. There are some easy and
mostly permanent ways around that, so let's look at them.
