Message-ID: <9b293a07-c822-a326-6ff2-4a9e0e9e5549@postgrespro.ru>
Date: Mon, 15 Aug 2022 10:29:22 +0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.11.0
Subject: Re: Fast COPY FROM based on batch insert
Content-Language: en-US
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Zhihong Yu <zyu@yugabyte.com>,
 Amit Langote <amitlangote09@gmail.com>, tanghy.fnst@fujitsu.com,
 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>,
 Tomas Vondra <tomas.vondra@enterprisedb.com>, houzj.fnst@fujitsu.com
References: 
 <TYAPR01MB2990B8451B4C725242E270F7FE979@TYAPR01MB2990.jpnprd01.prod.outlook.com>
 <TYAPR01MB2990124D4D859FE8C7E984C8FE649@TYAPR01MB2990.jpnprd01.prod.outlook.com>
 <20210409004956.GN6592@telsasoft.com>
 <CAPmGK15rVM+61WHZ3xS3zS6mq7293zCexVMYV4p3xoyTYo8FLg@mail.gmail.com>
 <bc489202-9855-7550-d64c-ad2d83c24867@postgrespro.ru>
 <CAPmGK15Z1kvZE0oULa+JD_CKzc4NwWEZYAfJ26VkoVYg1md63g@mail.gmail.com>
 <45ad1b94-339b-a841-8885-67ebe6fc5bc1@postgrespro.ru>
 <CAPmGK14ov-MbQtwHuSK5ESUeL1csgCVC1MAAwZxgV=NWsBE2ZQ@mail.gmail.com>
 <57733596-5fd6-07ec-d65c-67411411f10e@postgrespro.ru>
 <CAPmGK145252jR4fygMTtJcUA4fCoKPkbykir9p9XupT6N903fQ@mail.gmail.com>
 <05bc8819-3e53-d884-a0d0-a17ffa5febef@postgrespro.ru>
 <CAPmGK15jd6gEt_XyswQ4uEG6XVdz9DNC5uqW1KK=1A_ehLTEYw@mail.gmail.com>
 <09ff35ff-291f-d9e9-a0ed-55e6bb9732fc@postgrespro.ru>
 <CAPmGK15WWjXsyBE05vDjvi37012WTiW-j=-Y8Uo=VqPRpsbOgg@mail.gmail.com>
 <CAPmGK15ZqOfE4N0PknZMzW96DRdZOA8ih_7591JMqAZu-Nhiiw@mail.gmail.com>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CAPmGK15ZqOfE4N0PknZMzW96DRdZOA8ih_7591JMqAZu-Nhiiw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/9b293a07-c822-a326-6ff2-4a9e0e9e5549%40postgrespro.ru>
Precedence: bulk

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'

-- 
Regards
Andrey Lepikhov
Postgres Professional