Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1hIYKp-0007Ml-HG
	for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Apr 2019 12:46:11 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1hIYKo-0007Of-0B
	for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Apr 2019 12:46:10 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <fujita.etsuro@lab.ntt.co.jp>)
	id 1hIYKn-0007OU-K9; Mon, 22 Apr 2019 12:46:09 +0000
Received: from tama50.ecl.ntt.co.jp ([129.60.39.147])
	by makus.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <fujita.etsuro@lab.ntt.co.jp>)
	id 1hIYKk-00063q-PP; Mon, 22 Apr 2019 12:46:08 +0000
Received: from vc1.ecl.ntt.co.jp (vc1.ecl.ntt.co.jp [129.60.86.153])
	by tama50.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id x3MCjjaK021075;
	Mon, 22 Apr 2019 21:45:45 +0900
Received: from vc1.ecl.ntt.co.jp (localhost [127.0.0.1])
	by vc1.ecl.ntt.co.jp (Postfix) with ESMTP id E49EFEA8203;
	Mon, 22 Apr 2019 21:45:45 +0900 (JST)
Received: from jcms-pop21.ecl.ntt.co.jp (jcms-pop21.ecl.ntt.co.jp
 [129.60.87.134])
	by vc1.ecl.ntt.co.jp (Postfix) with ESMTP id D055BEA81D2;
	Mon, 22 Apr 2019 21:45:45 +0900 (JST)
Received: from [IPv6:::1] (unknown [129.60.241.75])
	by jcms-pop21.ecl.ntt.co.jp (Postfix) with ESMTPSA id 89DCD40075C;
	Mon, 22 Apr 2019 21:45:45 +0900 (JST)
Message-ID: <5CBDB770.2060602@lab.ntt.co.jp>
Date: Mon, 22 Apr 2019 21:45:36 +0900
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Subject: Re: pgsql: Allow insert and update tuple routing and COPY for foreign
 table
References: <E1f4aif-0004qo-A6@gemulon.postgresql.org>
 <ed0b7471dce91169d871347cf003d8094ab96895.camel@cybertec.at>
In-Reply-To: <ed0b7471dce91169d871347cf003d8094ab96895.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-CC-Mail-RelayStamp: 1
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Robert Haas <rhaas@postgresql.org>, pgsql-committers@lists.postgresql.org,
        pgsql-hackers@lists.postgresql.org
X-TM-AS-MML: disable
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

(2019/04/20 20:53), Laurenz Albe wrote:
> On Fri, 2018-04-06 at 23:24 +0000, Robert Haas wrote:
>> Allow insert and update tuple routing and COPY for foreign tables.
>>
>> Also enable this for postgres_fdw.
>>
>> Etsuro Fujita, based on an earlier patch by Amit Langote. The larger
>> patch series of which this is a part has been reviewed by Amit
>> Langote, David Fetter, Maksim Milyutin, Álvaro Herrera, Stephen Frost,
>> and me.  Minor documentation changes to the final version by me.
>>
>> Discussion: http://postgr.es/m/29906a26-da12-8c86-4fb9-d8f88442f2b9@lab.ntt.co.jp
>
> This commit makes life hard for foreign data wrappers that support
> data modifications.
>
> If a FDW implements ExecForeignInsert, this commit automatically assumes
> that it also supports COPY FROM.  It will call ExecForeignInsert without
> calling PlanForeignModify and BeginForeignModify, and a FDW that does not
> expect that will probably fail.

This is not 100% correct; the FDW documentation says:

     <para>
      Tuples inserted into a partitioned table by 
<command>INSERT</command> or
      <command>COPY FROM</command> are routed to partitions.  If an FDW
      supports routable foreign-table partitions, it should also provide the
      following callback functions.  These functions are also called when
      <command>COPY FROM</command> is executed on a foreign table.
     </para>

> maybe there are FDWs that support INSERT but don't want to support COPY
> for some reason.

I agree on that point.

> I propose that PostgreSQL only allows COPY FROM on a foreign table if the FDW
> implements BeginForeignInsert.  The attached patch implements that.

I don't think that is a good idea, because there might be some FDWs that 
want to support COPY FROM on foreign tables without providing 
BeginForeignInsert.  (As for INSERT into foreign tables, we actually 
allow FDWs to support it without providing PlanForeignModify, 
BeginForeignModify, or EndForeignModify.)

It's permissible to throw an error in BeginForeignInsert, so what I was 
thinking for FDWs that don't want to support COPY FROM and 
INSERT/UPDATE/COPY FROM tuple routing was to provide BeginForeignInsert 
implementing something like this:

static void
fooBeginForeignInsert(ModifyTableState *mtstate,
                       ResultRelInfo *resultRelInfo)
{
     Relation    rel = resultRelInfo->ri_RelationDesc;

     if (mtstate->ps.plan == NULL)
         ereport(ERROR,
                 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                  errmsg("cannot copy to foreign table \"%s\"",
                         RelationGetRelationName(rel))));
     else
         ereport(ERROR,
                 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                  errmsg("cannot route tuples into foreign table \"%s\"",
                         RelationGetRelationName(rel))));
}

Best regards,
Etsuro Fujita