Re: Copy From & Insert UNLESS

On Mon, Feb 06, 2006 at 05:08:38PM -0500, Alon Goldshuv wrote:
> The proposal is neat, however, I am not too
> excited about handling errors in such high granularity, as far as the user
> is concerned. I am more on the same line with Tom Lane's statement in
> Simon's thread (Practical error logging for very large COPY statements):
> 
> "The general problem that needs to be solved is "trap any error that
> occurs during attempted insertion of a COPY row, and instead of aborting
> the copy, record the data and the error message someplace else".  Seen
> in that light, implementing a special path for uniqueness violations is
> pretty pointless."

I think I would be inclined to actually agree with that, which is why I proposed
a special path for constraint violations in general as opposed to just
uniqueness. However, I can understand if you remain unmoved. ;)


> But, I definitely share your struggle to finding a good way to handle those
> unique/FK constraints...

Aye, :-(


> As far as UNIQUE goes, maybe there is a good way to do a bt scan against the
> index table right before the simple_heap_insert call?

Yes, but I believe some additional locking is required in order to make that
safe. Not that that would kill it, but I think there is a better way; I'm
cooking up a general proposal for refactoring unique constraints, so I'm hoping
something along those lines will aid any patch attempting to solving this
problem[copy error/violation management].
-- 
Regards, James William Pye