Re: Fast COPY FROM based on batch insert

On Mon, Aug 15, 2022 at 2:29 PM Andrey Lepikhov
<a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
> On 8/9/22 16:44, Etsuro Fujita wrote:
> >>> -1 foo
> >>> 1 bar
> >>> \.
> > ERROR: new row for relation "t1" violates check constraint "t1_f1positive"
> > DETAIL: Failing row contains (-1, foo).
> > CONTEXT: remote SQL command: INSERT INTO public.t1(f1, f2) VALUES
> > ($1, $2), ($3, $4)
> > COPY ft1, line 3
> >
> > In single-insert mode the error context information is correct, but in
> > batch-insert mode it isn’t (i.e., the line number isn’t correct).
> >
> > The error occurs on the remote side, so I'm not sure if there is a
> > simple fix. What I came up with is to just suppress error context
> > information other than the relation name, like the attached. What do
> > you think about that?

> I've spent many efforts to this problem too. Your solution have a
> rationale and looks fine.
> I only think, we should add a bit of info into an error report to
> simplify comprehension why don't point specific line here. For example:
> 'COPY %s (buffered)'
> or
> 'COPY FOREIGN TABLE %s'
>
> or, if instead of relname_only field to save a MultiInsertBuffer
> pointer, we might add min/max linenos into the report:
> 'COPY %s, line between %llu and %llu'

I think the latter is more consistent with the existing error context
information when in CopyMultiInsertBufferFlush(). Actually, I thought
this too, and I think this would be useful when the COPY FROM command
is executed on a foreign table. My concern, however, is the case when
the command is executed on a partitioned table containing foreign
partitions; in that case the input data would not always be sorted in
the partition order, so the range for an error-occurring foreign
partition might contain many lines with rows from other partitions,
which I think makes the range information less useful. Maybe I'm too
worried about that, though.

Best regards,
Etsuro Fujita