Обсуждение: skip duplicate key error during inserts

On 10/20/06, Jean-Christophe Roux <jcxxr@yahoo.com> wrote:
> Hello,
> I have a table like this:
> create table dummy (value integer primary key);
> and I insert a row like this
> insert into dummy values(0);
> then I want to insert three rows:
> insert into dummy values(0);
> insert into dummy values(1);
> insert into dummy values(2);

> none of them will be inserted because the first insert is a primary key
> violation. How can I have postgreSQL not mind about the error and proceed to
> the next insert. I could send the inserts one at a time but bundling them
> speeds up the process.
> Thanks

if this is bulk insert,
1. insert into scratch table:
create temp table scratch_foo as select * from foo limit 0;
2. insert into scratch, etc.
3. insert into foo select distinct on * from scratch_foo <-- season to taste

merlin

I feel your pain... However, there is no way to do this (currently?).  Some possible solutions had been suggested some time back by a colleague, but they were rejected.  It requires a source code change (and not a simple one, at that) to implement.

Until a concensus is reached by the primary contributors there is no easy answer.

We've resorted to batch loading and parsing error messages to load all data between primary key violations.  It's still considerably more efficient than single inserts.

(we've got the code to accomplish what you're asking about in the attic... hopefully something will come of it some day.)

Best of luck.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/22/06 04:39, Jeffrey Webster wrote:
> On 10/20/06, Jean-Christophe Roux <jcxxr@yahoo.com> wrote:
>>
>> Hello,
>> [snip]
>>
>
>
>
> none of them will be inserted because the first insert is a primary key
>> violation. How can I have postgreSQL not mind about the error and
>> proceed to
>> the next insert. I could send the inserts one at a time but bundling them
>> speeds up the process.
>> Thanks
>>
>>
> I feel your pain... However, there is no way to do this (currently?).  Some
> possible solutions had been suggested some time back by a colleague, but
> they were rejected.  It requires a source code change (and not a simple
> one,
> at that) to implement.
>
> Until a concensus is reached by the primary contributors there is no easy
> answer.
>
> We've resorted to batch loading and parsing error messages to load all data
> between primary key violations.  It's still considerably more efficient
> than
> single inserts.
>
> (we've got the code to accomplish what you're asking about in the attic...
> hopefully something will come of it some day.)

The generalized version of this issue (transaction totally fails on
any error) is extremely painful.

Most RDBMSs (well, ok, the other RDBMSs that *I* have worked with)
don't do that, and there's a lot of code written in the form:

  INSERT INTO ...
  IF PK-ERROR THEN
     UPDATE
  END-IF.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFO0buS9HxQb37XmcRAughAKCrD6o+ibwr7fclE+wBXnUgX3tNDwCgw8Or
5rGcfhYoAH8giSjwwSqHJe8=
=aczL
-----END PGP SIGNATURE-----

If you are using psql, I'd recommend using '\set ON_ERROR_ROLLBACK on'.

HTH

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


That doesn't help at all during multi-table transactions or
bulk-loads where you want the loader to kick duplicates out to an
exception file.

On 10/27/06 04:41, Gurjeet Singh wrote:
>> If you are using psql, I'd recommend using '\set ON_ERROR_ROLLBACK on'.
[snip]
> The generalized version of this issue (transaction totally fails on
> any error) is extremely painful.
>
> Most RDBMSs (well, ok, the other RDBMSs that *I* have worked with)
> don't do that, and there's a lot of code written in the form:
>
>   INSERT INTO ...
>   IF PK-ERROR THEN
>      UPDATE
>   END-IF.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFQde1S9HxQb37XmcRAnqJAKCGgg3CDeGWuvKNFBZCbrAdSESqjACdFZSI
virSEhXIR8SVDK2CXKbgN3Y=
=0Qrc
-----END PGP SIGNATURE-----

On 10/27/06, Ron Johnson <ron.l.johnson@cox.net> wrote:


