BUG #16631: postgres_fdw tries to insert into generated columns

The Post Office <noreply@postgresql.org>

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: dacherny@gmail.com
Date: 2020-09-23T18:41:29Z
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      16631
Logged by:          Daniel Cherniy
Email address:      dacherny@gmail.com
PostgreSQL version: 12.4
Operating system:   Debian (as &quot;docker pull postgres:latest&quot;)
Description:        

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)

-- maybe now?
insert into fgn.test (id, name, name_hash) values (2, 'Try to insert with
"default" value', default);
> [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)


select version();
+----------------------------------------------------------------------------------------------------------------+
|version                                                                    
                                    |
+----------------------------------------------------------------------------------------------------------------+
|PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit|
+----------------------------------------------------------------------------------------------------------------+

Commits

  1. postgres_fdw: Fix issues with generated columns in foreign tables.