summary | shortlog | log | commit | commitdiff | tree
raw | patch | inline | side by side (parent: a72ad63)
raw | patch | inline | side by side (parent: a72ad63)
| author | Etsuro Fujita <efujita@postgresql.org> | |
| Thu, 5 Aug 2021 11:00:02 +0000 (20:00 +0900) | ||
| committer | Etsuro Fujita <efujita@postgresql.org> | |
| Thu, 5 Aug 2021 11:00:02 +0000 (20:00 +0900) |
postgres_fdw imported generated columns from the remote tables as plain
columns, and caused failures like "ERROR: cannot insert a non-DEFAULT
value into column "foo"" when inserting into the foreign tables, as it
tried to insert values into the generated columns. To fix, we do the
following under the assumption that generated columns in a postgres_fdw
foreign table are defined so that they represent generated columns in
the underlying remote table:
* Send DEFAULT for the generated columns to the foreign server on insert
or update, not generated column values computed on the local server.
* Add to postgresImportForeignSchema() an option "import_generated" to
include column generated expressions in the definitions of foreign
tables imported from a foreign server. The option is true by default.
The assumption seems reasonable, because that would make a query of the
postgres_fdw foreign table return values for the generated columns that
are consistent with the generated expression.
While here, fix another issue in postgresImportForeignSchema(): it tried
to include column generated expressions as column default expressions in
the foreign table definitions when the import_default option was enabled.
Per bug #16631 from Daniel Cherniy. Back-patch to v12 where generated
columns were added.
Discussion: https://postgr.es/m/16631-e929fe9db0ffc7cf%40postgresql.org
columns, and caused failures like "ERROR: cannot insert a non-DEFAULT
value into column "foo"" when inserting into the foreign tables, as it
tried to insert values into the generated columns. To fix, we do the
following under the assumption that generated columns in a postgres_fdw
foreign table are defined so that they represent generated columns in
the underlying remote table:
* Send DEFAULT for the generated columns to the foreign server on insert
or update, not generated column values computed on the local server.
* Add to postgresImportForeignSchema() an option "import_generated" to
include column generated expressions in the definitions of foreign
tables imported from a foreign server. The option is true by default.
The assumption seems reasonable, because that would make a query of the
postgres_fdw foreign table return values for the generated columns that
are consistent with the generated expression.
While here, fix another issue in postgresImportForeignSchema(): it tried
to include column generated expressions as column default expressions in
the foreign table definitions when the import_default option was enabled.
Per bug #16631 from Daniel Cherniy. Back-patch to v12 where generated
columns were added.
Discussion: https://postgr.es/m/16631-e929fe9db0ffc7cf%40postgresql.org
index ad37a7422133eb0d4a52cb818e4e23faee0e515a..2029036c62a3c5e0d573ff5f7f2fe016f475cc9e 100644 (file)
List *withCheckOptionList, List *returningList,
List **retrieved_attrs)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
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, ')');
List *withCheckOptionList, List *returningList,
List **retrieved_attrs)
{
+ TupleDesc tupdesc = RelationGetDescr(rel);
AttrNumber pindex;
bool first;
ListCell *lc;
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 7978f0e313024cdd17753728e8c8fddbdedd63bc..b34e282a04fe208edc699655d07812d1ef84fc66 100644 (file)
-- ===================================================================
-- 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
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
-- ===================================================================
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);
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"
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
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"
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
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"
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)
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;
index 82ed9e41d9ff0ebbc93345756283d5d1e059776d..53fedb33c5ad09adef718064b42406908b19ebb6 100644 (file)
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++;
/* get following parameters from slot */
if (slot != NULL && fmstate->target_attrs != NIL)
{
+ TupleDesc tupdesc = RelationGetDescr(fmstate->rel);
int nestlevel;
ListCell *lc;
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;
List *commands = NIL;
bool import_collate = true;
bool import_default = false;
+ bool import_generated = true;
bool import_not_null = true;
ForeignServer *server;
UserMapping *mapping;
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
* 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 "
" 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 "
char *attname;
char *typename;
char *attnotnull;
+ char *attgenerated;
char *attdefault;
char *collname;
char *collnamespace;
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;
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");
index 33c574b11283fb1e1356483c28531061561460c5..b85d67b2998c658706d73c70fd4be535cfb59805 100644 (file)
-- ===================================================================
-- 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
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);
\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.*
index 4efaf35d3c4ecc50b07d6bc1d803bc13d3e317fd..fd7a6fa871fa14f7303ceb1ffe2b3f45088963c6 100644 (file)
</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>