
> > Would this seem a reasonable thing to do? Does anyone rely on COPY
> > FROM causing an ERROR on duplicate input?
> 
> Yes.  This change will not be acceptable unless it's made an optional
> (and not default, IMHO, though perhaps that's negotiable) feature of
> COPY.
> 
> The implementation might be rather messy too.  I don't much 
> care for the
> notion of a routine as low-level as bt_check_unique knowing that the
> context is or is not COPY.  We might have to do some restructuring.
> 
> > Would:
> >  WITH ON_DUPLICATE = CONTINUE|TERMINATE (or similar)
> > need to be added to the COPY command (I hope not)?
> 
> It occurs to me that skip-the-insert might be a useful option for
> INSERTs that detect a unique-key conflict, not only for COPY.  (Cf.
> the regular discussions we see on whether to do INSERT first or
> UPDATE first when the key might already exist.)  Maybe a SET variable
> that applies to all forms of insertion would be appropriate.

Imho yes, but:
I thought that the problem was, that you cannot simply skip the 
insert, because at that time the tuple (pointer) might have already 
been successfully inserted into an other index/heap, and thus this was 
only sanely possible with savepoints/undo.

An idea would probably be to at once mark the new tuple dead, and
proceed
normally?

Andreas

Mon, 1 Oct 2001 16:06:39 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Bulkloading using COPY - ignore duplicates? 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> I thought that the problem was, that you cannot simply skip the 
> insert, because at that time the tuple (pointer) might have already 
> been successfully inserted into an other index/heap, and thus this was 
> only sanely possible with savepoints/undo.

Hmm, good point.  If we don't error out the transaction then that tuple
would become good when we commit.  This is nastier than it appears.

			regards, tom lane

Mon, 01 Oct 2001 10:09:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bulkloading using COPY - ignore duplicates? 
I said:
> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> I thought that the problem was, that you cannot simply skip the 
>> insert, because at that time the tuple (pointer) might have already 
>> been successfully inserted into an other index/heap, and thus this was 
>> only sanely possible with savepoints/undo.

> Hmm, good point.  If we don't error out the transaction then that tuple
> would become good when we commit.  This is nastier than it appears.

On further thought, I think it *would* be possible to do this without
savepoints, but it'd take some restructuring of the index AM API.
What'd have to happen is that a unique index could not raise an elog
ERROR when it detects a uniqueness conflict.  Instead, it'd return a
uniqueness-conflict indication back to its caller.  This would have
to propagate up to the level of the executor.  At that point we'd make
the choice of whether to raise an error or not.  If not, we'd need to
modify the just-created tuple to mark it deleted by the current
transaction.  We can't remove it, since that would leave any
already-created entries in other indexes pointing to nothing.  But
marking it deleted by the same xact and command ID that inserted it
would leave things in a valid state until VACUUM comes along to do the
janitorial work.

To support backoff in the case of a conflict during UPDATE, it'd also be
necessary to un-mark the prior version of the tuple, which we'd already
marked as deleted.  This might create some concurrency issues in case
there are other updaters waiting to see if we commit or not.  (The same
issue would arise for savepoint-based undo, though.)  We might want to
punt on that part for now.

The effects don't stop propagating there, either.  The decision not to
insert the tuple must be reported up still further, so that the executor
knows not to run any AFTER INSERT/UPDATE triggers and knows not to count
the tuple as inserted/updated for the command completion report.

In short, quite a lot of code to touch to make this happen ...

			regards, tom lane

Mon, 01 Oct 2001 13:21:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bulkloading using COPY - ignore duplicates? 
> -----Original Message-----
> From: Tom Lane
>
> I said:
> > "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> >> I thought that the problem was, that you cannot simply skip the
> >> insert, because at that time the tuple (pointer) might have already
> >> been successfully inserted into an other index/heap, and thus this was
> >> only sanely possible with savepoints/undo.
>
> > Hmm, good point.  If we don't error out the transaction then that tuple
> > would become good when we commit.  This is nastier than it appears.
>
> On further thought, I think it *would* be possible to do this without
> savepoints,

It's a very well known issue that the partial rolloback functionality is
a basis of this kind of problem and it's the reason I've mentioned that
UNDO functionality has the highest priority. IMHO we shouldn't
implement a partial rolloback functionality specific to an individual
problem.

regards,
Hiroshi Inoue


Tue, 2 Oct 2001 05:58:26 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: Bulkloading using COPY - ignore duplicates? 