pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: dacherny@gmail.com
Subject: BUG #16631: postgres_fdw tries to insert into generated columns
Date: Wed, 23 Sep 2020 18:41:29 +0000
Message-ID: <16631-e929fe9db0ffc7cf@postgresql.org> (raw )
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 "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|
+----------------------------------------------------------------------------------------------------------------+
view thread (10+ messages) latest in thread
Message-ID: <16631-e929fe9db0ffc7cf@postgresql.org>
Permalink: ../16631-e929fe9db0ffc7cf@postgresql.org/
Also on: postgresql.org/message-id/16631-e929fe9db0ffc7cf@postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, dacherny@gmail.com
Subject: Re: BUG #16631: postgres_fdw tries to insert into generated columns
In-Reply-To: <16631-e929fe9db0ffc7cf@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox