pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16631: postgres_fdw tries to insert into generated columns
10+ messages / 4 participants
[nested] [flat]
* BUG #16631: postgres_fdw tries to insert into generated columns
@ 2020-09-23 18:41 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: PG Bug reporting form @ 2020-09-23 18:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: dacherny@gmail.com
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|
+----------------------------------------------------------------------------------------------------------------+
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-07-03 10:44 Etsuro Fujita <etsuro.fujita@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 10+ messages in thread
From: Etsuro Fujita @ 2021-07-03 10:44 UTC (permalink / raw)
To: dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; +Cc: peter.eisentraut@enterprisedb.com
I CCed Peter E.
On Thu, Sep 24, 2020 at 4:10 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
> 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)
Reproduced. Thanks for the report!
I studied the handling of generated columns in foreign tables, but I’m
not sure it is very well designed. This is the documentation note
about it in create_foreign_table.sgml:
Similar considerations apply to generated columns. Stored generated
columns are computed on insert or update on the local
<productname>PostgreSQL</productname> server and handed to the
foreign-data wrapper for writing out to the foreign data store, but it is
not enforced that a query of the foreign table returns values for stored
generated columns that are consistent with the generation expression.
Again, this might result in incorrect query results.
I’m not sure why this is similar to the constraint case. But rather
than computing the generated columns locally, I’m wondering that we
should compute them remotely, assuming that the corresponding
generated columns are defined on the remote sides. (It would be the
user’s responsibility to ensure that.) This seems to me similar to
the constraint case, and if we did so, I think we could fix the
reported issue by extending postgresImportForeignSchema to support
generated columns. Maybe I’m missing something, though.
(Column defaults are also computed locally, but as discussed in [1],
that wouldn’t be ideal, and it would be good if we fixed to compute
them remotely.)
While looking into this, I noticed this:
create schema fgn2;
import foreign schema src limit to (test) from server host_as_foreign
into fgn2 options (import_default 'true');
ERROR: cannot use column reference in DEFAULT expression
LINE 4: ... 'name_hash') COLLATE pg_catalog."default" DEFAULT md5(name)
^
QUERY: CREATE FOREIGN TABLE test (
id integer OPTIONS (column_name 'id') NOT NULL,
name text OPTIONS (column_name 'name') COLLATE pg_catalog."default" NOT NULL,
name_hash character(32) OPTIONS (column_name 'name_hash') COLLATE
pg_catalog."default" DEFAULT md5(name)
) SERVER host_as_foreign
OPTIONS (schema_name 'src', table_name 'test');
CONTEXT: importing foreign table "test"
When enabling the import_default option, postgresImportForeignSchema
incorrectly imports the generation expression for generated column
name_hash defined on the remote table as a default expression for it.
Attached is a patch for fixing this issue.
Best regards,
Etsuro Fujita
[1] https://www.postgresql.org/message-id/26654.1380145647%40sss.pgh.pa.us
Attachments:
[application/octet-stream] fix-postgresImportForeignSchema.patch (8.9K, ../../CAPmGK15BvXLPJQ7K9Pvq7mkeP3tSuuqU=pPJA1dUrsayrhzoiw@mail.gmail.com/2-fix-postgresImportForeignSchema.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 31b5de91ad..cfcb300337 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8544,6 +8544,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
+CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
FOR VALUES FROM (1) TO (100);
@@ -8561,7 +8562,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest1.*
Foreign table "import_dest1.t1"
@@ -8611,6 +8613,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest1.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
-- Options
CREATE SCHEMA import_dest2;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
@@ -8625,7 +8635,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest2.*
Foreign table "import_dest2.t1"
@@ -8675,6 +8686,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest2.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
OPTIONS (import_collate 'false', import_not_null 'false');
@@ -8688,7 +8707,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest3.*
Foreign table "import_dest3.t1"
@@ -8738,6 +8758,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest3.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
-- Check LIMIT TO and EXCEPT
CREATE SCHEMA import_dest4;
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part)
@@ -8762,7 +8790,8 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
-- Assorted error cases
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index fafbab6b02..35d738e9b6 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5275,13 +5275,24 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
* include a schema name for types/functions in other schemas, which
* is what we want.
*/
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull, ");
+
+ /* Generated columns are supported since Postgres 12 */
+ if (PQserverVersion(conn) >= 120000)
+ appendStringInfoString(&buf,
+ " attgenerated, "
+ " pg_get_expr(adbin, adrelid), ");
+ else
+ appendStringInfoString(&buf,
+ " NULL, "
+ " pg_get_expr(adbin, adrelid), ");
+
if (import_collate)
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
" collname, "
" collnsp.nspname "
"FROM pg_class c "
@@ -5298,11 +5309,6 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
" collnsp.oid = collnamespace ");
else
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
" NULL, NULL "
"FROM pg_class c "
" JOIN pg_namespace n ON "
@@ -5379,6 +5385,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
char *attname;
char *typename;
char *attnotnull;
+ char *attgenerated;
char *attdefault;
char *collname;
char *collnamespace;
@@ -5390,12 +5397,14 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
attname = PQgetvalue(res, i, 1);
typename = PQgetvalue(res, i, 2);
attnotnull = PQgetvalue(res, i, 3);
- attdefault = PQgetisnull(res, i, 4) ? (char *) NULL :
+ attgenerated = PQgetisnull(res, i, 4) ? (char *) NULL :
PQgetvalue(res, i, 4);
- collname = PQgetisnull(res, i, 5) ? (char *) NULL :
+ attdefault = PQgetisnull(res, i, 5) ? (char *) NULL :
PQgetvalue(res, i, 5);
- collnamespace = PQgetisnull(res, i, 6) ? (char *) NULL :
+ collname = PQgetisnull(res, i, 6) ? (char *) NULL :
PQgetvalue(res, i, 6);
+ collnamespace = PQgetisnull(res, i, 7) ? (char *) NULL :
+ PQgetvalue(res, i, 7);
if (first_item)
first_item = false;
@@ -5423,7 +5432,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
quote_identifier(collname));
/* Add DEFAULT if needed */
- if (import_default && attdefault != NULL)
+ if (import_default && attdefault != NULL &&
+ (!attgenerated || !attgenerated[0]))
appendStringInfo(&buf, " DEFAULT %s", attdefault);
/* Add NOT NULL if needed */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 286dd99573..ca0d703191 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2499,6 +2499,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
+CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
FOR VALUES FROM (1) TO (100);
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-07-07 07:20 Etsuro Fujita <etsuro.fujita@gmail.com>
parent: Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Etsuro Fujita @ 2021-07-07 07:20 UTC (permalink / raw)
To: dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; +Cc: peter.eisentraut@enterprisedb.com
On Sat, Jul 3, 2021 at 7:44 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> But rather
> than computing the generated columns locally, I’m wondering that we
> should compute them remotely, assuming that the corresponding
> generated columns are defined on the remote sides. (It would be the
> user’s responsibility to ensure that.) This seems to me similar to
> the constraint case, and if we did so, I think we could fix the
> reported issue by extending postgresImportForeignSchema to support
> generated columns.
I updated the patch as such:
* Modified nodeModifyTable.c and copyfrom.c so that they don't compute
generated columns for FDWs anymore.
* Modified deparse functions such as deparseInsertSql() so that the
query is deparsed to set target generated columns (if any) to DEFAULT.
* Added an option import_generated to postgres_fdw to support
importing generated columns.
* Added a bit more test cases to postgres_fdw.sql.
* Updated docs.
Currently, foreign tables are not allowed to be updated directly when
they have stored generated columns, but I think they could be. But I
left that as future work as that would be an improvement rather than a
fix.
Attached is an updated version of the patch.
Best regards,
Etsuro Fujita
Attachments:
[application/octet-stream] reconsider-handling-generated-columns-in-foreign-tables.patch (25.5K, ../../CAPmGK173z88dS-us68jm-3ynscYdSK=mnUpiO3pOTBP_5S=AFQ@mail.gmail.com/2-reconsider-handling-generated-columns-in-foreign-tables.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 31919fda8c..8d59247d30 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -1717,6 +1717,7 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs, int *values_end_len)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
@@ -1746,12 +1747,20 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
first = true;
foreach(lc, targetAttrs)
{
+ int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
+
if (!first)
appendStringInfoString(buf, ", ");
first = false;
- appendStringInfo(buf, "$%d", pindex);
- pindex++;
+ if (attr->attgenerated)
+ appendStringInfoString(buf, "DEFAULT");
+ else
+ {
+ appendStringInfo(buf, "$%d", pindex);
+ pindex++;
+ }
}
appendStringInfoChar(buf, ')');
@@ -1775,14 +1784,16 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
* right number of parameters.
*/
void
-rebuildInsertSql(StringInfo buf, char *orig_query,
- int values_end_len, int num_cols,
+rebuildInsertSql(StringInfo buf, Relation rel,
+ char *orig_query, List *target_attrs,
+ int values_end_len, int num_params,
int num_rows)
{
- int i,
- j;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int i;
int pindex;
bool first;
+ ListCell *lc;
/* Make sure the values_end_len is sensible */
Assert((values_end_len > 0) && (values_end_len <= strlen(orig_query)));
@@ -1794,20 +1805,28 @@ rebuildInsertSql(StringInfo buf, char *orig_query,
* Add records to VALUES clause (we already have parameters for the first
* row, so start at the right offset).
*/
- pindex = num_cols + 1;
+ pindex = num_params + 1;
for (i = 0; i < num_rows; i++)
{
appendStringInfoString(buf, ", (");
first = true;
- for (j = 0; j < num_cols; j++)
+ foreach(lc, target_attrs)
{
+ int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
+
if (!first)
appendStringInfoString(buf, ", ");
first = false;
- appendStringInfo(buf, "$%d", pindex);
- pindex++;
+ if (attr->attgenerated)
+ appendStringInfoString(buf, "DEFAULT");
+ else
+ {
+ appendStringInfo(buf, "$%d", pindex);
+ pindex++;
+ }
}
appendStringInfoChar(buf, ')');
@@ -1831,6 +1850,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
@@ -1844,14 +1864,20 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
foreach(lc, targetAttrs)
{
int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
if (!first)
appendStringInfoString(buf, ", ");
first = false;
deparseColumnRef(buf, rtindex, attnum, rte, false);
- appendStringInfo(buf, " = $%d", pindex);
- pindex++;
+ if (attr->attgenerated)
+ appendStringInfoString(buf, " = DEFAULT");
+ else
+ {
+ appendStringInfo(buf, " = $%d", pindex);
+ pindex++;
+ }
}
appendStringInfoString(buf, " WHERE ctid = $1");
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index b510322c4e..f8d5f4edd2 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6458,13 +6458,37 @@ select * from rem1;
-- ===================================================================
-- test generated columns
-- ===================================================================
-create table gloc1 (a int, b int);
+create table gloc1 (
+ a int,
+ b int generated always as (a * 2) stored);
alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
b int generated always as (a * 2) stored)
server loopback options(table_name 'gloc1');
+explain (verbose, costs off)
+insert into grem1 (a) values (1), (2);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Insert on public.grem1
+ Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
+ Batch Size: 1
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1, NULL::integer
+(5 rows)
+
insert into grem1 (a) values (1), (2);
+explain (verbose, costs off)
+update grem1 set a = 22 where a = 2;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Update on public.grem1
+ Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT WHERE ctid = $1
+ -> Foreign Scan on public.grem1
+ Output: 22, ctid, grem1.*
+ Remote SQL: SELECT a, b, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
+(5 rows)
+
update grem1 set a = 22 where a = 2;
select * from gloc1;
a | b
@@ -6480,6 +6504,54 @@ select * from grem1;
22 | 44
(2 rows)
+delete from grem1;
+-- test copy from
+copy grem1 from stdin;
+select * from gloc1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+select * from grem1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+delete from grem1;
+-- test batch insert
+alter server loopback options (add batch_size '10');
+explain (verbose, costs off)
+insert into grem1 (a) values (1), (2);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Insert on public.grem1
+ Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
+ Batch Size: 10
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1, NULL::integer
+(5 rows)
+
+insert into grem1 (a) values (1), (2);
+select * from gloc1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+select * from grem1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+delete from grem1;
+alter server loopback options (drop batch_size);
-- ===================================================================
-- test local triggers
-- ===================================================================
@@ -8546,6 +8618,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
+CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
FOR VALUES FROM (1) TO (100);
@@ -8563,7 +8636,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest1.*
Foreign table "import_dest1.t1"
@@ -8613,6 +8687,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest1.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+-------------------------------------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
-- Options
CREATE SCHEMA import_dest2;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
@@ -8627,7 +8709,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest2.*
Foreign table "import_dest2.t1"
@@ -8677,9 +8760,17 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest2.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+-------------------------------------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
- OPTIONS (import_collate 'false', import_not_null 'false');
+ OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
\det+ import_dest3.*
List of foreign tables
Schema | Table | Server | FDW options | Description
@@ -8690,7 +8781,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest3.*
Foreign table "import_dest3.t1"
@@ -8740,6 +8832,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest3.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
-- Check LIMIT TO and EXCEPT
CREATE SCHEMA import_dest4;
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part)
@@ -8764,7 +8864,8 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
-- Assorted error cases
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 5cf10402a2..39f84ae8c4 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -4009,6 +4009,9 @@ create_foreign_modify(EState *estate,
Assert(!attr->attisdropped);
+ /* Ignore generated columns; they are set to DEFAULT */
+ if (attr->attgenerated)
+ continue;
getTypeOutputInfo(attr->atttypid, &typefnoid, &isvarlena);
fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
fmstate->p_nums++;
@@ -4072,8 +4075,10 @@ execute_foreign_modify(EState *estate,
/* Build INSERT string with numSlots records in its VALUES clause. */
initStringInfo(&sql);
- rebuildInsertSql(&sql, fmstate->orig_query, fmstate->values_end,
- fmstate->p_nums, *numSlots - 1);
+ rebuildInsertSql(&sql, fmstate->rel,
+ fmstate->orig_query, fmstate->target_attrs,
+ fmstate->values_end, fmstate->p_nums,
+ *numSlots - 1);
pfree(fmstate->query);
fmstate->query = sql.data;
fmstate->num_slots = *numSlots;
@@ -4241,6 +4246,7 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
/* get following parameters from slots */
if (slots != NULL && fmstate->target_attrs != NIL)
{
+ TupleDesc tupdesc = RelationGetDescr(fmstate->rel);
int nestlevel;
ListCell *lc;
@@ -4252,9 +4258,13 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
foreach(lc, fmstate->target_attrs)
{
int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
Datum value;
bool isnull;
+ /* Ignore generated columns; they are set to DEFAULT */
+ if (attr->attgenerated)
+ continue;
value = slot_getattr(slots[i], attnum, &isnull);
if (isnull)
p_values[pindex] = NULL;
@@ -5185,6 +5195,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
List *commands = NIL;
bool import_collate = true;
bool import_default = false;
+ bool import_generated = true;
bool import_not_null = true;
ForeignServer *server;
UserMapping *mapping;
@@ -5204,6 +5215,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
import_collate = defGetBoolean(def);
else if (strcmp(def->defname, "import_default") == 0)
import_default = defGetBoolean(def);
+ else if (strcmp(def->defname, "import_generated") == 0)
+ import_generated = defGetBoolean(def);
else if (strcmp(def->defname, "import_not_null") == 0)
import_not_null = defGetBoolean(def);
else
@@ -5267,13 +5280,24 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
* include a schema name for types/functions in other schemas, which
* is what we want.
*/
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull, ");
+
+ /* Generated columns are supported since Postgres 12 */
+ if (PQserverVersion(conn) >= 120000)
+ appendStringInfoString(&buf,
+ " attgenerated, "
+ " pg_get_expr(adbin, adrelid), ");
+ else
+ appendStringInfoString(&buf,
+ " NULL, "
+ " pg_get_expr(adbin, adrelid), ");
+
if (import_collate)
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
" collname, "
" collnsp.nspname "
"FROM pg_class c "
@@ -5290,11 +5314,6 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
" collnsp.oid = collnamespace ");
else
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
" NULL, NULL "
"FROM pg_class c "
" JOIN pg_namespace n ON "
@@ -5371,6 +5390,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
char *attname;
char *typename;
char *attnotnull;
+ char *attgenerated;
char *attdefault;
char *collname;
char *collnamespace;
@@ -5382,12 +5402,14 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
attname = PQgetvalue(res, i, 1);
typename = PQgetvalue(res, i, 2);
attnotnull = PQgetvalue(res, i, 3);
- attdefault = PQgetisnull(res, i, 4) ? (char *) NULL :
+ attgenerated = PQgetisnull(res, i, 4) ? (char *) NULL :
PQgetvalue(res, i, 4);
- collname = PQgetisnull(res, i, 5) ? (char *) NULL :
+ attdefault = PQgetisnull(res, i, 5) ? (char *) NULL :
PQgetvalue(res, i, 5);
- collnamespace = PQgetisnull(res, i, 6) ? (char *) NULL :
+ collname = PQgetisnull(res, i, 6) ? (char *) NULL :
PQgetvalue(res, i, 6);
+ collnamespace = PQgetisnull(res, i, 7) ? (char *) NULL :
+ PQgetvalue(res, i, 7);
if (first_item)
first_item = false;
@@ -5415,9 +5437,20 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
quote_identifier(collname));
/* Add DEFAULT if needed */
- if (import_default && attdefault != NULL)
+ if (import_default && attdefault != NULL &&
+ (!attgenerated || !attgenerated[0]))
appendStringInfo(&buf, " DEFAULT %s", attdefault);
+ /* Add GENERATED if needed */
+ if (import_generated && attgenerated != NULL &&
+ attgenerated[0] == ATTRIBUTE_GENERATED_STORED)
+ {
+ Assert(attdefault != NULL);
+ appendStringInfo(&buf,
+ " GENERATED ALWAYS AS (%s) STORED",
+ attdefault);
+ }
+
/* Add NOT NULL if needed */
if (import_not_null && attnotnull[0] == 't')
appendStringInfoString(&buf, " NOT NULL");
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 9591c0f6c2..ca83306af9 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -176,8 +176,9 @@ extern void deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
List *targetAttrs, bool doNothing,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs, int *values_end_len);
-extern void rebuildInsertSql(StringInfo buf, char *orig_query,
- int values_end_len, int num_cols,
+extern void rebuildInsertSql(StringInfo buf, Relation rel,
+ char *orig_query, List *target_attrs,
+ int values_end_len, int num_params,
int num_rows);
extern void deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
Index rtindex, Relation rel,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 911f171d81..1251211083 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1482,16 +1482,42 @@ select * from rem1;
-- ===================================================================
-- test generated columns
-- ===================================================================
-create table gloc1 (a int, b int);
+create table gloc1 (
+ a int,
+ b int generated always as (a * 2) stored);
alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
b int generated always as (a * 2) stored)
server loopback options(table_name 'gloc1');
+explain (verbose, costs off)
insert into grem1 (a) values (1), (2);
+insert into grem1 (a) values (1), (2);
+explain (verbose, costs off)
update grem1 set a = 22 where a = 2;
+update grem1 set a = 22 where a = 2;
+select * from gloc1;
+select * from grem1;
+delete from grem1;
+
+-- test copy from
+copy grem1 from stdin;
+1
+2
+\.
+select * from gloc1;
+select * from grem1;
+delete from grem1;
+
+-- test batch insert
+alter server loopback options (add batch_size '10');
+explain (verbose, costs off)
+insert into grem1 (a) values (1), (2);
+insert into grem1 (a) values (1), (2);
select * from gloc1;
select * from grem1;
+delete from grem1;
+alter server loopback options (drop batch_size);
-- ===================================================================
-- test local triggers
@@ -2501,6 +2527,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
+CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
FOR VALUES FROM (1) TO (100);
@@ -2520,7 +2547,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
\d import_dest2.*
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
- OPTIONS (import_collate 'false', import_not_null 'false');
+ OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
\det+ import_dest3.*
\d import_dest3.*
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index c9fce77599..0075bc3dbb 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -555,6 +555,18 @@ OPTIONS (ADD password_required 'false');
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>import_generated</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ This option controls whether column <literal>GENERATED</literal> expressions
+ are included in the definitions of foreign tables imported
+ from a foreign server. The default is <literal>true</literal>.
+ The <command>IMPORT</command> will fail altogether if an imported generated
+ expression uses a function or operator that does not exist locally.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>import_not_null</literal> (<type>boolean</type>)</term>
<listitem>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index f9477efe58..99d4f18d25 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -277,9 +277,7 @@ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ]
<para>
The keyword <literal>STORED</literal> is required to signify that the
- column will be computed on write. (The computed value will be presented
- to the foreign-data wrapper for storage and must be returned on
- reading.)
+ column will be computed on write.
</para>
<para>
@@ -355,12 +353,15 @@ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ]
<para>
Similar considerations apply to generated columns. Stored generated
- columns are computed on insert or update on the local
- <productname>PostgreSQL</productname> server and handed to the
- foreign-data wrapper for writing out to the foreign data store, but it is
+ columns are not computed on insert or update on the local
+ <productname>PostgreSQL</productname> server to present them to the
+ foreign-data wrapper for storage. Instead, they should represent stored
+ generated columns that are being computed on the remote server. It is
not enforced that a query of the foreign table returns values for stored
generated columns that are consistent with the generation expression.
- Again, this might result in incorrect query results.
+ Again, this might result in incorrect query results. It is the user's
+ responsibility to ensure that the generated-column definition matches
+ reality.
</para>
<para>
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 40a54ad0bd..0b7bb5bd96 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1020,8 +1020,12 @@ CopyFrom(CopyFromState cstate)
}
else
{
- /* Compute stored generated columns */
- if (resultRelInfo->ri_RelationDesc->rd_att->constr &&
+ /*
+ * If the target is a plain table, compute stored generated
+ * columns.
+ */
+ if (resultRelInfo->ri_FdwRoutine == NULL &&
+ resultRelInfo->ri_RelationDesc->rd_att->constr &&
resultRelInfo->ri_RelationDesc->rd_att->constr->has_generated_stored)
ExecComputeStoredGenerated(resultRelInfo, estate, myslot,
CMD_INSERT);
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index c24684aa6f..76aeac96b4 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -658,20 +658,6 @@ ExecInsert(ModifyTableState *mtstate,
}
else if (resultRelInfo->ri_FdwRoutine)
{
- /*
- * GENERATED expressions might reference the tableoid column, so
- * (re-)initialize tts_tableOid before evaluating them.
- */
- slot->tts_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
-
- /*
- * Compute stored generated columns
- */
- if (resultRelationDesc->rd_att->constr &&
- resultRelationDesc->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(resultRelInfo, estate, slot,
- CMD_INSERT);
-
/*
* If the FDW supports batching, and batching is requested, accumulate
* rows and insert them in batches. Otherwise use the per-row inserts.
@@ -1646,20 +1632,6 @@ ExecUpdate(ModifyTableState *mtstate,
}
else if (resultRelInfo->ri_FdwRoutine)
{
- /*
- * GENERATED expressions might reference the tableoid column, so
- * (re-)initialize tts_tableOid before evaluating them.
- */
- slot->tts_tableOid = RelationGetRelid(resultRelInfo->ri_RelationDesc);
-
- /*
- * Compute stored generated columns
- */
- if (resultRelationDesc->rd_att->constr &&
- resultRelationDesc->rd_att->constr->has_generated_stored)
- ExecComputeStoredGenerated(resultRelInfo, estate, slot,
- CMD_UPDATE);
-
/*
* update in foreign table: let the FDW do it
*/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-07-07 08:44 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Peter Eisentraut @ 2021-07-07 08:44 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On 07.07.21 09:20, Etsuro Fujita wrote:
> * Modified nodeModifyTable.c and copyfrom.c so that they don't compute
> generated columns for FDWs anymore.
I don't agree with that change. What is the point of declaring a
generated column on a foreign table if you ignore it? Then you might as
well prohibit declaring such columns in the first place.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-07-07 09:35 Etsuro Fujita <etsuro.fujita@gmail.com>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
1 sibling, 0 replies; 10+ messages in thread
From: Etsuro Fujita @ 2021-07-07 09:35 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Jul 7, 2021 at 5:45 PM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
> On 07.07.21 09:20, Etsuro Fujita wrote:
> > * Modified nodeModifyTable.c and copyfrom.c so that they don't compute
> > generated columns for FDWs anymore.
>
> I don't agree with that change. What is the point of declaring a
> generated column on a foreign table if you ignore it? Then you might as
> well prohibit declaring such columns in the first place.
I made that change as FDWs could call ExecComputeStoredGenerated() by
themselves if necessary. I think that that would be consistent with
the constraint case.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-07-07 15:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2021-07-07 15:54 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> On 07.07.21 09:20, Etsuro Fujita wrote:
>> * Modified nodeModifyTable.c and copyfrom.c so that they don't compute
>> generated columns for FDWs anymore.
> I don't agree with that change. What is the point of declaring a
> generated column on a foreign table if you ignore it? Then you might as
> well prohibit declaring such columns in the first place.
Maybe what we need here is some documentation clarifying exactly what
the point of a generated column on a foreign table actually *is*.
When would you do it, and what relationship if any has it got to the
generation status of the underlying remote column?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-07-12 20:36 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2021-07-12 20:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On 07.07.21 17:54, Tom Lane wrote:
> Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
>> On 07.07.21 09:20, Etsuro Fujita wrote:
>>> * Modified nodeModifyTable.c and copyfrom.c so that they don't compute
>>> generated columns for FDWs anymore.
>
>> I don't agree with that change. What is the point of declaring a
>> generated column on a foreign table if you ignore it? Then you might as
>> well prohibit declaring such columns in the first place.
>
> Maybe what we need here is some documentation clarifying exactly what
> the point of a generated column on a foreign table actually *is*.
> When would you do it, and what relationship if any has it got to the
> generation status of the underlying remote column?
The behavior is explained here:
https://www.postgresql.org/docs/current/sql-createforeigntable.html
If you have a foreign table with a column "a" and a column "b" generated
as (a * 2), and say the underlying storage is a flat file, then the
generated value is computed by the server and written to the file. The
file behaves essentially analogous to the regular table heap file.
The way I understand this bug report is, what if the underlying storage
is another SQL database with its own generated columns. Then how do we
manage it so that IMPORT FOREIGN SCHEMA can mark the foreign table
column as "column exists to be read, but don't write to it". The
proposal was to repurpose the generated column syntax for that, but that
breaks the flat-file use case above.
The follow-up proposal was to let the FDW implementation decide. I
don't think that is always the right thing either. What if I want the
foreign-table node to do the computations, because it has access to
special parameters or something.
There might be multiple possibilities of what is appropriate here. But
that's not something we can just change around as part of a bug fix.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-07-15 09:15 Etsuro Fujita <etsuro.fujita@gmail.com>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 1 reply; 10+ messages in thread
From: Etsuro Fujita @ 2021-07-15 09:15 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jul 13, 2021 at 5:36 AM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
> On 07.07.21 17:54, Tom Lane wrote:
> > Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> >> On 07.07.21 09:20, Etsuro Fujita wrote:
> >>> * Modified nodeModifyTable.c and copyfrom.c so that they don't compute
> >>> generated columns for FDWs anymore.
> >
> >> I don't agree with that change. What is the point of declaring a
> >> generated column on a foreign table if you ignore it? Then you might as
> >> well prohibit declaring such columns in the first place.
> >
> > Maybe what we need here is some documentation clarifying exactly what
> > the point of a generated column on a foreign table actually *is*.
> > When would you do it, and what relationship if any has it got to the
> > generation status of the underlying remote column?
>
> The behavior is explained here:
> https://www.postgresql.org/docs/current/sql-createforeigntable.html
>
> If you have a foreign table with a column "a" and a column "b" generated
> as (a * 2), and say the underlying storage is a flat file, then the
> generated value is computed by the server and written to the file. The
> file behaves essentially analogous to the regular table heap file.
>
> The way I understand this bug report is, what if the underlying storage
> is another SQL database with its own generated columns. Then how do we
> manage it so that IMPORT FOREIGN SCHEMA can mark the foreign table
> column as "column exists to be read, but don't write to it". The
> proposal was to repurpose the generated column syntax for that, but that
> breaks the flat-file use case above.
I think this may also depend on the FDW implementation.
> The follow-up proposal was to let the FDW implementation decide. I
> don't think that is always the right thing either. What if I want the
> foreign-table node to do the computations, because it has access to
> special parameters or something.
The follow-up proposal was mainly for consistency with the handling of
constraints on foreign tables; they are not enforced on the local PG
server at all, as noted in the above documentation.
> There might be multiple possibilities of what is appropriate here. But
> that's not something we can just change around as part of a bug fix.
I agree on this part. The changes I made to the core side would go
beyond a fix for the reported issue. I'll remove it in the next
version of the patch.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-08-04 08:30 Etsuro Fujita <etsuro.fujita@gmail.com>
parent: Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Etsuro Fujita @ 2021-08-04 08:30 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Thu, Jul 15, 2021 at 6:15 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Tue, Jul 13, 2021 at 5:36 AM Peter Eisentraut
> <peter.eisentraut@enterprisedb.com> wrote:
> > The follow-up proposal was to let the FDW implementation decide. I
> > don't think that is always the right thing either. What if I want the
> > foreign-table node to do the computations, because it has access to
> > special parameters or something.
>
> The follow-up proposal was mainly for consistency with the handling of
> constraints on foreign tables; they are not enforced on the local PG
> server at all, as noted in the above documentation.
>
> > There might be multiple possibilities of what is appropriate here. But
> > that's not something we can just change around as part of a bug fix.
>
> I agree on this part. The changes I made to the core side would go
> beyond a fix for the reported issue. I'll remove it in the next
> version of the patch.
I updated the patch as such. Attached is an update patch
(postgres_fdw-generated-columns-PG14-HEAD.patch), which would apply to
HEAD and v14. I'm also attaching a patch for v12 and v13
(postgres_fdw-generated-columns-PG12-PG13.patch). I'll apply these if
there are no objections.
Best regards,
Etsuro Fujita
Attachments:
[application/octet-stream] postgres_fdw-generated-columns-PG14-HEAD.patch (21.5K, ../../CAPmGK15Ze73T+U=P2-gyGXz8tbGHN371B=5c4WVXiPqy0u7VOA@mail.gmail.com/2-postgres_fdw-generated-columns-PG14-HEAD.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 2b8f00abe7..d98bd66681 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -1844,6 +1844,7 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs, int *values_end_len)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
@@ -1873,12 +1874,20 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
first = true;
foreach(lc, targetAttrs)
{
+ int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
+
if (!first)
appendStringInfoString(buf, ", ");
first = false;
- appendStringInfo(buf, "$%d", pindex);
- pindex++;
+ if (attr->attgenerated)
+ appendStringInfoString(buf, "DEFAULT");
+ else
+ {
+ appendStringInfo(buf, "$%d", pindex);
+ pindex++;
+ }
}
appendStringInfoChar(buf, ')');
@@ -1902,14 +1911,16 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
* right number of parameters.
*/
void
-rebuildInsertSql(StringInfo buf, char *orig_query,
- int values_end_len, int num_cols,
+rebuildInsertSql(StringInfo buf, Relation rel,
+ char *orig_query, List *target_attrs,
+ int values_end_len, int num_params,
int num_rows)
{
- int i,
- j;
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int i;
int pindex;
bool first;
+ ListCell *lc;
/* Make sure the values_end_len is sensible */
Assert((values_end_len > 0) && (values_end_len <= strlen(orig_query)));
@@ -1921,20 +1932,28 @@ rebuildInsertSql(StringInfo buf, char *orig_query,
* Add records to VALUES clause (we already have parameters for the first
* row, so start at the right offset).
*/
- pindex = num_cols + 1;
+ pindex = num_params + 1;
for (i = 0; i < num_rows; i++)
{
appendStringInfoString(buf, ", (");
first = true;
- for (j = 0; j < num_cols; j++)
+ foreach(lc, target_attrs)
{
+ int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
+
if (!first)
appendStringInfoString(buf, ", ");
first = false;
- appendStringInfo(buf, "$%d", pindex);
- pindex++;
+ if (attr->attgenerated)
+ appendStringInfoString(buf, "DEFAULT");
+ else
+ {
+ appendStringInfo(buf, "$%d", pindex);
+ pindex++;
+ }
}
appendStringInfoChar(buf, ')');
@@ -1958,6 +1977,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
@@ -1971,14 +1991,20 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
foreach(lc, targetAttrs)
{
int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
if (!first)
appendStringInfoString(buf, ", ");
first = false;
deparseColumnRef(buf, rtindex, attnum, rte, false);
- appendStringInfo(buf, " = $%d", pindex);
- pindex++;
+ if (attr->attgenerated)
+ appendStringInfoString(buf, " = DEFAULT");
+ else
+ {
+ appendStringInfo(buf, " = $%d", pindex);
+ pindex++;
+ }
}
appendStringInfoString(buf, " WHERE ctid = $1");
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c7b83180a8..e3ee30f1aa 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6548,13 +6548,37 @@ select * from rem1;
-- ===================================================================
-- test generated columns
-- ===================================================================
-create table gloc1 (a int, b int);
+create table gloc1 (
+ a int,
+ b int generated always as (a * 2) stored);
alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
b int generated always as (a * 2) stored)
server loopback options(table_name 'gloc1');
+explain (verbose, costs off)
+insert into grem1 (a) values (1), (2);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Insert on public.grem1
+ Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
+ Batch Size: 1
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1, NULL::integer
+(5 rows)
+
insert into grem1 (a) values (1), (2);
+explain (verbose, costs off)
+update grem1 set a = 22 where a = 2;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Update on public.grem1
+ Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT WHERE ctid = $1
+ -> Foreign Scan on public.grem1
+ Output: 22, ctid, grem1.*
+ Remote SQL: SELECT a, b, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
+(5 rows)
+
update grem1 set a = 22 where a = 2;
select * from gloc1;
a | b
@@ -6570,6 +6594,54 @@ select * from grem1;
22 | 44
(2 rows)
+delete from grem1;
+-- test copy from
+copy grem1 from stdin;
+select * from gloc1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+select * from grem1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+delete from grem1;
+-- test batch insert
+alter server loopback options (add batch_size '10');
+explain (verbose, costs off)
+insert into grem1 (a) values (1), (2);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Insert on public.grem1
+ Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
+ Batch Size: 10
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1, NULL::integer
+(5 rows)
+
+insert into grem1 (a) values (1), (2);
+select * from gloc1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+select * from grem1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+delete from grem1;
+alter server loopback options (drop batch_size);
-- ===================================================================
-- test local triggers
-- ===================================================================
@@ -8656,6 +8728,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
+CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
FOR VALUES FROM (1) TO (100);
@@ -8673,7 +8746,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest1.*
Foreign table "import_dest1.t1"
@@ -8723,6 +8797,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest1.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+-------------------------------------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
-- Options
CREATE SCHEMA import_dest2;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
@@ -8737,7 +8819,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest2.*
Foreign table "import_dest2.t1"
@@ -8787,9 +8870,17 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest2.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+-------------------------------------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
- OPTIONS (import_collate 'false', import_not_null 'false');
+ OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
\det+ import_dest3.*
List of foreign tables
Schema | Table | Server | FDW options | Description
@@ -8800,7 +8891,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest3.*
Foreign table "import_dest3.t1"
@@ -8850,6 +8942,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest3.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
-- Check LIMIT TO and EXCEPT
CREATE SCHEMA import_dest4;
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part)
@@ -8874,7 +8974,8 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
-- Assorted error cases
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 3e4a307880..9d443baf02 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -4012,6 +4012,9 @@ create_foreign_modify(EState *estate,
Assert(!attr->attisdropped);
+ /* Ignore generated columns; they are set to DEFAULT */
+ if (attr->attgenerated)
+ continue;
getTypeOutputInfo(attr->atttypid, &typefnoid, &isvarlena);
fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
fmstate->p_nums++;
@@ -4075,8 +4078,10 @@ execute_foreign_modify(EState *estate,
/* Build INSERT string with numSlots records in its VALUES clause. */
initStringInfo(&sql);
- rebuildInsertSql(&sql, fmstate->orig_query, fmstate->values_end,
- fmstate->p_nums, *numSlots - 1);
+ rebuildInsertSql(&sql, fmstate->rel,
+ fmstate->orig_query, fmstate->target_attrs,
+ fmstate->values_end, fmstate->p_nums,
+ *numSlots - 1);
pfree(fmstate->query);
fmstate->query = sql.data;
fmstate->num_slots = *numSlots;
@@ -4244,6 +4249,7 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
/* get following parameters from slots */
if (slots != NULL && fmstate->target_attrs != NIL)
{
+ TupleDesc tupdesc = RelationGetDescr(fmstate->rel);
int nestlevel;
ListCell *lc;
@@ -4255,9 +4261,13 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
foreach(lc, fmstate->target_attrs)
{
int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
Datum value;
bool isnull;
+ /* Ignore generated columns; they are set to DEFAULT */
+ if (attr->attgenerated)
+ continue;
value = slot_getattr(slots[i], attnum, &isnull);
if (isnull)
p_values[pindex] = NULL;
@@ -5188,6 +5198,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
List *commands = NIL;
bool import_collate = true;
bool import_default = false;
+ bool import_generated = true;
bool import_not_null = true;
ForeignServer *server;
UserMapping *mapping;
@@ -5207,6 +5218,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
import_collate = defGetBoolean(def);
else if (strcmp(def->defname, "import_default") == 0)
import_default = defGetBoolean(def);
+ else if (strcmp(def->defname, "import_generated") == 0)
+ import_generated = defGetBoolean(def);
else if (strcmp(def->defname, "import_not_null") == 0)
import_not_null = defGetBoolean(def);
else
@@ -5270,13 +5283,24 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
* include a schema name for types/functions in other schemas, which
* is what we want.
*/
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull, ");
+
+ /* Generated columns are supported since Postgres 12 */
+ if (PQserverVersion(conn) >= 120000)
+ appendStringInfoString(&buf,
+ " attgenerated, "
+ " pg_get_expr(adbin, adrelid), ");
+ else
+ appendStringInfoString(&buf,
+ " NULL, "
+ " pg_get_expr(adbin, adrelid), ");
+
if (import_collate)
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
" collname, "
" collnsp.nspname "
"FROM pg_class c "
@@ -5293,11 +5317,6 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
" collnsp.oid = collnamespace ");
else
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
" NULL, NULL "
"FROM pg_class c "
" JOIN pg_namespace n ON "
@@ -5374,6 +5393,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
char *attname;
char *typename;
char *attnotnull;
+ char *attgenerated;
char *attdefault;
char *collname;
char *collnamespace;
@@ -5385,12 +5405,14 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
attname = PQgetvalue(res, i, 1);
typename = PQgetvalue(res, i, 2);
attnotnull = PQgetvalue(res, i, 3);
- attdefault = PQgetisnull(res, i, 4) ? (char *) NULL :
+ attgenerated = PQgetisnull(res, i, 4) ? (char *) NULL :
PQgetvalue(res, i, 4);
- collname = PQgetisnull(res, i, 5) ? (char *) NULL :
+ attdefault = PQgetisnull(res, i, 5) ? (char *) NULL :
PQgetvalue(res, i, 5);
- collnamespace = PQgetisnull(res, i, 6) ? (char *) NULL :
+ collname = PQgetisnull(res, i, 6) ? (char *) NULL :
PQgetvalue(res, i, 6);
+ collnamespace = PQgetisnull(res, i, 7) ? (char *) NULL :
+ PQgetvalue(res, i, 7);
if (first_item)
first_item = false;
@@ -5418,9 +5440,20 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
quote_identifier(collname));
/* Add DEFAULT if needed */
- if (import_default && attdefault != NULL)
+ if (import_default && attdefault != NULL &&
+ (!attgenerated || !attgenerated[0]))
appendStringInfo(&buf, " DEFAULT %s", attdefault);
+ /* Add GENERATED if needed */
+ if (import_generated && attgenerated != NULL &&
+ attgenerated[0] == ATTRIBUTE_GENERATED_STORED)
+ {
+ Assert(attdefault != NULL);
+ appendStringInfo(&buf,
+ " GENERATED ALWAYS AS (%s) STORED",
+ attdefault);
+ }
+
/* Add NOT NULL if needed */
if (import_not_null && attnotnull[0] == 't')
appendStringInfoString(&buf, " NOT NULL");
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 9591c0f6c2..ca83306af9 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -176,8 +176,9 @@ extern void deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
List *targetAttrs, bool doNothing,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs, int *values_end_len);
-extern void rebuildInsertSql(StringInfo buf, char *orig_query,
- int values_end_len, int num_cols,
+extern void rebuildInsertSql(StringInfo buf, Relation rel,
+ char *orig_query, List *target_attrs,
+ int values_end_len, int num_params,
int num_rows);
extern void deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
Index rtindex, Relation rel,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 4c653f2473..30b5175da5 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1511,16 +1511,42 @@ select * from rem1;
-- ===================================================================
-- test generated columns
-- ===================================================================
-create table gloc1 (a int, b int);
+create table gloc1 (
+ a int,
+ b int generated always as (a * 2) stored);
alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
b int generated always as (a * 2) stored)
server loopback options(table_name 'gloc1');
+explain (verbose, costs off)
insert into grem1 (a) values (1), (2);
+insert into grem1 (a) values (1), (2);
+explain (verbose, costs off)
update grem1 set a = 22 where a = 2;
+update grem1 set a = 22 where a = 2;
+select * from gloc1;
+select * from grem1;
+delete from grem1;
+
+-- test copy from
+copy grem1 from stdin;
+1
+2
+\.
+select * from gloc1;
+select * from grem1;
+delete from grem1;
+
+-- test batch insert
+alter server loopback options (add batch_size '10');
+explain (verbose, costs off)
+insert into grem1 (a) values (1), (2);
+insert into grem1 (a) values (1), (2);
select * from gloc1;
select * from grem1;
+delete from grem1;
+alter server loopback options (drop batch_size);
-- ===================================================================
-- test local triggers
@@ -2534,6 +2560,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
+CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
FOR VALUES FROM (1) TO (100);
@@ -2553,7 +2580,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
\d import_dest2.*
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
- OPTIONS (import_collate 'false', import_not_null 'false');
+ OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
\det+ import_dest3.*
\d import_dest3.*
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index c9fce77599..0075bc3dbb 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -555,6 +555,18 @@ OPTIONS (ADD password_required 'false');
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>import_generated</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ This option controls whether column <literal>GENERATED</literal> expressions
+ are included in the definitions of foreign tables imported
+ from a foreign server. The default is <literal>true</literal>.
+ The <command>IMPORT</command> will fail altogether if an imported generated
+ expression uses a function or operator that does not exist locally.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>import_not_null</literal> (<type>boolean</type>)</term>
<listitem>
[application/octet-stream] postgres_fdw-generated-columns-PG12-PG13.patch (17.6K, ../../CAPmGK15Ze73T+U=P2-gyGXz8tbGHN371B=5c4WVXiPqy0u7VOA@mail.gmail.com/3-postgres_fdw-generated-columns-PG12-PG13.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index ad37a74221..2029036c62 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -1708,6 +1708,7 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
@@ -1737,12 +1738,20 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
first = true;
foreach(lc, targetAttrs)
{
+ int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
+
if (!first)
appendStringInfoString(buf, ", ");
first = false;
- appendStringInfo(buf, "$%d", pindex);
- pindex++;
+ if (attr->attgenerated)
+ appendStringInfoString(buf, "DEFAULT");
+ else
+ {
+ appendStringInfo(buf, "$%d", pindex);
+ pindex++;
+ }
}
appendStringInfoChar(buf, ')');
@@ -1772,6 +1781,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
@@ -1785,14 +1795,20 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
foreach(lc, targetAttrs)
{
int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
if (!first)
appendStringInfoString(buf, ", ");
first = false;
deparseColumnRef(buf, rtindex, attnum, rte, false);
- appendStringInfo(buf, " = $%d", pindex);
- pindex++;
+ if (attr->attgenerated)
+ appendStringInfoString(buf, " = DEFAULT");
+ else
+ {
+ appendStringInfo(buf, " = $%d", pindex);
+ pindex++;
+ }
}
appendStringInfoString(buf, " WHERE ctid = $1");
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7978f0e313..b34e282a04 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6395,13 +6395,36 @@ select * from rem1;
-- ===================================================================
-- test generated columns
-- ===================================================================
-create table gloc1 (a int, b int);
+create table gloc1 (
+ a int,
+ b int generated always as (a * 2) stored);
alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
b int generated always as (a * 2) stored)
server loopback options(table_name 'gloc1');
+explain (verbose, costs off)
+insert into grem1 (a) values (1), (2);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Insert on public.grem1
+ Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1, NULL::integer
+(4 rows)
+
insert into grem1 (a) values (1), (2);
+explain (verbose, costs off)
+update grem1 set a = 22 where a = 2;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Update on public.grem1
+ Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT WHERE ctid = $1
+ -> Foreign Scan on public.grem1
+ Output: 22, b, ctid
+ Remote SQL: SELECT b, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
+(5 rows)
+
update grem1 set a = 22 where a = 2;
select * from gloc1;
a | b
@@ -6417,6 +6440,24 @@ select * from grem1;
22 | 44
(2 rows)
+delete from grem1;
+-- test copy from
+copy grem1 from stdin;
+select * from gloc1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+select * from grem1;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+delete from grem1;
-- ===================================================================
-- test local triggers
-- ===================================================================
@@ -8316,6 +8357,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
+CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
FOR VALUES FROM (1) TO (100);
@@ -8331,7 +8373,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest1.*
Foreign table "import_dest1.t1"
@@ -8381,6 +8424,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest1.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+-------------------------------------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
-- Options
CREATE SCHEMA import_dest2;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
@@ -8395,7 +8446,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest2.*
Foreign table "import_dest2.t1"
@@ -8445,9 +8497,17 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest2.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+-------------------------------------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
- OPTIONS (import_collate 'false', import_not_null 'false');
+ OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
\det+ import_dest3.*
List of foreign tables
Schema | Table | Server | FDW options | Description
@@ -8458,7 +8518,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(6 rows)
+ import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(7 rows)
\d import_dest3.*
Foreign table "import_dest3.t1"
@@ -8508,6 +8569,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
Server: loopback
FDW options: (schema_name 'import_source', table_name 'x 5')
+ Foreign table "import_dest3.x 6"
+ Column | Type | Collation | Nullable | Default | FDW options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+ c2 | integer | | | | (column_name 'c2')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 'x 6')
+
-- Check LIMIT TO and EXCEPT
CREATE SCHEMA import_dest4;
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
@@ -8530,7 +8599,8 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
import_dest4 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(5 rows)
+ import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
+(6 rows)
-- Assorted error cases
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 82ed9e41d9..53fedb33c5 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3621,6 +3621,9 @@ create_foreign_modify(EState *estate,
Assert(!attr->attisdropped);
+ /* Ignore generated columns; they are set to DEFAULT */
+ if (attr->attgenerated)
+ continue;
getTypeOutputInfo(attr->atttypid, &typefnoid, &isvarlena);
fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
fmstate->p_nums++;
@@ -3806,6 +3809,7 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
/* get following parameters from slot */
if (slot != NULL && fmstate->target_attrs != NIL)
{
+ TupleDesc tupdesc = RelationGetDescr(fmstate->rel);
int nestlevel;
ListCell *lc;
@@ -3814,9 +3818,13 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
foreach(lc, fmstate->target_attrs)
{
int attnum = lfirst_int(lc);
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
Datum value;
bool isnull;
+ /* Ignore generated columns; they are set to DEFAULT */
+ if (attr->attgenerated)
+ continue;
value = slot_getattr(slot, attnum, &isnull);
if (isnull)
p_values[pindex] = NULL;
@@ -4728,6 +4736,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
List *commands = NIL;
bool import_collate = true;
bool import_default = false;
+ bool import_generated = true;
bool import_not_null = true;
ForeignServer *server;
UserMapping *mapping;
@@ -4747,6 +4756,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
import_collate = defGetBoolean(def);
else if (strcmp(def->defname, "import_default") == 0)
import_default = defGetBoolean(def);
+ else if (strcmp(def->defname, "import_generated") == 0)
+ import_generated = defGetBoolean(def);
else if (strcmp(def->defname, "import_not_null") == 0)
import_not_null = defGetBoolean(def);
else
@@ -4808,13 +4819,24 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
* include a schema name for types/functions in other schemas, which
* is what we want.
*/
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " attname, "
+ " format_type(atttypid, atttypmod), "
+ " attnotnull, ");
+
+ /* Generated columns are supported since Postgres 12 */
+ if (PQserverVersion(conn) >= 120000)
+ appendStringInfoString(&buf,
+ " attgenerated, "
+ " pg_get_expr(adbin, adrelid), ");
+ else
+ appendStringInfoString(&buf,
+ " NULL, "
+ " pg_get_expr(adbin, adrelid), ");
+
if (import_collate)
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
" collname, "
" collnsp.nspname "
"FROM pg_class c "
@@ -4831,11 +4853,6 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
" collnsp.oid = collnamespace ");
else
appendStringInfoString(&buf,
- "SELECT relname, "
- " attname, "
- " format_type(atttypid, atttypmod), "
- " attnotnull, "
- " pg_get_expr(adbin, adrelid), "
" NULL, NULL "
"FROM pg_class c "
" JOIN pg_namespace n ON "
@@ -4911,6 +4928,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
char *attname;
char *typename;
char *attnotnull;
+ char *attgenerated;
char *attdefault;
char *collname;
char *collnamespace;
@@ -4922,12 +4940,14 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
attname = PQgetvalue(res, i, 1);
typename = PQgetvalue(res, i, 2);
attnotnull = PQgetvalue(res, i, 3);
- attdefault = PQgetisnull(res, i, 4) ? (char *) NULL :
+ attgenerated = PQgetisnull(res, i, 4) ? (char *) NULL :
PQgetvalue(res, i, 4);
- collname = PQgetisnull(res, i, 5) ? (char *) NULL :
+ attdefault = PQgetisnull(res, i, 5) ? (char *) NULL :
PQgetvalue(res, i, 5);
- collnamespace = PQgetisnull(res, i, 6) ? (char *) NULL :
+ collname = PQgetisnull(res, i, 6) ? (char *) NULL :
PQgetvalue(res, i, 6);
+ collnamespace = PQgetisnull(res, i, 7) ? (char *) NULL :
+ PQgetvalue(res, i, 7);
if (first_item)
first_item = false;
@@ -4955,9 +4975,20 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
quote_identifier(collname));
/* Add DEFAULT if needed */
- if (import_default && attdefault != NULL)
+ if (import_default && attdefault != NULL &&
+ (!attgenerated || !attgenerated[0]))
appendStringInfo(&buf, " DEFAULT %s", attdefault);
+ /* Add GENERATED if needed */
+ if (import_generated && attgenerated != NULL &&
+ attgenerated[0] == ATTRIBUTE_GENERATED_STORED)
+ {
+ Assert(attdefault != NULL);
+ appendStringInfo(&buf,
+ " GENERATED ALWAYS AS (%s) STORED",
+ attdefault);
+ }
+
/* Add NOT NULL if needed */
if (import_not_null && attnotnull[0] == 't')
appendStringInfoString(&buf, " NOT NULL");
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 33c574b112..b85d67b299 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1442,16 +1442,32 @@ select * from rem1;
-- ===================================================================
-- test generated columns
-- ===================================================================
-create table gloc1 (a int, b int);
+create table gloc1 (
+ a int,
+ b int generated always as (a * 2) stored);
alter table gloc1 set (autovacuum_enabled = 'false');
create foreign table grem1 (
a int,
b int generated always as (a * 2) stored)
server loopback options(table_name 'gloc1');
+explain (verbose, costs off)
+insert into grem1 (a) values (1), (2);
insert into grem1 (a) values (1), (2);
+explain (verbose, costs off)
+update grem1 set a = 22 where a = 2;
update grem1 set a = 22 where a = 2;
select * from gloc1;
select * from grem1;
+delete from grem1;
+
+-- test copy from
+copy grem1 from stdin;
+1
+2
+\.
+select * from gloc1;
+select * from grem1;
+delete from grem1;
-- ===================================================================
-- test local triggers
@@ -2354,6 +2370,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
+CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
FOR VALUES FROM (1) TO (100);
@@ -2371,7 +2388,7 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
\d import_dest2.*
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
- OPTIONS (import_collate 'false', import_not_null 'false');
+ OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
\det+ import_dest3.*
\d import_dest3.*
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 4efaf35d3c..fd7a6fa871 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -441,6 +441,18 @@ OPTIONS (ADD password_required 'false');
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>import_generated</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ This option controls whether column <literal>GENERATED</literal> expressions
+ are included in the definitions of foreign tables imported
+ from a foreign server. The default is <literal>true</literal>.
+ The <command>IMPORT</command> will fail altogether if an imported generated
+ expression uses a function or operator that does not exist locally.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>import_not_null</literal></term>
<listitem>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #16631: postgres_fdw tries to insert into generated columns
@ 2021-08-05 11:19 Etsuro Fujita <etsuro.fujita@gmail.com>
parent: Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Etsuro Fujita @ 2021-08-05 11:19 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; dacherny@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Aug 4, 2021 at 5:30 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> I'll apply these if there are no objections.
Applied.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2021-08-05 11:19 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-23 18:41 BUG #16631: postgres_fdw tries to insert into generated columns PG Bug reporting form <noreply@postgresql.org>
2021-07-03 10:44 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-07 07:20 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-07 08:44 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2021-07-07 09:35 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-07 15:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-07-12 20:36 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2021-07-15 09:15 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-08-04 08:30 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-08-05 11:19 ` Etsuro Fujita <etsuro.fujita@gmail.com>
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