Re: BUG #16631: postgres_fdw tries to insert into generated columns

On 07.07.21 17:54, Tom Lane wrote:
> Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)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.