Re: Fast COPY FROM based on batch insert

pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Zhihong Yu <zyu@yugabyte.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: tanghy.fnst@fujitsu.com, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: houzj.fnst@fujitsu.com
Subject: Re: Fast COPY FROM based on batch insert
Date: Wed, 27 Jul 2022 10:42:28 +0500
Message-ID: <ed89807e-0c15-02ac-b0aa-c01cdc5a2b2e@postgrespro.ru> (raw)
In-Reply-To: <CAPmGK15jd6gEt_XyswQ4uEG6XVdz9DNC5uqW1KK=1A_ehLTEYw@mail.gmail.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>

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

view thread (39+ messages)  latest in thread

Message-ID: <ed89807e-0c15-02ac-b0aa-c01cdc5a2b2e@postgrespro.ru>
Permalink:  ../ed89807e-0c15-02ac-b0aa-c01cdc5a2b2e@postgrespro.ru/
Also on:    postgresql.org/message-id/ed89807e-0c15-02ac-b0aa-c01cdc5a2b2e@postgrespro.ru

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: a.lepikhov@postgrespro.ru, etsuro.fujita@gmail.com, pryzby@telsasoft.com, zyu@yugabyte.com, amitlangote09@gmail.com, ashutosh.bapat.oss@gmail.com, tomas.vondra@enterprisedb.com, houzj.fnst@fujitsu.com
  Subject: Re: Fast COPY FROM based on batch insert
  In-Reply-To: <ed89807e-0c15-02ac-b0aa-c01cdc5a2b2e@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox