aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--contrib/postgres_fdw/deparse.c24
-rw-r--r--contrib/postgres_fdw/expected/postgres_fdw.out82
-rw-r--r--contrib/postgres_fdw/postgres_fdw.c59
-rw-r--r--contrib/postgres_fdw/sql/postgres_fdw.sql21
-rw-r--r--doc/src/sgml/postgres-fdw.sgml12
5 files changed, 172 insertions, 26 deletions
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 613c8e1319c..a74111cb788 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -1710,6 +1710,7 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
@@ -1739,12 +1740,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, ')');
@@ -1774,6 +1783,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
@@ -1787,14 +1797,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 17364ed7052..546281103fc 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6365,13 +6365,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
@@ -6387,6 +6410,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
-- ===================================================================
@@ -8286,6 +8327,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);
@@ -8301,7 +8343,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"
@@ -8351,6 +8394,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
@@ -8365,7 +8416,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"
@@ -8415,9 +8467,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
@@ -8428,7 +8488,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"
@@ -8478,6 +8539,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)
@@ -8500,7 +8569,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 4d1b906381e..4c2c339d1e5 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3567,6 +3567,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++;
@@ -3752,6 +3755,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;
@@ -3760,9 +3764,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;
@@ -4673,6 +4681,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;
@@ -4692,6 +4701,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
@@ -4753,13 +4764,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 "
@@ -4776,11 +4798,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 "
@@ -4856,6 +4873,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
char *attname;
char *typename;
char *attnotnull;
+ char *attgenerated;
char *attdefault;
char *collname;
char *collnamespace;
@@ -4867,12 +4885,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;
@@ -4900,9 +4920,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 b1aea5ff7c6..4d254486443 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1417,16 +1417,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
@@ -2329,6 +2345,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);
@@ -2346,7 +2363,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 ed369cb54b8..eda1200708e 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -406,6 +406,18 @@
</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>
<para>
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-20 20:53:09 +0000