Message-ID: <ed89807e-0c15-02ac-b0aa-c01cdc5a2b2e@postgrespro.ru>
Date: Wed, 27 Jul 2022 10:42:28 +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>
 <TYAPR01MB29909FDCD92F5EEE68E37767FE649@TYAPR01MB2990.jpnprd01.prod.outlook.com>
 <CALNJ-vRi4DjjkCmvmXWWtULzY6PSeEAC9vSpXVNHyVoGEQ8HVQ@mail.gmail.com>
 <20210323032322.GW4203@telsasoft.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>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CAPmGK15jd6gEt_XyswQ4uEG6XVdz9DNC5uqW1KK=1A_ehLTEYw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ed89807e-0c15-02ac-b0aa-c01cdc5a2b2e%40postgrespro.ru>
Precedence: bulk

On 7/22/22 13:14, Etsuro Fujita wrote:
> On Fri, Jul 22, 2022 at 3:39 PM Andrey Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> Analyzing multi-level heterogeneous partitioned configurations I
>> realized, that single write into a partition with a trigger will flush
>> buffers for all other partitions of the parent table even if the parent
>> haven't any triggers.
>> It relates to the code:
>> else if (insertMethod == CIM_MULTI_CONDITIONAL &&
>>     !CopyMultiInsertInfoIsEmpty(&multiInsertInfo))
>> {
>>     /*
>>      * Flush pending inserts if this partition can't use
>>      * batching, so rows are visible to triggers etc.
>>      */
>>     CopyMultiInsertInfoFlush(&multiInsertInfo, resultRelInfo);
>> }
>>
>> Why such cascade flush is really necessary, especially for BEFORE and
>> INSTEAD OF triggers?
> 
> BEFORE triggers on the chosen partition might query the parent table,
> not just the partition, so I think we need to do this so that such
> triggers can see all the rows that have been inserted into the parent
> table until then.
if you'll excuse me, I will add one more argument.
It wasn't clear, so I've made an experiment: result of a SELECT in an 
INSERT trigger function shows only data, existed in the parent table 
before the start of COPY.
So, we haven't tools to access newly inserting rows in neighboring 
partition and don't need to flush tuple buffers immediately.
Where am I wrong?

-- 
Regards
Andrey Lepikhov
Postgres Professional