Re: BUG #16631: postgres_fdw tries to insert into generated columns - Mailing list pgsql-bugs

I CCed Peter E.

On Thu, Sep 24, 2020 at 4:10 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
> Postgres FDW imports generated (stored) columns from foreign table like
> usual columns and tries to insert values into them instead of skipping.
>
> Steps to reproduce:
>
> create extension postgres_fdw;
> create server host_as_foreign foreign data wrapper postgres_fdw options
> (dbname 'postgres');
> create user mapping for current_user server host_as_foreign;
>
> create schema src;
> create table src.test (
>   id int primary key,
>   name text not null,
>   name_hash char(32) generated always as (md5(name)) stored
> );
>
> insert into src.test (id, name) values (1, 'Hello') returning *;
> +--+-----+--------------------------------+
> |id|name |name_hash                       |
> +--+-----+--------------------------------+
> |1 |Hello|8b1a9953c4611296a827abf8c47804d7|
> +--+-----+--------------------------------+
>
> -- lets import schema
> create schema fgn;
> import foreign schema src limit to (test) from server host_as_foreign into
> fgn;
>
> -- and check what we've got
> select * from fgn.test;
> +--+-----+--------------------------------+
> |id|name |name_hash                       |
> +--+-----+--------------------------------+
> |1 |Hello|8b1a9953c4611296a827abf8c47804d7|
> +--+-----+--------------------------------+
>
> -- try to insert only columns what we suppose to
> insert into fgn.test (id, name) values (2, 'Try to insert without generated
> column');
> > [42601] ERROR: cannot insert into column "name_hash"
> > Detail: Column "name_hash" is a generated column.
> > Where: remote SQL command: INSERT INTO src.test(id, name, name_hash)
> VALUES ($1, $2, $3)

Reproduced.  Thanks for the report!

I studied the handling of generated columns in foreign tables, but I’m
not sure it is very well designed.  This is the documentation note
about it in create_foreign_table.sgml:

    Similar considerations apply to generated columns.  Stored generated
    columns are computed on insert or update on the local
    <productname>PostgreSQL</productname> server and handed to the
    foreign-data wrapper for writing out to the foreign data store, but it is
    not enforced that a query of the foreign table returns values for stored
    generated columns that are consistent with the generation expression.
    Again, this might result in incorrect query results.

I’m not sure why this is similar to the constraint case.  But rather
than computing the generated columns locally, I’m wondering that we
should compute them remotely, assuming that the corresponding
generated columns are defined on the remote sides.  (It would be the
user’s responsibility to ensure that.)  This seems to me similar to
the constraint case, and if we did so, I think we could fix the
reported issue by extending postgresImportForeignSchema to support
generated columns.  Maybe I’m missing something, though.

(Column defaults are also computed locally, but as discussed in [1],
that wouldn’t be ideal, and it would be good if we fixed to compute
them remotely.)

While looking into this, I noticed this:

create schema fgn2;
import foreign schema src limit to (test) from server host_as_foreign
into fgn2 options (import_default 'true');
ERROR:  cannot use column reference in DEFAULT expression
LINE 4: ... 'name_hash') COLLATE pg_catalog."default" DEFAULT md5(name)
                                                                  ^
QUERY:  CREATE FOREIGN TABLE test (
  id integer OPTIONS (column_name 'id') NOT NULL,
  name text OPTIONS (column_name 'name') COLLATE pg_catalog."default" NOT NULL,
  name_hash character(32) OPTIONS (column_name 'name_hash') COLLATE
pg_catalog."default" DEFAULT md5(name)
) SERVER host_as_foreign
OPTIONS (schema_name 'src', table_name 'test');
CONTEXT:  importing foreign table "test"

When enabling the import_default option, postgresImportForeignSchema
incorrectly imports the generation expression for generated column
name_hash defined on the remote table as a default expression for it.
Attached is a patch for fixing this issue.

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/26654.1380145647%40sss.pgh.pa.us