Re: [GENERAL] skip duplicate key error during inserts
-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- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] skip duplicate key error during inserts
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] skip duplicate key error during inserts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/27/06 06:48, Gurjeet Singh wrote: > On 10/27/06, Ron Johnson <[EMAIL PROTECTED]> 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- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] skip duplicate key error during inserts
On 10/27/06, Ron Johnson <[EMAIL PROTECTED]> 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 -- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] skip duplicate key error during inserts
On 10/27/06 05:10, Gurjeet Singh wrote: > On 10/27/06, Ron Johnson <[EMAIL PROTECTED]> 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 signature.asc Description: OpenPGP digital signature
Re: [GENERAL] skip duplicate key error during inserts
On 10/27/06, Ron Johnson <[EMAIL PROTECTED]> wrote: That doesn't help at all during multi-table transactionsWhat 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 onpostgres=# insert into t1 values ( 1 ); INSERT 0 1postgres=# insert into t1 values ( 1 ); ERROR: duplicate key violates unique constraint "t1_pkey" postgres=# insert into t2 values ( 1 ); INSERT 0 1postgres=# insert into t2 values ( 1 ); ERROR: duplicate key violates unique constraint "t2_pkey"postgres=# commit; COMMITpostgres=# select * from t1; a--- 1(1 row) postgres=# select * from t2; a --- 1(1 row) postgres=# want the loader to kick duplicates out to anexception file. Now you are asking for a completely new feature!!!Regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] skip duplicate key error during inserts
-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- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] skip duplicate key error during inserts
If you are using psql, I'd recommend using '\set ON_ERROR_ROLLBACK on'.HTH-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com On 10/22/06, Ron Johnson <[EMAIL PROTECTED]> wrote: -BEGIN PGP SIGNED MESSAGE-Hash: SHA1On 10/22/06 04:39, Jeffrey Webster wrote:> On 10/20/06, Jean-Christophe Roux <[EMAIL PROTECTED]> 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 USAIs "common sense" really valid?For example, it is "common sense" to white-power racists thatwhites 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(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] skip duplicate key error during inserts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/22/06 04:39, Jeffrey Webster wrote: > On 10/20/06, Jean-Christophe Roux <[EMAIL PROTECTED]> 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- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] skip duplicate key error during inserts
On 10/20/06, Jean-Christophe Roux <[EMAIL PROTECTED]> 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.) Best of luck.
Re: [GENERAL] skip duplicate key error during inserts
On 10/20/06, Jean-Christophe Roux <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] skip duplicate key error during inserts
Hello,I have a table like this:create table dummy (value integer primary key);and I insert a row like thisinsert 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
