Subject: Re: BUG #16631: postgres_fdw tries to insert into generated columns
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>, dacherny@gmail.com,
 PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
References: <16631-e929fe9db0ffc7cf@postgresql.org>
 <CAPmGK15BvXLPJQ7K9Pvq7mkeP3tSuuqU=pPJA1dUrsayrhzoiw@mail.gmail.com>
 <CAPmGK173z88dS-us68jm-3ynscYdSK=mnUpiO3pOTBP_5S=AFQ@mail.gmail.com>
 <8a61d9c4-b614-e2dc-6bcc-bfc17db4d2f9@enterprisedb.com>
 <1838679.1625673259@sss.pgh.pa.us>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Message-ID: <b1b88804-7612-42d7-dc85-b45579215d48@enterprisedb.com>
Date: Mon, 12 Jul 2021 22:36:53 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:78.0)
 Gecko/20100101 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <1838679.1625673259@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/b1b88804-7612-42d7-dc85-b45579215d48%40enterprisedb.com>
Precedence: bulk

On 07.07.21 17:54, Tom Lane wrote:
> Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
>> On 07.07.21 09:20, Etsuro Fujita wrote:
>>> * Modified nodeModifyTable.c and copyfrom.c so that they don't compute
>>> generated columns for FDWs anymore.
> 
>> I don't agree with that change.  What is the point of declaring a
>> generated column on a foreign table if you ignore it?  Then you might as
>> well prohibit declaring such columns in the first place.
> 
> Maybe what we need here is some documentation clarifying exactly what
> the point of a generated column on a foreign table actually *is*.
> When would you do it, and what relationship if any has it got to the
> generation status of the underlying remote column?

The behavior is explained here: 
https://www.postgresql.org/docs/current/sql-createforeigntable.html

If you have a foreign table with a column "a" and a column "b" generated 
as (a * 2), and say the underlying storage is a flat file, then the 
generated value is computed by the server and written to the file.  The 
file behaves essentially analogous to the regular table heap file.

The way I understand this bug report is, what if the underlying storage 
is another SQL database with its own generated columns.  Then how do we 
manage it so that IMPORT FOREIGN SCHEMA can mark the foreign table 
column as "column exists to be read, but don't write to it".  The 
proposal was to repurpose the generated column syntax for that, but that 
breaks the flat-file use case above.

The follow-up proposal was to let the FDW implementation decide.  I 
don't think that is always the right thing either.  What if I want the 
foreign-table node to do the computations, because it has access to 
special parameters or something.

There might be multiple possibilities of what is appropriate here.  But 
that's not something we can just change around as part of a bug fix.