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 "docker pull postgres:latest")
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|
+----------------------------------------------------------------------------------------------------------------+
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 |