Re: postgres_fdw: virtual generated columns vs. IMPORT FOREIGN SCHEMA

On Fri, Nov 6, 2026 at 6:51 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
wrote:

> Hi,
>
> While working on something else, I noticed $SUBJECT:
>
> create server loopback foreign data wrapper postgres_fdw options
> (dbname 'postgres');
> create user mapping for current_user server loopback;
> create schema import_source;
> create table import_source.import_table (a int, b int generated always
> as (a * 2) virtual);
> create schema import_dest;
> import foreign schema import_source from server loopback into import_dest;
>
> explain verbose insert into import_dest.import_table (a) values (1);
> QUERY PLAN
>
> ----------------------------------------------------------------------------
> Insert on import_dest.import_table (cost=0.00..0.01 rows=0 width=0)
> Remote SQL: INSERT INTO import_source.import_table(a, b) VALUES ($1, $2)
> Batch Size: 1
> -> Result (cost=0.00..0.01 rows=1 width=8)
> Output: 1, NULL::integer
> (5 rows)
>
> insert into import_dest.import_table (a) values (1);
> ERROR: cannot insert a non-DEFAULT value into column "b"
> DETAIL: Column "b" is a generated column.
> CONTEXT: remote SQL command: INSERT INTO
> import_source.import_table(a, b) VALUES ($1, $2)
>
> The root cause of this is that we fail to include the virtual
> generated expression for column "b" in the foreign table definition
> like this:
>
> \d+ import_dest.import_table
> Foreign table "import_dest.import_table"
> Column | Type | Collation | Nullable | Default | FDW options
> | Storage | Stats target | Description
>
> --------+---------+-----------+----------+---------+-------------------+---------+--------------+-------------
> a | integer | | | | (column_name 'a')
> | plain | |
> b | integer | | | | (column_name 'b')
> | plain | |
> Server: loopback
> FDW options: (schema_name 'import_source', table_name 'import_table')
>
> This causes the remote query to try to insert NULL into the column,
> despite not being allowed to do so, as shown in the error message.
>
> We actually had the same issue for stored generated columns, and fixed
> it by modifying IMPORT FOREIGN SCHEMA to include column (stored)
> generated expressions in the definitions of foreign tables imported
> from a foreign server (see commit aa769f80e). So I fixed this by just
> expanding that handling to cover the virtual-generated-columns case.
> Attached is a patch for that.
>

Hi Etsuro,

Thanks for the patch. I reviewed it and the fix looks good to me.

I reproduced the error with your example on current master, and with the
patch applied, INSERT and UPDATE through the imported table work. The
postgres_fdw regression tests pass, and I also tried a stored NOT NULL
column and a virtual column whose expression calls a function (upper());
both import and work fine.

*Note*: the patch doesn't apply cleanly to REL_18_STABLE (Virtual generated
columns only exist in v18 and later, so tested only on that, and master).
The code is indented one level deeper there, but a re-indented version
passes the postgres_fdw tests.

*One suggestion on the tests*: the existing INSERT/UPDATE/COPY tests use a
hand-written foreign table (grem1), whose column "c" was already declared
virtual, so they pass with or without the patch. The import tests only
check the \d output and never run DML on an imported table. It would be
good to also insert into one of the imported tables, e.g. import_dest1."x 6",
and select from it and from the remote table, so that the failing INSERT
from your report is covered directly.

Thanks

>
> I don’t want to rush this, so I will push/backpatch the patch after
> the freeze, if no objections.
>
> Best regards,
> Etsuro Fujita
>

--
*Jeevan Chalke*
*Senior Principal Engineer, Engineering Manager*
*Product Development*

enterprisedb.com <https://www.enterprisedb.com>