Skip to content

Commit 588d3f5

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Fix issues with generated columns in foreign tables.
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
1 parent ecbdbdf commit 588d3f5

File tree

6 files changed

+238
-38
lines changed

6 files changed

+238
-38
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 38 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1717,6 +1717,7 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
17171717
List *withCheckOptionList, List *returningList,
17181718
List **retrieved_attrs, int *values_end_len)
17191719
{
1720+
TupleDesc tupdesc = RelationGetDescr(rel);
17201721
AttrNumber pindex;
17211722
bool first;
17221723
ListCell *lc;
@@ -1746,12 +1747,20 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
17461747
first = true;
17471748
foreach(lc, targetAttrs)
17481749
{
1750+
int attnum = lfirst_int(lc);
1751+
Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
1752+
17491753
if (!first)
17501754
appendStringInfoString(buf, ", ");
17511755
first = false;
17521756

1753-
appendStringInfo(buf, "$%d", pindex);
1754-
pindex++;
1757+
if (attr->attgenerated)
1758+
appendStringInfoString(buf, "DEFAULT");
1759+
else
1760+
{
1761+
appendStringInfo(buf, "$%d", pindex);
1762+
pindex++;
1763+
}
17551764
}
17561765

17571766
appendStringInfoChar(buf, ')');
@@ -1775,14 +1784,16 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
17751784
* right number of parameters.
17761785
*/
17771786
void
1778-
rebuildInsertSql(StringInfo buf, char *orig_query,
1779-
int values_end_len, int num_cols,
1787+
rebuildInsertSql(StringInfo buf, Relation rel,
1788+
char *orig_query, List *target_attrs,
1789+
int values_end_len, int num_params,
17801790
int num_rows)
17811791
{
1782-
int i,
1783-
j;
1792+
TupleDesc tupdesc = RelationGetDescr(rel);
1793+
int i;
17841794
int pindex;
17851795
bool first;
1796+
ListCell *lc;
17861797

17871798
/* Make sure the values_end_len is sensible */
17881799
Assert((values_end_len > 0) && (values_end_len <= strlen(orig_query)));
@@ -1794,20 +1805,28 @@ rebuildInsertSql(StringInfo buf, char *orig_query,
17941805
* Add records to VALUES clause (we already have parameters for the first
17951806
* row, so start at the right offset).
17961807
*/
1797-
pindex = num_cols + 1;
1808+
pindex = num_params + 1;
17981809
for (i = 0; i < num_rows; i++)
17991810
{
18001811
appendStringInfoString(buf, ", (");
18011812

18021813
first = true;
1803-
for (j = 0; j < num_cols; j++)
1814+
foreach(lc, target_attrs)
18041815
{
1816+
int attnum = lfirst_int(lc);
1817+
Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
1818+
18051819
if (!first)
18061820
appendStringInfoString(buf, ", ");
18071821
first = false;
18081822

1809-
appendStringInfo(buf, "$%d", pindex);
1810-
pindex++;
1823+
if (attr->attgenerated)
1824+
appendStringInfoString(buf, "DEFAULT");
1825+
else
1826+
{
1827+
appendStringInfo(buf, "$%d", pindex);
1828+
pindex++;
1829+
}
18111830
}
18121831

18131832
appendStringInfoChar(buf, ')');
@@ -1831,6 +1850,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
18311850
List *withCheckOptionList, List *returningList,
18321851
List **retrieved_attrs)
18331852
{
1853+
TupleDesc tupdesc = RelationGetDescr(rel);
18341854
AttrNumber pindex;
18351855
bool first;
18361856
ListCell *lc;
@@ -1844,14 +1864,20 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
18441864
foreach(lc, targetAttrs)
18451865
{
18461866
int attnum = lfirst_int(lc);
1867+
Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
18471868

18481869
if (!first)
18491870
appendStringInfoString(buf, ", ");
18501871
first = false;
18511872

18521873
deparseColumnRef(buf, rtindex, attnum, rte, false);
1853-
appendStringInfo(buf, " = $%d", pindex);
1854-
pindex++;
1874+
if (attr->attgenerated)
1875+
appendStringInfoString(buf, " = DEFAULT");
1876+
else
1877+
{
1878+
appendStringInfo(buf, " = $%d", pindex);
1879+
pindex++;
1880+
}
18551881
}
18561882
appendStringInfoString(buf, " WHERE ctid = $1");
18571883

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 107 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -6458,13 +6458,37 @@ select * from rem1;
64586458
-- ===================================================================
64596459
-- test generated columns
64606460
-- ===================================================================
6461-
create table gloc1 (a int, b int);
6461+
create table gloc1 (
6462+
a int,
6463+
b int generated always as (a * 2) stored);
64626464
alter table gloc1 set (autovacuum_enabled = 'false');
64636465
create foreign table grem1 (
64646466
a int,
64656467
b int generated always as (a * 2) stored)
64666468
server loopback options(table_name 'gloc1');
6469+
explain (verbose, costs off)
6470+
insert into grem1 (a) values (1), (2);
6471+
QUERY PLAN
6472+
-------------------------------------------------------------------
6473+
Insert on public.grem1
6474+
Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
6475+
Batch Size: 1
6476+
-> Values Scan on "*VALUES*"
6477+
Output: "*VALUES*".column1, NULL::integer
6478+
(5 rows)
6479+
64676480
insert into grem1 (a) values (1), (2);
6481+
explain (verbose, costs off)
6482+
update grem1 set a = 22 where a = 2;
6483+
QUERY PLAN
6484+
------------------------------------------------------------------------------------
6485+
Update on public.grem1
6486+
Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT WHERE ctid = $1
6487+
-> Foreign Scan on public.grem1
6488+
Output: 22, ctid, grem1.*
6489+
Remote SQL: SELECT a, b, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
6490+
(5 rows)
6491+
64686492
update grem1 set a = 22 where a = 2;
64696493
select * from gloc1;
64706494
a | b
@@ -6480,6 +6504,54 @@ select * from grem1;
64806504
22 | 44
64816505
(2 rows)
64826506

6507+
delete from grem1;
6508+
-- test copy from
6509+
copy grem1 from stdin;
6510+
select * from gloc1;
6511+
a | b
6512+
---+---
6513+
1 | 2
6514+
2 | 4
6515+
(2 rows)
6516+
6517+
select * from grem1;
6518+
a | b
6519+
---+---
6520+
1 | 2
6521+
2 | 4
6522+
(2 rows)
6523+
6524+
delete from grem1;
6525+
-- test batch insert
6526+
alter server loopback options (add batch_size '10');
6527+
explain (verbose, costs off)
6528+
insert into grem1 (a) values (1), (2);
6529+
QUERY PLAN
6530+
-------------------------------------------------------------------
6531+
Insert on public.grem1
6532+
Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
6533+
Batch Size: 10
6534+
-> Values Scan on "*VALUES*"
6535+
Output: "*VALUES*".column1, NULL::integer
6536+
(5 rows)
6537+
6538+
insert into grem1 (a) values (1), (2);
6539+
select * from gloc1;
6540+
a | b
6541+
---+---
6542+
1 | 2
6543+
2 | 4
6544+
(2 rows)
6545+
6546+
select * from grem1;
6547+
a | b
6548+
---+---
6549+
1 | 2
6550+
2 | 4
6551+
(2 rows)
6552+
6553+
delete from grem1;
6554+
alter server loopback options (drop batch_size);
64836555
-- ===================================================================
64846556
-- test local triggers
64856557
-- ===================================================================
@@ -8566,6 +8638,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
85668638
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
85678639
CREATE TABLE import_source."x 5" (c1 float8);
85688640
ALTER TABLE import_source."x 5" DROP COLUMN c1;
8641+
CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
85698642
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
85708643
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
85718644
FOR VALUES FROM (1) TO (100);
@@ -8583,7 +8656,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
85838656
import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
85848657
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
85858658
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8586-
(6 rows)
8659+
import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
8660+
(7 rows)
85878661

85888662
\d import_dest1.*
85898663
Foreign table "import_dest1.t1"
@@ -8633,6 +8707,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
86338707
Server: loopback
86348708
FDW options: (schema_name 'import_source', table_name 'x 5')
86358709

8710+
Foreign table "import_dest1.x 6"
8711+
Column | Type | Collation | Nullable | Default | FDW options
8712+
--------+---------+-----------+----------+-------------------------------------+--------------------
8713+
c1 | integer | | | | (column_name 'c1')
8714+
c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
8715+
Server: loopback
8716+
FDW options: (schema_name 'import_source', table_name 'x 6')
8717+
86368718
-- Options
86378719
CREATE SCHEMA import_dest2;
86388720
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
@@ -8647,7 +8729,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
86478729
import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
86488730
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
86498731
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8650-
(6 rows)
8732+
import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
8733+
(7 rows)
86518734

86528735
\d import_dest2.*
86538736
Foreign table "import_dest2.t1"
@@ -8697,9 +8780,17 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
86978780
Server: loopback
86988781
FDW options: (schema_name 'import_source', table_name 'x 5')
86998782

8783+
Foreign table "import_dest2.x 6"
8784+
Column | Type | Collation | Nullable | Default | FDW options
8785+
--------+---------+-----------+----------+-------------------------------------+--------------------
8786+
c1 | integer | | | | (column_name 'c1')
8787+
c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
8788+
Server: loopback
8789+
FDW options: (schema_name 'import_source', table_name 'x 6')
8790+
87008791
CREATE SCHEMA import_dest3;
87018792
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
8702-
OPTIONS (import_collate 'false', import_not_null 'false');
8793+
OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
87038794
\det+ import_dest3.*
87048795
List of foreign tables
87058796
Schema | Table | Server | FDW options | Description
@@ -8710,7 +8801,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
87108801
import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
87118802
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
87128803
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8713-
(6 rows)
8804+
import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
8805+
(7 rows)
87148806

87158807
\d import_dest3.*
87168808
Foreign table "import_dest3.t1"
@@ -8760,6 +8852,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
87608852
Server: loopback
87618853
FDW options: (schema_name 'import_source', table_name 'x 5')
87628854

8855+
Foreign table "import_dest3.x 6"
8856+
Column | Type | Collation | Nullable | Default | FDW options
8857+
--------+---------+-----------+----------+---------+--------------------
8858+
c1 | integer | | | | (column_name 'c1')
8859+
c2 | integer | | | | (column_name 'c2')
8860+
Server: loopback
8861+
FDW options: (schema_name 'import_source', table_name 'x 6')
8862+
87638863
-- Check LIMIT TO and EXCEPT
87648864
CREATE SCHEMA import_dest4;
87658865
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part)
@@ -8784,7 +8884,8 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
87848884
import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
87858885
import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
87868886
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8787-
(6 rows)
8887+
import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
8888+
(7 rows)
87888889

87898890
-- Assorted error cases
87908891
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;

0 commit comments

Comments
 (0)