What problem do you think you would face in multi-table scenario? I tried the following and it worked for me; hope this is what you meant by multi-table transactions:

postgres=# begin;
BEGIN
postgres=# create table t1 ( a int primary key );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for tab le "t1"
CREATE TABLE
postgres=# create table t2 ( a int primary key );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for tab le "t2"
CREATE TABLE
postgres=# \set ON_ERROR_ROLLBACK on
postgres=# insert into t1 values ( 1 );
INSERT 0 1
postgres=# insert into t1 values ( 1 );
ERROR:  duplicate key violates unique constraint "t1_pkey"
postgres=# insert into t2 values ( 1 );
INSERT 0 1
postgres=# insert into t2 values ( 1 );
ERROR:  duplicate key violates unique constraint "t2_pkey"
postgres=# commit;
COMMIT
postgres=# select * from t1;
 a
---
 1
(1 row)

postgres=# select * from t2;
 a
---
 1
(1 row)

postgres=#


Now you are asking for a completely new feature!!!

Regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

On 10/27/06 05:10, Gurjeet Singh wrote:
> On 10/27/06, Ron Johnson <ron.l.johnson@cox.net> wrote:
>
> That doesn't help at all during multi-table transactions
>
>
> What problem do you think you would face in multi-table scenario? I tried
> the following and it worked for me; hope this is what you meant by
> multi-table transactions:
>
> postgres=# begin;
> BEGIN
> postgres=# create table t1 ( a int primary key );
[snip]
> postgres=# commit;
> COMMIT
> postgres=# select * from t1;
> a
> ---
> 1
> (1 row)
>
> postgres=# select * from t2;
> a
> ---
> 1
> (1 row)
>
> postgres=#

Dueling examples.  Attached are two examples of errors.

BTW, PG 8.1.5.

> want the loader to kick duplicates out to an
>> exception file.
>>
>
> Now you are asking for a completely new feature!!!

But easily doable if errors don't puke a transaction.

--
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
toll_archive=# begin;
BEGIN
Time: 0.124 ms
toll_archive=# create table foo (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
Time: 281.424 ms
toll_archive=# commit;
COMMIT
Time: 15.864 ms
toll_archive=# begin;
BEGIN
Time: 0.204 ms
toll_archive=# insert into foo (1);
ERROR:  syntax error at or near "1" at character 18
LINE 1: insert into foo (1);
                         ^
toll_archive=# insert into foo values (1);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
toll_archive=# insert into foo values (1);
INSERT 0 1
Time: 0.985 ms
toll_archive=# insert into foo values (2);
INSERT 0 1
Time: 0.233 ms
toll_archive=# insert into foo values (3);
INSERT 0 1
Time: 0.230 ms
toll_archive=# insert into foo values (1);
ERROR:  duplicate key violates unique constraint "foo_pkey"
toll_archive=# insert into foo values (4);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/27/06 06:48, Gurjeet Singh wrote:
> On 10/27/06, Ron Johnson <ron.l.johnson@cox.net> wrote:
>>
>> Dueling examples.  Attached are two examples of errors.
>
>
> 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!!

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFQgH8S9HxQb37XmcRAsW+AKCOzz6WidLYlS96oacxCL0qbaxfOACgqz/H
/RA16NFFwi82JyibP58tmUI=
=jNzL
-----END PGP SIGNATURE-----

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/27/06 08:41, Alexander Staubo wrote:
> 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

That'll teach me not to assume what a statement means.

>> errors are only ignored in interactive sessions, and not when reading
>> script files.

Hmmm.

I guess COPY will fail also, instead of throwing a warning and
continuing.

>>               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.
[snip]
>
> The wording of the option (in combination with the value "on") is
> admittedly confusing. It's really "on_error_continue".

Isn't that the truth.  :\

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFQhxsS9HxQb37XmcRAkhIAKDkLRIbOOlNCN4nC1N2DllKfKo5wQCfS/Gk
xHRyVEytS3cQK9y2F8bXeGw=
=wzfH
-----END PGP SIGNATURE-----