On Oct 27, 2006, at 14:56 , Ron Johnson wrote:
I think you completely missed that I am recommending using '\set
ON_ERROR_ROLLBACK on' in psql.
Please refer to my previous post and see the effect of the
following line:
postgres=# \set ON_ERROR_ROLLBACK on
But I do *not* want my whole transaction to roll back!!
That is not what is happening. From the documentation:
ON_ERROR_ROLLBACK
When on, if a statement in a transaction block generates an error,
the error is ignored and the transaction continues. When
interactive, such errors are only ignored in interactive sessions,
and not when reading script files. When off (the default), a
statement in a transaction block that generates an error aborts the
entire transaction. The on_error_rollback-on mode works by issuing
an implicit SAVEPOINT for you, just before each command that is in
a transaction block, and rolls back to the savepoint on error.
So with on_error_rollback the transaction continues regardless of
errors:
# begin;
# \set ON_ERROR_ROLLBACK on
# insert into t2 values ( 1 );
ERROR: duplicate key violates unique constraint "t2_pkey"
alex # insert into t2 values ( 1 );
ERROR: duplicate key violates unique constraint "t2_pkey"
With on_error_rollback disabled, the transaction is implicitly aborted:
# begin;
# \set ON_ERROR_ROLLBACK on
# insert into t2 values ( 1 );
ERROR: duplicate key violates unique constraint "t2_pkey"
# insert into t2 values ( 1 );
ERROR: current transaction is aborted, commands ignored until end of
transaction block
The wording of the option (in combination with the value "on") is
admittedly confusing. It's really "on_error_continue".
Alexander.
