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

On Tue, Jul 13, 2021 at 5:36 AM Peter Eisentraut
<peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
> 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.

I think this may also depend on the FDW implementation.

> 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.

The follow-up proposal was mainly for consistency with the handling of
constraints on foreign tables; they are not enforced on the local PG
server at all, as noted in the above documentation.

> 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.

I agree on this part. The changes I made to the core side would go
beyond a fix for the reported issue. I'll remove it in the next
version of the patch.

Best regards,
Etsuro Fujita