Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitaa769f8

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 plaincolumns, and caused failures like "ERROR: cannot insert a non-DEFAULTvalue into column "foo"" when inserting into the foreign tables, as ittried to insert values into the generated columns. To fix, we do thefollowing under the assumption that generated columns in a postgres_fdwforeign table are defined so that they represent generated columns inthe 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 thepostgres_fdw foreign table return values for the generated columns thatare consistent with the generated expression.While here, fix another issue in postgresImportForeignSchema(): it triedto include column generated expressions as column default expressions inthe foreign table definitions when the import_default option was enabled.Per bug #16631 from Daniel Cherniy. Back-patch to v12 where generatedcolumns were added.Discussion:https://postgr.es/m/16631-e929fe9db0ffc7cf%40postgresql.org
1 parent93d573d commitaa769f8

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
@@ -1844,6 +1844,7 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
18441844
List*withCheckOptionList,List*returningList,
18451845
List**retrieved_attrs,int*values_end_len)
18461846
{
1847+
TupleDesctupdesc=RelationGetDescr(rel);
18471848
AttrNumberpindex;
18481849
boolfirst;
18491850
ListCell*lc;
@@ -1873,12 +1874,20 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
18731874
first= true;
18741875
foreach(lc,targetAttrs)
18751876
{
1877+
intattnum=lfirst_int(lc);
1878+
Form_pg_attributeattr=TupleDescAttr(tupdesc,attnum-1);
1879+
18761880
if (!first)
18771881
appendStringInfoString(buf,", ");
18781882
first= false;
18791883

1880-
appendStringInfo(buf,"$%d",pindex);
1881-
pindex++;
1884+
if (attr->attgenerated)
1885+
appendStringInfoString(buf,"DEFAULT");
1886+
else
1887+
{
1888+
appendStringInfo(buf,"$%d",pindex);
1889+
pindex++;
1890+
}
18821891
}
18831892

18841893
appendStringInfoChar(buf,')');
@@ -1902,14 +1911,16 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
19021911
* right number of parameters.
19031912
*/
19041913
void
1905-
rebuildInsertSql(StringInfobuf,char*orig_query,
1906-
intvalues_end_len,intnum_cols,
1914+
rebuildInsertSql(StringInfobuf,Relationrel,
1915+
char*orig_query,List*target_attrs,
1916+
intvalues_end_len,intnum_params,
19071917
intnum_rows)
19081918
{
1909-
inti,
1910-
j;
1919+
TupleDesctupdesc=RelationGetDescr(rel);
1920+
inti;
19111921
intpindex;
19121922
boolfirst;
1923+
ListCell*lc;
19131924

19141925
/* Make sure the values_end_len is sensible */
19151926
Assert((values_end_len>0)&& (values_end_len <=strlen(orig_query)));
@@ -1921,20 +1932,28 @@ rebuildInsertSql(StringInfo buf, char *orig_query,
19211932
* Add records to VALUES clause (we already have parameters for the first
19221933
* row, so start at the right offset).
19231934
*/
1924-
pindex=num_cols+1;
1935+
pindex=num_params+1;
19251936
for (i=0;i<num_rows;i++)
19261937
{
19271938
appendStringInfoString(buf,", (");
19281939

19291940
first= true;
1930-
for (j=0;j<num_cols;j++)
1941+
foreach(lc,target_attrs)
19311942
{
1943+
intattnum=lfirst_int(lc);
1944+
Form_pg_attributeattr=TupleDescAttr(tupdesc,attnum-1);
1945+
19321946
if (!first)
19331947
appendStringInfoString(buf,", ");
19341948
first= false;
19351949

1936-
appendStringInfo(buf,"$%d",pindex);
1937-
pindex++;
1950+
if (attr->attgenerated)
1951+
appendStringInfoString(buf,"DEFAULT");
1952+
else
1953+
{
1954+
appendStringInfo(buf,"$%d",pindex);
1955+
pindex++;
1956+
}
19381957
}
19391958

19401959
appendStringInfoChar(buf,')');
@@ -1958,6 +1977,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
19581977
List*withCheckOptionList,List*returningList,
19591978
List**retrieved_attrs)
19601979
{
1980+
TupleDesctupdesc=RelationGetDescr(rel);
19611981
AttrNumberpindex;
19621982
boolfirst;
19631983
ListCell*lc;
@@ -1971,14 +1991,20 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
19711991
foreach(lc,targetAttrs)
19721992
{
19731993
intattnum=lfirst_int(lc);
1994+
Form_pg_attributeattr=TupleDescAttr(tupdesc,attnum-1);
19741995

19751996
if (!first)
19761997
appendStringInfoString(buf,", ");
19771998
first= false;
19781999

19792000
deparseColumnRef(buf,rtindex,attnum,rte, false);
1980-
appendStringInfo(buf," = $%d",pindex);
1981-
pindex++;
2001+
if (attr->attgenerated)
2002+
appendStringInfoString(buf," = DEFAULT");
2003+
else
2004+
{
2005+
appendStringInfo(buf," = $%d",pindex);
2006+
pindex++;
2007+
}
19822008
}
19832009
appendStringInfoString(buf," WHERE ctid = $1");
19842010

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 107 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -6548,13 +6548,37 @@ select * from rem1;
65486548
-- ===================================================================
65496549
-- test generated columns
65506550
-- ===================================================================
6551-
create table gloc1 (a int, b int);
6551+
create table gloc1 (
6552+
a int,
6553+
b int generated always as (a * 2) stored);
65526554
alter table gloc1 set (autovacuum_enabled = 'false');
65536555
create foreign table grem1 (
65546556
a int,
65556557
b int generated always as (a * 2) stored)
65566558
server loopback options(table_name 'gloc1');
6559+
explain (verbose, costs off)
6560+
insert into grem1 (a) values (1), (2);
6561+
QUERY PLAN
6562+
-------------------------------------------------------------------
6563+
Insert on public.grem1
6564+
Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
6565+
Batch Size: 1
6566+
-> Values Scan on "*VALUES*"
6567+
Output: "*VALUES*".column1, NULL::integer
6568+
(5 rows)
6569+
65576570
insert into grem1 (a) values (1), (2);
6571+
explain (verbose, costs off)
6572+
update grem1 set a = 22 where a = 2;
6573+
QUERY PLAN
6574+
------------------------------------------------------------------------------------
6575+
Update on public.grem1
6576+
Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT WHERE ctid = $1
6577+
-> Foreign Scan on public.grem1
6578+
Output: 22, ctid, grem1.*
6579+
Remote SQL: SELECT a, b, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
6580+
(5 rows)
6581+
65586582
update grem1 set a = 22 where a = 2;
65596583
select * from gloc1;
65606584
a | b
@@ -6570,6 +6594,54 @@ select * from grem1;
65706594
22 | 44
65716595
(2 rows)
65726596

6597+
delete from grem1;
6598+
-- test copy from
6599+
copy grem1 from stdin;
6600+
select * from gloc1;
6601+
a | b
6602+
---+---
6603+
1 | 2
6604+
2 | 4
6605+
(2 rows)
6606+
6607+
select * from grem1;
6608+
a | b
6609+
---+---
6610+
1 | 2
6611+
2 | 4
6612+
(2 rows)
6613+
6614+
delete from grem1;
6615+
-- test batch insert
6616+
alter server loopback options (add batch_size '10');
6617+
explain (verbose, costs off)
6618+
insert into grem1 (a) values (1), (2);
6619+
QUERY PLAN
6620+
-------------------------------------------------------------------
6621+
Insert on public.grem1
6622+
Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
6623+
Batch Size: 10
6624+
-> Values Scan on "*VALUES*"
6625+
Output: "*VALUES*".column1, NULL::integer
6626+
(5 rows)
6627+
6628+
insert into grem1 (a) values (1), (2);
6629+
select * from gloc1;
6630+
a | b
6631+
---+---
6632+
1 | 2
6633+
2 | 4
6634+
(2 rows)
6635+
6636+
select * from grem1;
6637+
a | b
6638+
---+---
6639+
1 | 2
6640+
2 | 4
6641+
(2 rows)
6642+
6643+
delete from grem1;
6644+
alter server loopback options (drop batch_size);
65736645
-- ===================================================================
65746646
-- test local triggers
65756647
-- ===================================================================
@@ -8656,6 +8728,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
86568728
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
86578729
CREATE TABLE import_source."x 5" (c1 float8);
86588730
ALTER TABLE import_source."x 5" DROP COLUMN c1;
8731+
CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored);
86598732
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
86608733
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
86618734
FOR VALUES FROM (1) TO (100);
@@ -8673,7 +8746,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
86738746
import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
86748747
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
86758748
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8676-
(6 rows)
8749+
import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
8750+
(7 rows)
86778751

86788752
\d import_dest1.*
86798753
Foreign table "import_dest1.t1"
@@ -8723,6 +8797,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
87238797
Server: loopback
87248798
FDW options: (schema_name 'import_source', table_name 'x 5')
87258799

8800+
Foreign table "import_dest1.x 6"
8801+
Column | Type | Collation | Nullable | Default | FDW options
8802+
--------+---------+-----------+----------+-------------------------------------+--------------------
8803+
c1 | integer | | | | (column_name 'c1')
8804+
c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
8805+
Server: loopback
8806+
FDW options: (schema_name 'import_source', table_name 'x 6')
8807+
87268808
-- Options
87278809
CREATE SCHEMA import_dest2;
87288810
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
87378819
import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
87388820
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
87398821
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8740-
(6 rows)
8822+
import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
8823+
(7 rows)
87418824

87428825
\d import_dest2.*
87438826
Foreign table "import_dest2.t1"
@@ -8787,9 +8870,17 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
87878870
Server: loopback
87888871
FDW options: (schema_name 'import_source', table_name 'x 5')
87898872

8873+
Foreign table "import_dest2.x 6"
8874+
Column | Type | Collation | Nullable | Default | FDW options
8875+
--------+---------+-----------+----------+-------------------------------------+--------------------
8876+
c1 | integer | | | | (column_name 'c1')
8877+
c2 | integer | | | generated always as (c1 * 2) stored | (column_name 'c2')
8878+
Server: loopback
8879+
FDW options: (schema_name 'import_source', table_name 'x 6')
8880+
87908881
CREATE SCHEMA import_dest3;
87918882
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
8792-
OPTIONS (import_collate 'false', import_not_null 'false');
8883+
OPTIONS (import_collate 'false',import_generated 'false',import_not_null 'false');
87938884
\det+ import_dest3.*
87948885
List of foreign tables
87958886
Schema | Table | Server | FDW options | Description
@@ -8800,7 +8891,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
88008891
import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
88018892
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
88028893
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8803-
(6 rows)
8894+
import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
8895+
(7 rows)
88048896

88058897
\d import_dest3.*
88068898
Foreign table "import_dest3.t1"
@@ -8850,6 +8942,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4')
88508942
Server: loopback
88518943
FDW options: (schema_name 'import_source', table_name 'x 5')
88528944

8945+
Foreign table "import_dest3.x 6"
8946+
Column | Type | Collation | Nullable | Default | FDW options
8947+
--------+---------+-----------+----------+---------+--------------------
8948+
c1 | integer | | | | (column_name 'c1')
8949+
c2 | integer | | | | (column_name 'c2')
8950+
Server: loopback
8951+
FDW options: (schema_name 'import_source', table_name 'x 6')
8952+
88538953
-- Check LIMIT TO and EXCEPT
88548954
CREATE SCHEMA import_dest4;
88558955
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)
88748974
import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
88758975
import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
88768976
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8877-
(6 rows)
8977+
import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') |
8978+
(7 rows)
88788979

88798980
-- Assorted error cases
88808981
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp