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.
