> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Seems nested transactions are not required if we load
> > > each COPY line in its own transaction, like we do with
> > > INSERT from pg_dump.
> > 
> > I don't think that's an acceptable answer.  Consider
> 
> Oh, very good point.  "Requires nested transactions" added to TODO.

Also add performance issue with per-line-commit...

Also-II - there is more common name for required feature - savepoints.

Vadim

Thu, 3 Jan 2002 13:11:34 -0800 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
Re: Bulkloading using COPY - ignore duplicates?
Mikheev, Vadim wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > Seems nested transactions are not required if we load
> > > > each COPY line in its own transaction, like we do with
> > > > INSERT from pg_dump.
> > > 
> > > I don't think that's an acceptable answer.  Consider
> > 
> > Oh, very good point.  "Requires nested transactions" added to TODO.
> 
> Also add performance issue with per-line-commit...
> 
> Also-II - there is more common name for required feature - savepoints.

OK, updated TODO to prefer savepoints term.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 3 Jan 2002 16:15:04 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bulkloading using COPY - ignore duplicates?
>>>Bruce Momjian said:
 > Mikheev, Vadim wrote:
 > > > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
 > > > > > Seems nested transactions are not required if we load
 > > > > > each COPY line in its own transaction, like we do with
 > > > > > INSERT from pg_dump.
 > > > > 
 > > > > I don't think that's an acceptable answer.  Consider
 > > > 
 > > > Oh, very good point.  "Requires nested transactions" added to TODO.
 > > 
 > > Also add performance issue with per-line-commit...
 > > 
 > > Also-II - there is more common name for required feature - savepoints.
 > 
 > OK, updated TODO to prefer savepoints term.

Now, how about the same functionality for

INSERT into table1 SELECT * from table2 ... WITH ERRORS;

Should allow the insert to complete, even if table1 has unique indexes and we 
try to insert duplicate rows. Might save LOTS of time in bulkloading scripts 
not having to do single INSERTs.

Guess all this will be available in 7.3?

Daniel


Fri, 04 Jan 2002 09:36:01 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: Bulkloading using COPY - ignore duplicates? 
> Now, how about the same functionality for
>
> INSERT into table1 SELECT * from table2 ... WITH ERRORS;
>
> Should allow the insert to complete, even if table1 has unique indexes and
we
> try to insert duplicate rows. Might save LOTS of time in bulkloading
scripts
> not having to do single INSERTs.

1. I prefer Oracle' (and others, I believe) way - put statement(s) in PL
block and define
for what exceptions (errors) what actions should be taken (ie IGNORE for
NON_UNIQ_KEY
error, etc).

2. For INSERT ... SELECT statement one can put DISTINCT in select' target
list.

> Guess all this will be available in 7.3?

We'll see.

Vadim



Thu, 3 Jan 2002 23:47:36 -0800
"Vadim Mikheev" <vmikheev@sectorbase.com>
Re: Bulkloading using COPY - ignore duplicates? 
>>>"Vadim Mikheev" said:
 > 1. I prefer Oracle' (and others, I believe) way - put statement(s) in PL
 > block and define
 > for what exceptions (errors) what actions should be taken (ie IGNORE for
 > NON_UNIQ_KEY
 > error, etc).

Some people prefer 'pure' SQL. Anyway, it can be argued which is worse - the 
usage of non-SQL language, or usage of extended SQL language. I guess the SQL 
standard does not provide for such functionality?

 > 2. For INSERT ... SELECT statement one can put DISTINCT in select' target
 > list.

With this construct, you are effectively copying rows from one table to 
another - or constructing rows from various sources (constants, other tables 
etc) and inserting these in the table. If the target table has unique indexes 
(or constraints), and some of the rows returned by SELECT violate the 
restrictions - you are supposed to get errors - and unfortunately the entire 
INSERT is aborted. I fail to see how DISTINCT can help here... Perhaps it is 
possible to include checking for already existing tuples in the destination 
table in the select... but this will significantly increase the runtime, 
especially when the destination table is huge.

My idea is to let this INSERT statement insert as much of its rows as 
possible, eventually returning NOTICEs or ignoring the errors (with an IGNORE 
ERRORS syntax for example :)

I believe all this functionality will have to consider the syntax firts.

Daniel


Fri, 04 Jan 2002 10:07:21 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: Bulkloading using COPY - ignore duplicates? 
Vadim Mikheev wrote:
> > Now, how about the same functionality for
> >
> > INSERT into table1 SELECT * from table2 ... WITH ERRORS;
> >
> > Should allow the insert to complete, even if table1 has unique indexes and
> we
> > try to insert duplicate rows. Might save LOTS of time in bulkloading
> scripts
> > not having to do single INSERTs.
> 
> 1. I prefer Oracle' (and others, I believe) way - put statement(s) in PL
> block and define
> for what exceptions (errors) what actions should be taken (ie IGNORE for
> NON_UNIQ_KEY
> error, etc).

Added to TODO:

	* Allow command blocks that can ignore certain types of errors

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 4 Jan 2002 13:16:46 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bulkloading using COPY - ignore duplicates?