From: | Alexander Staubo <alex(at)purefiction(dot)net> |
---|---|
To: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: skip duplicate key error during inserts |
Date: | 2006-10-27 13:41:17 |
Message-ID: | ED860713-4E78-44E1-A18B-99B827941DC3@purefiction.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jorge Godoy | 2006-10-27 13:56:38 | Re: plpython |
Previous Message | Alvaro Herrera | 2006-10-27 13:40:03 | Re: Send email from PostgreSQL, may I ? |