BUG #16631: postgres_fdw tries to insert into generated columns

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: dacherny(at)gmail(dot)com
Subject: BUG #16631: postgres_fdw tries to insert into generated columns
Date: 2020-09-23 18:41:29
Message-ID: 16631-e929fe9db0ffc7cf@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16631
Logged by: Daniel Cherniy
Email address: dacherny(at)gmail(dot)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|
+----------------------------------------------------------------------------------------------------------------+

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2020-09-23 19:22:52 Re: BUG #16624: Query Optimizer - Performance bug related to predicate simplification
Previous Message Pavel Stehule 2020-09-23 16:28:08 Re: BUG #16630: TupleDesc reference leak: TupleDesc 0x7fad89c9c928 (16386,-1) still referenced