On Thu, Apr 19, 2018 at 11:38 AM, Kyotaro HORIGUCHI
<horiguchi.kyot...@lab.ntt.co.jp> wrote:
>
>> /* No rows should be returned if no rows were updated. */
>> Assert(n_rows_returned == 0 || n_rows_updated > 0);
>
> The assertion is correct but I think that we shouldn't crash
> server by any kind of protocol error. I think ERROR is suitable.
>
That's a good idea. Done.
>> I have attached a set of patches
>> 0001 adds a test case showing the issue.
>> 0002 modified patch based on your idea of throwing an error
>> 0003 WIP patch with a partial fix for the issue as discussed upthread
>>
>> The expected output in 0001 is set to what it would when the problem
>> gets fixed. The expected output in 0002 is what it would be when we
>> commit only 0002 without a complete fix.
>> >
>> >
>> >> There are two ways to fix this
>> >> 1. Use WHERE CURRENT OF with cursors to update rows. This means that
>> >> we fetch only one row at a time and update it. This can slow down the
>> >> execution drastically.
>> >> 2. Along with ctid use tableoid as a qualifier i.e. WHERE clause of
>> >> UPDATE/DELETE statement has ctid = $1 AND tableoid = $2 as conditions.
>> >>
>> >> PFA patch along the lines of 2nd approach and along with the
>> >> testcases. The idea is to inject tableoid attribute to be fetched from
>> >> the foreign server similar to ctid and then add it to the DML
>> >> statement being constructed.
>> >>
>> >> It does fix the problem. But the patch as is interferes with the way
>> >> we handle tableoid currently. That can be seen from the regression
>> >> diffs that the patch causes. RIght now, every tableoid reference gets
>> >> converted into the tableoid of the foreign table (and not the tableoid
>> >> of the foreign table). Somehow we need to differentiate between the
>> >> tableoid injected for DML and tableoid references added by the user in
>> >> the original query and then use tableoid on the foreign server for the
>> >> first and local foreign table's oid for the second. Right now, I don't
>> >> see a simple way to do that.
>> >
>> > We cannot add no non-system (junk) columns not defined in foreign
>> > table columns.
>>
>> Why? That's a probable way of fixing this problem.
>
> In other words, tuples returned from ForeignNext
> (postgresIterateForeignScan) on a foreign (base) relation cannot
> contain a non-system column which is not a part of the relation,
> since its tuple descriptor doesn't know of and does error out it.
> The current 0003 stores remote tableoid in tuples' existing
> tableOid field (not a column data), which is not proper since
> remote tableoid is bogus for the local server. I might missing
> something here, though. If we can somehow attach an blob at the
> end of t_data and it is finally passed to
> ExecForeignUpdate/Delete, the problem would be resolved.
Attached 0003 uses HeapTupleData::t_tableoid to store remote tableoid
and local tableoid. Remote tableoid is stored there for a scan
underlying DELETE/UPDATE. Local tableoid is stored otherwise. We use a
flag fetch_foreign_tableoid, stand alone and in deparse_expr_cxt to
differentiate between these two usages.
>
> I don't think it is acceptable but (hopefully) almost solves this
> problem if we allow that. User always sees the conventional
> tableOid and all ExecForeignUpdate/Delete have to do is to use
> remote_tableoid as a part of remote tuple identifier. Required to
> consider how to propagate the remote_tableoid through joins or
> other intermediate executor nodes, though. It is partly similar
> to the way deciding join push down.
0003 does that. Fortunately we already have testing UPDATE/DELETE with joins.
>
> Another point is that, even though HeapTupleData is the only
> expected coveyer of the tuple identification, assuming tableoid +
> ctid is not adequite since FDW interface is not exlusive for
> postgres_fdw. The existig ctid field is not added for the purpose
> and just happened to (seem to) work as tuple identifier for
> postgres_fdw but I think tableoid is not.
I am not able to understand. postgresAddForeignUpdateTargets does that
specifically for postgres_fdw. I am using the same function to add
junk column for tableoid similar to ctid.
>
> The same can be said on ctid. Maybe my description was
> unclear. Specifically, I intended to say something like:
>
> - If we want to update/delete remote partitioned/inhtance tables
> without direct modify, the foreign relation must have a columns
> defined as "tableoid as remote_tableoid" or something. (We
> could change the column name by a fdw option.)
Ok. I think, I misunderstood your proposal. IIUC, this way, SELECT *
FROM foreign_table is going to report remote_tableoid, which won't be
welcome by users.
Let me know what you think of the attached patches.
>
>
>> I think we should try getting 0001 and 0002 at least committed
>> independent of 0003.
>
> Agreed on 0002. 0001 should be committed with 0003?
0001 adds testcases which show the problem, so we have to commit it
with 0003 or 0002.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
From 1a8fc73fffa522e10a831bb9e6557a9fb4b0b602 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.ba...@enterprisedb.com>
Date: Wed, 18 Apr 2018 10:20:27 +0530
Subject: [PATCH 1/3] Tests to show problem when foreign table points to a
partitioned table or inheritance table on the foreign
server
When a foreign table points to a partitioned table or an inheritance
parent on the foreign server, a non-direct DML can affect multiple
rows when only one row is intended to be affected. This
happens because postgres_fdw uses only ctid to identify a row to work
on. Though ctid uniquely identifies a row in a single table, in a
partitioned table or in an inheritance hierarchy, there can be be
multiple rows, in different partitions, with the same ctid. So
DML statement sent to the foreign server by postgres_fdw ends up
affecting more than one rows, only one of which is intended to be
affected.
This commit adds testcases to show the problem. A subsequent commit
would have a fix to the problem.
Ashutosh Bapat, reviewed by Kyotaro Horiguchi
---
contrib/postgres_fdw/expected/postgres_fdw.out | 121 ++++++++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 53 +++++++++++
2 files changed, 174 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e4d9469..5156002 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6923,6 +6923,69 @@ SELECT tableoid::regclass, * FROM ONLY a;
DROP TABLE a CASCADE;
NOTICE: drop cascades to foreign table b
DROP TABLE loct;
+-- test DML statement on a foreign table pointing to an inheritance hierarchy
+-- on the remote server
+CREATE TABLE a(aa TEXT);
+ALTER TABLE a SET (autovacuum_enabled = 'false');
+CREATE TABLE b() INHERITS(a);
+ALTER TABLE b SET (autovacuum_enabled = 'false');
+INSERT INTO a(aa) VALUES('aaa');
+INSERT INTO b(aa) VALUES('bbb');
+CREATE FOREIGN TABLE fa (aa TEXT) SERVER loopback OPTIONS (table_name 'a');
+SELECT tableoid::regclass, ctid, * FROM fa;
+ tableoid | ctid | aa
+----------+-------+-----
+ fa | (0,1) | aaa
+ fa | (0,1) | bbb
+(2 rows)
+
+-- use random() so that DML statement is not pushed down to the foreign
+-- server
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ Update on public.fa
+ Remote SQL: UPDATE public.a SET aa = $2 WHERE ctid = $1
+ -> Foreign Scan on public.fa
+ Output: CASE WHEN (random() <= '1'::double precision) THEN 'zzzz'::text ELSE NULL::text END, ctid
+ Remote SQL: SELECT ctid FROM public.a WHERE ((aa = 'aaa'::text)) FOR UPDATE
+(5 rows)
+
+UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+SELECT tableoid::regclass, ctid, * FROM fa;
+ tableoid | ctid | aa
+----------+-------+------
+ fa | (0,2) | zzzz
+ fa | (0,1) | bbb
+(2 rows)
+
+-- repopulate tables so that we have rows with same ctid
+TRUNCATE a, b;
+INSERT INTO a(aa) VALUES('aaa');
+INSERT INTO b(aa) VALUES('bbb');
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
+ Delete on public.fa
+ Remote SQL: DELETE FROM public.a WHERE ctid = $1
+ -> Foreign Scan on public.fa
+ Output: ctid
+ Filter: (fa.aa = CASE WHEN (random() <= '1'::double precision) THEN 'aaa'::text ELSE 'bbb'::text END)
+ Remote SQL: SELECT aa, ctid FROM public.a FOR UPDATE
+(6 rows)
+
+DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+SELECT tableoid::regclass, ctid, * FROM fa;
+ tableoid | ctid | aa
+----------+------+----
+ fa | (0,1) | bbb
+(1 rows)
+
+DROP FOREIGN TABLE fa;
+DROP TABLE a CASCADE;
+NOTICE: drop cascades to table b
-- Check SELECT FOR UPDATE/SHARE with an inherited source table
create table loct1 (f1 int, f2 int, f3 int);
create table loct2 (f1 int, f2 int, f3 int);
@@ -8317,3 +8380,61 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
-- Clean-up
RESET enable_partitionwise_aggregate;
+-- test DML statement on foreign table pointing to a foreign partitioned table
+CREATE TABLE plt (a int, b int) PARTITION BY LIST(a);
+CREATE TABLE plt_p1 PARTITION OF plt FOR VALUES IN (1);
+CREATE TABLE plt_p2 PARTITION OF plt FOR VALUES IN (2);
+INSERT INTO plt VALUES (1, 1), (2, 2);
+CREATE FOREIGN TABLE fplt (a int, b int) SERVER loopback OPTIONS (table_name 'plt');
+SELECT tableoid::regclass, ctid, * FROM fplt;
+ tableoid | ctid | a | b
+----------+-------+---+---
+ fplt | (0,1) | 1 | 1
+ fplt | (0,1) | 2 | 2
+(2 rows)
+
+-- use random() so that DML statement is not pushed down to the foreign
+-- server
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ Update on public.fplt
+ Remote SQL: UPDATE public.plt SET b = $2 WHERE ctid = $1
+ -> Foreign Scan on public.fplt
+ Output: a, CASE WHEN (random() <= '1'::double precision) THEN 10 ELSE 20 END, ctid
+ Remote SQL: SELECT a, ctid FROM public.plt WHERE ((a = 1)) FOR UPDATE
+(5 rows)
+
+UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+SELECT tableoid::regclass, ctid, * FROM fplt;
+ tableoid | ctid | a | b
+----------+-------+---+----
+ fplt | (0,2) | 1 | 10
+ fplt | (0,1) | 2 | 2
+(2 rows)
+
+-- repopulate partitioned table so that we have rows with same ctid
+TRUNCATE plt;
+INSERT INTO plt VALUES (1, 1), (2, 2);
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Delete on public.fplt
+ Remote SQL: DELETE FROM public.plt WHERE ctid = $1
+ -> Foreign Scan on public.fplt
+ Output: ctid
+ Filter: (fplt.a = CASE WHEN (random() <= '1'::double precision) THEN 1 ELSE 10 END)
+ Remote SQL: SELECT a, ctid FROM public.plt FOR UPDATE
+(6 rows)
+
+DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+SELECT tableoid::regclass, ctid, * FROM fplt;
+ tableoid | ctid | a | b
+----------+-------+---+----
+ fplt | (0,1) | 2 | 2
+(1 row)
+
+DROP TABLE plt;
+DROP FOREIGN TABLE fplt;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e1df952..1bec916 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1648,6 +1648,35 @@ SELECT tableoid::regclass, * FROM ONLY a;
DROP TABLE a CASCADE;
DROP TABLE loct;
+-- test DML statement on a foreign table pointing to an inheritance hierarchy
+-- on the remote server
+CREATE TABLE a(aa TEXT);
+ALTER TABLE a SET (autovacuum_enabled = 'false');
+CREATE TABLE b() INHERITS(a);
+ALTER TABLE b SET (autovacuum_enabled = 'false');
+INSERT INTO a(aa) VALUES('aaa');
+INSERT INTO b(aa) VALUES('bbb');
+CREATE FOREIGN TABLE fa (aa TEXT) SERVER loopback OPTIONS (table_name 'a');
+
+SELECT tableoid::regclass, ctid, * FROM fa;
+-- use random() so that DML statement is not pushed down to the foreign
+-- server
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+SELECT tableoid::regclass, ctid, * FROM fa;
+-- repopulate tables so that we have rows with same ctid
+TRUNCATE a, b;
+INSERT INTO a(aa) VALUES('aaa');
+INSERT INTO b(aa) VALUES('bbb');
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+SELECT tableoid::regclass, ctid, * FROM fa;
+
+DROP FOREIGN TABLE fa;
+DROP TABLE a CASCADE;
+
-- Check SELECT FOR UPDATE/SHARE with an inherited source table
create table loct1 (f1 int, f2 int, f3 int);
create table loct2 (f1 int, f2 int, f3 int);
@@ -2220,3 +2249,27 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
-- Clean-up
RESET enable_partitionwise_aggregate;
+
+-- test DML statement on foreign table pointing to a foreign partitioned table
+CREATE TABLE plt (a int, b int) PARTITION BY LIST(a);
+CREATE TABLE plt_p1 PARTITION OF plt FOR VALUES IN (1);
+CREATE TABLE plt_p2 PARTITION OF plt FOR VALUES IN (2);
+INSERT INTO plt VALUES (1, 1), (2, 2);
+CREATE FOREIGN TABLE fplt (a int, b int) SERVER loopback OPTIONS (table_name 'plt');
+SELECT tableoid::regclass, ctid, * FROM fplt;
+-- use random() so that DML statement is not pushed down to the foreign
+-- server
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+SELECT tableoid::regclass, ctid, * FROM fplt;
+-- repopulate partitioned table so that we have rows with same ctid
+TRUNCATE plt;
+INSERT INTO plt VALUES (1, 1), (2, 2);
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+SELECT tableoid::regclass, ctid, * FROM fplt;
+
+DROP TABLE plt;
+DROP FOREIGN TABLE fplt;
--
1.7.9.5
From 8f1521bba9249d02f17a4bc9be8de315bb527ec0 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.ba...@enterprisedb.com>
Date: Wed, 18 Apr 2018 11:06:20 +0530
Subject: [PATCH 2/3] Error out if one iteration of non-direct DML affects
more than one row on the foreign server
When a foreign table points to a partitioned table or an inheritance
parent on the foreign server, a non-direct DML can affect multiple
rows when only one row is intended to be affected. This happens
because postgres_fdw uses only ctid to identify a row to work on.
Though ctid uniquely identifies a row in a single table, in a
partitioned table or in an inheritance hierarchy, there can be be
multiple rows, in different partitions, with the same ctid. So a DML
statement sent to the foreign server by postgres_fdw ends up affecting
more than one rows, only one of which is intended to be affected.
In such a case it's good to throw an error instead of corrupting
remote database with unwanted UPDATE/DELETEs. Subsequent commits will
try to fix this situation.
Ashutosh Bapat and Kyotaro Horiguchi
---
contrib/postgres_fdw/expected/postgres_fdw.out | 32 +++++++++-------
contrib/postgres_fdw/postgres_fdw.c | 48 +++++++++++++++++++-----
2 files changed, 57 insertions(+), 23 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 5156002..77d24ea 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6953,10 +6953,11 @@ UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa
(5 rows)
UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+ERROR: foreign server updated 2 rows when only one row was expected to be updated
SELECT tableoid::regclass, ctid, * FROM fa;
- tableoid | ctid | aa
-----------+-------+------
- fa | (0,2) | zzzz
+ tableoid | ctid | aa
+----------+-------+-----
+ fa | (0,1) | aaa
fa | (0,1) | bbb
(2 rows)
@@ -6977,11 +6978,13 @@ DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
(6 rows)
DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+ERROR: foreign server deleted 2 rows when only one row was expected to be deleted
SELECT tableoid::regclass, ctid, * FROM fa;
- tableoid | ctid | aa
-----------+------+----
+ tableoid | ctid | aa
+----------+-------+-----
+ fa | (0,1) | aaa
fa | (0,1) | bbb
-(1 rows)
+(2 rows)
DROP FOREIGN TABLE fa;
DROP TABLE a CASCADE;
@@ -8407,10 +8410,11 @@ UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
(5 rows)
UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+ERROR: foreign server updated 2 rows when only one row was expected to be updated
SELECT tableoid::regclass, ctid, * FROM fplt;
- tableoid | ctid | a | b
-----------+-------+---+----
- fplt | (0,2) | 1 | 10
+ tableoid | ctid | a | b
+----------+-------+---+---
+ fplt | (0,1) | 1 | 1
fplt | (0,1) | 2 | 2
(2 rows)
@@ -8430,11 +8434,13 @@ DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
(6 rows)
DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+ERROR: foreign server deleted 2 rows when only one row was expected to be deleted
SELECT tableoid::regclass, ctid, * FROM fplt;
- tableoid | ctid | a | b
-----------+-------+---+----
- fplt | (0,1) | 2 | 2
-(1 row)
+ tableoid | ctid | a | b
+----------+-------+---+---
+ fplt | (0,1) | 1 | 1
+ fplt | (0,1) | 2 | 2
+(2 rows)
DROP TABLE plt;
DROP FOREIGN TABLE fplt;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 30e5726..469c7dd 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -1810,7 +1810,8 @@ postgresExecForeignUpdate(EState *estate,
bool isNull;
const char **p_values;
PGresult *res;
- int n_rows;
+ int n_rows_returned;
+ int n_rows_updated;
/* Set up the prepared statement on the remote server, if we didn't yet */
if (!fmstate->p_name)
@@ -1853,22 +1854,35 @@ postgresExecForeignUpdate(EState *estate,
pgfdw_report_error(ERROR, res, fmstate->conn, true, fmstate->query);
/* Check number of rows affected, and fetch RETURNING tuple if any */
+ n_rows_updated = atoi(PQcmdTuples(res));
if (fmstate->has_returning)
{
- n_rows = PQntuples(res);
- if (n_rows > 0)
+ n_rows_returned = PQntuples(res);
+ if (n_rows_returned > 0)
store_returning_result(fmstate, slot, res);
}
else
- n_rows = atoi(PQcmdTuples(res));
+ n_rows_returned = 0;
/* And clean up */
PQclear(res);
MemoryContextReset(fmstate->temp_cxt);
+ /* No rows should be returned if no rows were updated. */
+ if (n_rows_updated == 0 && n_rows_returned != 0)
+ elog(ERROR, "foreign server returned %d rows when no row was updated",
+ n_rows_returned);
+
+ /* ERROR if more than one row was updated on the remote end */
+ if (n_rows_updated > 1)
+ ereport(ERROR,
+ (errcode (ERRCODE_FDW_ERROR), /* XXX */
+ errmsg ("foreign server updated %d rows when only one row was expected to be updated",
+ n_rows_updated)));
+
/* Return NULL if nothing was updated on the remote end */
- return (n_rows > 0) ? slot : NULL;
+ return (n_rows_updated > 0) ? slot : NULL;
}
/*
@@ -1886,7 +1900,8 @@ postgresExecForeignDelete(EState *estate,
bool isNull;
const char **p_values;
PGresult *res;
- int n_rows;
+ int n_rows_returned;
+ int n_rows_deleted;
/* Set up the prepared statement on the remote server, if we didn't yet */
if (!fmstate->p_name)
@@ -1929,22 +1944,35 @@ postgresExecForeignDelete(EState *estate,
pgfdw_report_error(ERROR, res, fmstate->conn, true, fmstate->query);
/* Check number of rows affected, and fetch RETURNING tuple if any */
+ n_rows_deleted = atoi(PQcmdTuples(res));
if (fmstate->has_returning)
{
- n_rows = PQntuples(res);
- if (n_rows > 0)
+ n_rows_returned = PQntuples(res);
+ if (n_rows_returned > 0)
store_returning_result(fmstate, slot, res);
}
else
- n_rows = atoi(PQcmdTuples(res));
+ n_rows_returned = 0;
/* And clean up */
PQclear(res);
MemoryContextReset(fmstate->temp_cxt);
+ /* No rows should be returned if no rows were deleted. */
+ if (n_rows_deleted == 0 && n_rows_returned != 0)
+ elog(ERROR, "foreign server returned %d rows when no row was deleted",
+ n_rows_returned);
+
+ /* ERROR if more than one row was updated on the remote end */
+ if (n_rows_deleted > 1)
+ ereport(ERROR,
+ (errcode (ERRCODE_FDW_ERROR), /* XXX */
+ errmsg ("foreign server deleted %d rows when only one row was expected to be deleted",
+ n_rows_deleted)));
+
/* Return NULL if nothing was deleted on the remote end */
- return (n_rows > 0) ? slot : NULL;
+ return (n_rows_deleted > 0) ? slot : NULL;
}
/*
--
1.7.9.5
From 55773bc58315bc920192151bdf0b6e9a87fe31bf Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.ba...@enterprisedb.com>
Date: Wed, 18 Apr 2018 10:33:59 +0530
Subject: [PATCH 3/3] DML on foreign table pointing to an inherited or a
partitioned table may affect multiple rows on the
foreign server.
When a foreign table points to a partitioned table or an inheritance
parent on the foreign server, a non-direct DML can affect multiple
rows when only one row is intended to be affected. This
happens because postgres_fdw uses only ctid to identify a row to work
on. Though ctid uniquely identifies a row in a single table, in a
partitioned table or in an inheritance hierarchy, there can be be
multiple rows, in different partitions, with the same ctid. So a
DML statement sent to the foreign server by postgres_fdw ends up
affecting more than one row, only one of which is intended to be
affected.
(ctid, tableoid) is unique across a partitioning or inheritance
hierarchy. Thus instead of using just ctid, we can use qualification
based on both ctid and tableoid. When tableoid is requested from a
foreign table, foreign table's local tableoid is returned instead of
the tableoid of the table pointed by the foreign table. But for
DELETE/UPDATE qualification we need tableoid fetched from the foreign
server. The commit adds code to add tableoid as a resjunk column in
the targetlist and fetch tableoid from the foreign server.
Ashutosh Bapat, reviewed by Kyotaro Horiguchi
---
contrib/postgres_fdw/deparse.c | 108 ++++++++++---
contrib/postgres_fdw/expected/postgres_fdw.out | 198 ++++++++++++------------
contrib/postgres_fdw/postgres_fdw.c | 128 ++++++++++++---
src/backend/executor/nodeForeignscan.c | 5 +-
4 files changed, 294 insertions(+), 145 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 6e2fa14..20c30b1 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -103,6 +103,9 @@ typedef struct deparse_expr_cxt
* a base relation. */
StringInfo buf; /* output buffer to append to */
List **params_list; /* exprs that will become remote Params */
+ bool fetch_foreign_tableoid; /* fetch tableoid from the foreign
+ * server instead of using local
+ * tableoid. */
} deparse_expr_cxt;
#define REL_ALIAS_PREFIX "r"
@@ -131,7 +134,8 @@ static void deparseTargetList(StringInfo buf,
bool is_returning,
Bitmapset *attrs_used,
bool qualify_col,
- List **retrieved_attrs);
+ List **retrieved_attrs,
+ bool fetch_foreign_tableoid);
static void deparseExplicitTargetList(List *tlist,
bool is_returning,
List **retrieved_attrs,
@@ -143,7 +147,8 @@ static void deparseReturningList(StringInfo buf, PlannerInfo *root,
List *returningList,
List **retrieved_attrs);
static void deparseColumnRef(StringInfo buf, int varno, int varattno,
- PlannerInfo *root, bool qualify_col);
+ PlannerInfo *root, bool qualify_col,
+ bool fetch_foreign_tableoid);
static void deparseRelation(StringInfo buf, Relation rel);
static void deparseExpr(Expr *expr, deparse_expr_cxt *context);
static void deparseVar(Var *node, deparse_expr_cxt *context);
@@ -936,6 +941,7 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
deparse_expr_cxt context;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
List *quals;
+ Query *parse = root->parse;
/*
* We handle relations for foreign tables, joins between those and upper
@@ -951,7 +957,16 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
context.params_list = params_list;
/* Construct SELECT clause */
+ /*
+ * If this scan is being performed on UPDATE/DELETE target, we want to
+ * fetch tableoid in the targetlist from the foreign server.
+ */
+ if ((parse->commandType == CMD_UPDATE ||
+ parse->commandType == CMD_DELETE) &&
+ bms_is_member(parse->resultRelation, context.scanrel->relids))
+ context.fetch_foreign_tableoid = true;
deparseSelectSql(tlist, is_subquery, retrieved_attrs, &context);
+ context.fetch_foreign_tableoid = false;
/*
* For upper relations, the WHERE clause is built from the remote
@@ -1043,15 +1058,31 @@ deparseSelectSql(List *tlist, bool is_subquery, List **retrieved_attrs,
* required to be fetched from the foreign server.
*/
RangeTblEntry *rte = planner_rt_fetch(foreignrel->relid, root);
+ Query *parse = root->parse;
/*
* Core code already has some lock on each rel being planned, so we
* can use NoLock here.
*/
Relation rel = heap_open(rte->relid, NoLock);
+ bool fetch_foreign_tableoid = false;
+
+ /*
+ * Fetch tableoid from the foreign server when scanning for DELETE or
+ * UPDATE statement on a foreign table.
+ */
+ if ((parse->commandType == CMD_DELETE ||
+ parse->commandType == CMD_UPDATE) &&
+ parse->resultRelation == foreignrel->relid)
+ {
+ fetch_foreign_tableoid = true;
+ Assert(bms_is_member(TableOidAttributeNumber - FirstLowInvalidHeapAttributeNumber,
+ fpinfo->attrs_used));
+ }
deparseTargetList(buf, root, foreignrel->relid, rel, false,
- fpinfo->attrs_used, false, retrieved_attrs);
+ fpinfo->attrs_used, false, retrieved_attrs,
+ fetch_foreign_tableoid);
heap_close(rel, NoLock);
}
}
@@ -1105,7 +1136,8 @@ deparseTargetList(StringInfo buf,
bool is_returning,
Bitmapset *attrs_used,
bool qualify_col,
- List **retrieved_attrs)
+ List **retrieved_attrs,
+ bool fetch_foreign_tableoid)
{
TupleDesc tupdesc = RelationGetDescr(rel);
bool have_wholerow;
@@ -1137,15 +1169,17 @@ deparseTargetList(StringInfo buf,
appendStringInfoString(buf, " RETURNING ");
first = false;
- deparseColumnRef(buf, rtindex, i, root, qualify_col);
+ deparseColumnRef(buf, rtindex, i, root, qualify_col, false);
*retrieved_attrs = lappend_int(*retrieved_attrs, i);
}
}
/*
- * Add ctid and oid if needed. We currently don't support retrieving any
- * other system columns.
+ * Add ctid, tableoid and oid if needed. We currently don't support retrieving any
+ * other system columns. Non-direct DMLs require tableoid from the foreign
+ * server to identify a row correctly. In all other cases, tableoid is the
+ * local OID of the foreign table.
*/
if (bms_is_member(SelfItemPointerAttributeNumber - FirstLowInvalidHeapAttributeNumber,
attrs_used))
@@ -1179,6 +1213,26 @@ deparseTargetList(StringInfo buf,
*retrieved_attrs = lappend_int(*retrieved_attrs,
ObjectIdAttributeNumber);
}
+ if (bms_is_member(TableOidAttributeNumber - FirstLowInvalidHeapAttributeNumber,
+ attrs_used) && fetch_foreign_tableoid)
+ {
+ /*
+ * RETURNING targetlist should never request tableoid at the foreign
+ * server.
+ */
+ Assert(!fetch_foreign_tableoid || !is_returning);
+
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ if (qualify_col)
+ ADD_REL_QUALIFIER(buf, rtindex);
+ appendStringInfoString(buf, "tableoid");
+
+ *retrieved_attrs = lappend_int(*retrieved_attrs,
+ TableOidAttributeNumber);
+ }
/* Don't generate bad syntax if no undropped columns */
if (first && !is_returning)
@@ -1540,6 +1594,7 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
context.scanrel = foreignrel;
context.root = root;
context.params_list = params_list;
+ context.fetch_foreign_tableoid = false;
appendStringInfoChar(buf, '(');
appendConditions(fpinfo->joinclauses, &context);
@@ -1674,7 +1729,7 @@ deparseInsertSql(StringInfo buf, PlannerInfo *root,
appendStringInfoString(buf, ", ");
first = false;
- deparseColumnRef(buf, rtindex, attnum, root, false);
+ deparseColumnRef(buf, rtindex, attnum, root, false, false);
}
appendStringInfoString(buf, ") VALUES (");
@@ -1725,7 +1780,7 @@ deparseUpdateSql(StringInfo buf, PlannerInfo *root,
deparseRelation(buf, rel);
appendStringInfoString(buf, " SET ");
- pindex = 2; /* ctid is always the first param */
+ pindex = 3; /* ctid, tableoid params appear first */
first = true;
foreach(lc, targetAttrs)
{
@@ -1735,11 +1790,11 @@ deparseUpdateSql(StringInfo buf, PlannerInfo *root,
appendStringInfoString(buf, ", ");
first = false;
- deparseColumnRef(buf, rtindex, attnum, root, false);
+ deparseColumnRef(buf, rtindex, attnum, root, false, false);
appendStringInfo(buf, " = $%d", pindex);
pindex++;
}
- appendStringInfoString(buf, " WHERE ctid = $1");
+ appendStringInfoString(buf, " WHERE ctid = $1 AND tableoid = $2");
deparseReturningList(buf, root, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_update_after_row,
@@ -1784,6 +1839,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
context.scanrel = foreignrel;
context.buf = buf;
context.params_list = params_list;
+ context.fetch_foreign_tableoid = false;
appendStringInfoString(buf, "UPDATE ");
deparseRelation(buf, rel);
@@ -1808,7 +1864,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
appendStringInfoString(buf, ", ");
first = false;
- deparseColumnRef(buf, rtindex, attnum, root, false);
+ deparseColumnRef(buf, rtindex, attnum, root, false, false);
appendStringInfoString(buf, " = ");
deparseExpr((Expr *) tle->expr, &context);
}
@@ -1854,7 +1910,7 @@ deparseDeleteSql(StringInfo buf, PlannerInfo *root,
{
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
- appendStringInfoString(buf, " WHERE ctid = $1");
+ appendStringInfoString(buf, " WHERE ctid = $1 AND tableoid = $2");
deparseReturningList(buf, root, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_delete_after_row,
@@ -1892,6 +1948,7 @@ deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
context.scanrel = foreignrel;
context.buf = buf;
context.params_list = params_list;
+ context.fetch_foreign_tableoid = false;
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
@@ -1953,7 +2010,7 @@ deparseReturningList(StringInfo buf, PlannerInfo *root,
if (attrs_used != NULL)
deparseTargetList(buf, root, rtindex, rel, true, attrs_used, false,
- retrieved_attrs);
+ retrieved_attrs, false);
else
*retrieved_attrs = NIL;
}
@@ -2046,14 +2103,21 @@ deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
* If it has a column_name FDW option, use that instead of attribute name.
*
* If qualify_col is true, qualify column name with the alias of relation.
+ *
+ * If fetch_foreign_tableoid is true, get the tableoid,if requested, from the
+ * foreign server.
*/
static void
deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root,
- bool qualify_col)
+ bool qualify_col, bool fetch_foreign_tableoid)
{
RangeTblEntry *rte;
- /* We support fetching the remote side's CTID and OID. */
+ /*
+ * We support fetching the remote side's CTID, OID and TABLEOID. The last
+ * one is fetched from remote side for UPDATEs and DELETEs on the foreign
+ * table represented by the given varno.
+ */
if (varattno == SelfItemPointerAttributeNumber)
{
if (qualify_col)
@@ -2066,6 +2130,13 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root,
ADD_REL_QUALIFIER(buf, varno);
appendStringInfoString(buf, "oid");
}
+ else if (varattno == TableOidAttributeNumber && fetch_foreign_tableoid &&
+ varno == root->parse->resultRelation)
+ {
+ if (qualify_col)
+ ADD_REL_QUALIFIER(buf, varno);
+ appendStringInfoString(buf, "tableoid");
+ }
else if (varattno < 0)
{
/*
@@ -2135,7 +2206,7 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root,
appendStringInfoString(buf, "ROW(");
deparseTargetList(buf, root, varno, rel, false, attrs_used, qualify_col,
- &retrieved_attrs);
+ &retrieved_attrs, false);
appendStringInfoChar(buf, ')');
/* Complete the CASE WHEN statement started above. */
@@ -2354,7 +2425,8 @@ deparseVar(Var *node, deparse_expr_cxt *context)
if (bms_is_member(node->varno, relids) && node->varlevelsup == 0)
deparseColumnRef(context->buf, node->varno, node->varattno,
- context->root, qualify_col);
+ context->root, qualify_col,
+ context->fetch_foreign_tableoid);
else
{
/* Treat like a Param */
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 77d24ea..3a7d8b7 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -5494,15 +5494,15 @@ INSERT INTO ft2 (c1,c2,c3)
SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(2001, 2010) id;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: UPDATE "S 1"."T 1" SET c3 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
-> Foreign Scan on public.ft2
- Output: c1, c2, NULL::integer, 'bar'::text, c4, c5, c6, c7, c8, ctid
+ Output: c1, c2, NULL::integer, 'bar'::text, c4, c5, c6, c7, c8, ctid, tableoid
Filter: (postgres_fdw_abs(ft2.c1) > 2000)
- Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE
+ Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid, tableoid FROM "S 1"."T 1" FOR UPDATE
(7 rows)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *;
@@ -5529,13 +5529,13 @@ UPDATE ft2 SET c3 = 'baz'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
- Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: UPDATE "S 1"."T 1" SET c3 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
-> Nested Loop
- Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
+ Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft2.tableoid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Join Filter: (ft2.c2 === ft4.c1)
-> Foreign Scan on public.ft2
- Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid
- Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
+ Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft2.tableoid
+ Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan
Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3
Relations: (public.ft4) INNER JOIN (public.ft5)
@@ -5567,24 +5567,24 @@ DELETE FROM ft2
USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 = ft4.c1
RETURNING ft2.c1, ft2.c2, ft2.c3; -- can't be pushed down
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3
- Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 RETURNING "C 1", c2, c3
+ Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1 AND tableoid = $2 RETURNING "C 1", c2, c3
-> Foreign Scan
- Output: ft2.ctid, ft4.*, ft5.*
+ Output: ft2.ctid, ft2.tableoid, ft4.*, ft5.*
Filter: (ft4.c1 === ft5.c1)
Relations: ((public.ft2) INNER JOIN (public.ft4)) INNER JOIN (public.ft5)
- Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r2.c1, r3.c1 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 2000)))) INNER JOIN "S 1"."T 4" r3 ON (TRUE)) FOR UPDATE OF r1
+ Remote SQL: SELECT r1.ctid, r1.tableoid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r2.c1, r3.c1 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 2000)))) INNER JOIN "S 1"."T 4" r3 ON (TRUE)) FOR UPDATE OF r1
-> Nested Loop
- Output: ft2.ctid, ft4.*, ft5.*, ft4.c1, ft5.c1
+ Output: ft2.ctid, ft2.tableoid, ft4.*, ft5.*, ft4.c1, ft5.c1
-> Nested Loop
- Output: ft2.ctid, ft4.*, ft4.c1
+ Output: ft2.ctid, ft2.tableoid, ft4.*, ft4.c1
Join Filter: (ft2.c2 = ft4.c1)
-> Foreign Scan on public.ft2
- Output: ft2.ctid, ft2.c2
- Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
+ Output: ft2.ctid, ft2.tableoid, ft2.c2
+ Remote SQL: SELECT c2, ctid, tableoid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan on public.ft4
Output: ft4.*, ft4.c1
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
@@ -6198,25 +6198,25 @@ ERROR: new row violates check option for view "rw_view"
DETAIL: Failing row contains (10, 0).
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down
- QUERY PLAN
---------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
Update on public.foreign_tbl
- Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1
+ Remote SQL: UPDATE public.base_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.foreign_tbl
- Output: foreign_tbl.a, 20, foreign_tbl.ctid
- Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE
+ Output: foreign_tbl.a, 20, foreign_tbl.ctid, foreign_tbl.tableoid
+ Remote SQL: SELECT a, ctid, tableoid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE
(5 rows)
UPDATE rw_view SET b = 20 WHERE a = 0; -- ok
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down
- QUERY PLAN
---------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
Update on public.foreign_tbl
- Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1
+ Remote SQL: UPDATE public.base_tbl SET b = $3 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.foreign_tbl
- Output: foreign_tbl.a, '-20'::integer, foreign_tbl.ctid
- Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE
+ Output: foreign_tbl.a, '-20'::integer, foreign_tbl.ctid, foreign_tbl.tableoid
+ Remote SQL: SELECT a, ctid, tableoid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE
(5 rows)
UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail
@@ -6686,13 +6686,13 @@ BEFORE UPDATE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can't be pushed down
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Update on public.rem1
- Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1
+ Remote SQL: UPDATE public.loc1 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.rem1
- Output: f1, ''::text, ctid, rem1.*
- Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+ Output: f1, ''::text, ctid, tableoid, rem1.*
+ Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE
(5 rows)
EXPLAIN (verbose, costs off)
@@ -6710,13 +6710,13 @@ AFTER UPDATE ON rem1
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
EXPLAIN (verbose, costs off)
UPDATE rem1 set f2 = ''; -- can't be pushed down
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
Update on public.rem1
- Remote SQL: UPDATE public.loc1 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2
+ Remote SQL: UPDATE public.loc1 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2
-> Foreign Scan on public.rem1
- Output: f1, ''::text, ctid, rem1.*
- Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+ Output: f1, ''::text, ctid, tableoid, rem1.*
+ Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE
(5 rows)
EXPLAIN (verbose, costs off)
@@ -6744,13 +6744,13 @@ UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can't be pushed down
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Delete on public.rem1
- Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1
+ Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.rem1
- Output: ctid, rem1.*
- Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+ Output: ctid, tableoid, rem1.*
+ Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE
(5 rows)
DROP TRIGGER trig_row_before_delete ON rem1;
@@ -6768,13 +6768,13 @@ UPDATE rem1 set f2 = ''; -- can be pushed down
EXPLAIN (verbose, costs off)
DELETE FROM rem1; -- can't be pushed down
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------
Delete on public.rem1
- Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 RETURNING f1, f2
+ Remote SQL: DELETE FROM public.loc1 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2
-> Foreign Scan on public.rem1
- Output: ctid, rem1.*
- Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+ Output: ctid, tableoid, rem1.*
+ Remote SQL: SELECT f1, f2, ctid, tableoid FROM public.loc1 FOR UPDATE
(5 rows)
DROP TRIGGER trig_row_after_delete ON rem1;
@@ -6943,21 +6943,20 @@ SELECT tableoid::regclass, ctid, * FROM fa;
-- server
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
- QUERY PLAN
------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
Update on public.fa
- Remote SQL: UPDATE public.a SET aa = $2 WHERE ctid = $1
+ Remote SQL: UPDATE public.a SET aa = $3 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.fa
- Output: CASE WHEN (random() <= '1'::double precision) THEN 'zzzz'::text ELSE NULL::text END, ctid
- Remote SQL: SELECT ctid FROM public.a WHERE ((aa = 'aaa'::text)) FOR UPDATE
+ Output: CASE WHEN (random() <= '1'::double precision) THEN 'zzzz'::text ELSE NULL::text END, ctid, tableoid
+ Remote SQL: SELECT ctid, tableoid FROM public.a WHERE ((aa = 'aaa'::text)) FOR UPDATE
(5 rows)
UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
-ERROR: foreign server updated 2 rows when only one row was expected to be updated
SELECT tableoid::regclass, ctid, * FROM fa;
- tableoid | ctid | aa
-----------+-------+-----
- fa | (0,1) | aaa
+ tableoid | ctid | aa
+----------+-------+------
+ fa | (0,2) | zzzz
fa | (0,1) | bbb
(2 rows)
@@ -6970,21 +6969,19 @@ DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Delete on public.fa
- Remote SQL: DELETE FROM public.a WHERE ctid = $1
+ Remote SQL: DELETE FROM public.a WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.fa
- Output: ctid
+ Output: ctid, tableoid
Filter: (fa.aa = CASE WHEN (random() <= '1'::double precision) THEN 'aaa'::text ELSE 'bbb'::text END)
- Remote SQL: SELECT aa, ctid FROM public.a FOR UPDATE
+ Remote SQL: SELECT aa, ctid, tableoid FROM public.a FOR UPDATE
(6 rows)
DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
-ERROR: foreign server deleted 2 rows when only one row was expected to be deleted
SELECT tableoid::regclass, ctid, * FROM fa;
tableoid | ctid | aa
----------+-------+-----
- fa | (0,1) | aaa
fa | (0,1) | bbb
-(2 rows)
+(1 row)
DROP FOREIGN TABLE fa;
DROP TABLE a CASCADE;
@@ -7091,12 +7088,12 @@ select * from bar where f1 in (select f1 from foo) for share;
-- Check UPDATE with inherited target and an inherited source table
explain (verbose, costs off)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
- QUERY PLAN
----------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
- Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
+ Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2
-> Hash Join
Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.*, foo.tableoid
Inner Unique: true
@@ -7115,12 +7112,12 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, bar2.tableoid, foo.ctid, foo.*, foo.tableoid
Inner Unique: true
Hash Cond: (bar2.f1 = foo.f1)
-> Foreign Scan on public.bar2
- Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
- Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid, bar2.tableoid
+ Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE
-> Hash
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
-> HashAggregate
@@ -7152,12 +7149,12 @@ update bar set f2 = f2 + 100
from
( select f1 from foo union all select f1+3 from foo ) ss
where bar.f1 = ss.f1;
- QUERY PLAN
---------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
- Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
+ Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2
-> Hash Join
Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1))
Hash Cond: (foo.f1 = bar.f1)
@@ -7177,14 +7174,14 @@ where bar.f1 = ss.f1;
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Merge Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, bar2.tableoid, (ROW(foo.f1))
Merge Cond: (bar2.f1 = foo.f1)
-> Sort
- Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid, bar2.tableoid
Sort Key: bar2.f1
-> Foreign Scan on public.bar2
- Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
- Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid, bar2.tableoid
+ Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE
-> Sort
Output: (ROW(foo.f1)), foo.f1
Sort Key: foo.f1
@@ -7382,17 +7379,17 @@ AFTER UPDATE OR DELETE ON bar2
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
explain (verbose, costs off)
update bar set f2 = f2 + 100;
- QUERY PLAN
---------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
- Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2, f3
+ Remote SQL: UPDATE public.loct2 SET f2 = $3 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2, f3
-> Seq Scan on public.bar
Output: bar.f1, (bar.f2 + 100), bar.ctid
-> Foreign Scan on public.bar2
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, bar2.*
- Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, bar2.tableoid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 FOR UPDATE
(9 rows)
update bar set f2 = f2 + 100;
@@ -7410,18 +7407,18 @@ NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2
NOTICE: OLD: (7,277,77),NEW: (7,377,77)
explain (verbose, costs off)
delete from bar where f2 < 400;
- QUERY PLAN
----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
Delete on public.bar
Delete on public.bar
Foreign Delete on public.bar2
- Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3
+ Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 AND tableoid = $2 RETURNING f1, f2, f3
-> Seq Scan on public.bar
Output: bar.ctid
Filter: (bar.f2 < 400)
-> Foreign Scan on public.bar2
- Output: bar2.ctid, bar2.*
- Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE
+ Output: bar2.ctid, bar2.tableoid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid, tableoid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE
(10 rows)
delete from bar where f2 < 400;
@@ -8400,22 +8397,21 @@ SELECT tableoid::regclass, ctid, * FROM fplt;
-- server
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------
Update on public.fplt
- Remote SQL: UPDATE public.plt SET b = $2 WHERE ctid = $1
+ Remote SQL: UPDATE public.plt SET b = $3 WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.fplt
- Output: a, CASE WHEN (random() <= '1'::double precision) THEN 10 ELSE 20 END, ctid
- Remote SQL: SELECT a, ctid FROM public.plt WHERE ((a = 1)) FOR UPDATE
+ Output: a, CASE WHEN (random() <= '1'::double precision) THEN 10 ELSE 20 END, ctid, tableoid
+ Remote SQL: SELECT a, ctid, tableoid FROM public.plt WHERE ((a = 1)) FOR UPDATE
(5 rows)
UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
-ERROR: foreign server updated 2 rows when only one row was expected to be updated
SELECT tableoid::regclass, ctid, * FROM fplt;
- tableoid | ctid | a | b
-----------+-------+---+---
- fplt | (0,1) | 1 | 1
- fplt | (0,1) | 2 | 2
+ tableoid | ctid | a | b
+----------+-------+---+----
+ fplt | (0,2) | 1 | 10
+ fplt | (0,1) | 2 | 2
(2 rows)
-- repopulate partitioned table so that we have rows with same ctid
@@ -8426,21 +8422,19 @@ DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
QUERY PLAN
---------------------------------------------------------------------------------------------
Delete on public.fplt
- Remote SQL: DELETE FROM public.plt WHERE ctid = $1
+ Remote SQL: DELETE FROM public.plt WHERE ctid = $1 AND tableoid = $2
-> Foreign Scan on public.fplt
- Output: ctid
+ Output: ctid, tableoid
Filter: (fplt.a = CASE WHEN (random() <= '1'::double precision) THEN 1 ELSE 10 END)
- Remote SQL: SELECT a, ctid FROM public.plt FOR UPDATE
+ Remote SQL: SELECT a, ctid, tableoid FROM public.plt FOR UPDATE
(6 rows)
DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
-ERROR: foreign server deleted 2 rows when only one row was expected to be deleted
SELECT tableoid::regclass, ctid, * FROM fplt;
tableoid | ctid | a | b
----------+-------+---+---
- fplt | (0,1) | 1 | 1
fplt | (0,1) | 2 | 2
-(2 rows)
+(1 row)
DROP TABLE plt;
DROP FOREIGN TABLE fplt;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 469c7dd..2271a76 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -178,6 +178,7 @@ typedef struct PgFdwModifyState
/* info about parameters for prepared statement */
AttrNumber ctidAttno; /* attnum of input resjunk ctid column */
+ AttrNumber tableoidAttno; /* attnum of input resjunk tableoid column */
int p_nums; /* number of parameters to transmit */
FmgrInfo *p_flinfo; /* output conversion functions for them */
@@ -390,7 +391,7 @@ static PgFdwModifyState *create_foreign_modify(EState *estate,
List *retrieved_attrs);
static void prepare_foreign_modify(PgFdwModifyState *fmstate);
static const char **convert_prep_stmt_params(PgFdwModifyState *fmstate,
- ItemPointer tupleid,
+ ItemPointer tupleid, Oid tableoid,
TupleTableSlot *slot);
static void store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res);
@@ -1543,26 +1544,39 @@ postgresAddForeignUpdateTargets(Query *parsetree,
TargetEntry *tle;
/*
- * In postgres_fdw, what we need is the ctid, same as for a regular table.
+ * ctid is used to locate a row in a given table and tableoid is used to
+ * identify a table in a partition or inheritance hierarchy.
*/
- /* Make a Var representing the desired value */
+ /*
+ * Make a Var representing the ctid, wrap it in a resjunk TLE with the
+ * right name and add it to the query's targetlist.
+ */
var = makeVar(parsetree->resultRelation,
SelfItemPointerAttributeNumber,
TIDOID,
-1,
InvalidOid,
0);
-
- /* Wrap it in a resjunk TLE with the right name ... */
attrname = "ctid";
-
tle = makeTargetEntry((Expr *) var,
list_length(parsetree->targetList) + 1,
pstrdup(attrname),
true);
+ parsetree->targetList = lappend(parsetree->targetList, tle);
- /* ... and add it to the query's targetlist */
+ /* Do the same for tableoid */
+ var = makeVar(parsetree->resultRelation,
+ TableOidAttributeNumber,
+ OIDOID,
+ -1,
+ InvalidOid,
+ 0);
+ attrname = "tableoid";
+ tle = makeTargetEntry((Expr *) var,
+ list_length(parsetree->targetList) + 1,
+ pstrdup(attrname),
+ true);
parsetree->targetList = lappend(parsetree->targetList, tle);
}
@@ -1751,7 +1765,7 @@ postgresExecForeignInsert(EState *estate,
prepare_foreign_modify(fmstate);
/* Convert parameters needed by prepared statement to text form */
- p_values = convert_prep_stmt_params(fmstate, NULL, slot);
+ p_values = convert_prep_stmt_params(fmstate, NULL, InvalidOid, slot);
/*
* Execute the prepared statement.
@@ -1806,7 +1820,8 @@ postgresExecForeignUpdate(EState *estate,
TupleTableSlot *planSlot)
{
PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
- Datum datum;
+ Datum ctid_datum;
+ Datum tableoid_datum;
bool isNull;
const char **p_values;
PGresult *res;
@@ -1818,16 +1833,29 @@ postgresExecForeignUpdate(EState *estate,
prepare_foreign_modify(fmstate);
/* Get the ctid that was passed up as a resjunk column */
- datum = ExecGetJunkAttribute(planSlot,
- fmstate->ctidAttno,
- &isNull);
+ ctid_datum = ExecGetJunkAttribute(planSlot,
+ fmstate->ctidAttno,
+ &isNull);
/* shouldn't ever get a null result... */
if (isNull)
elog(ERROR, "ctid is NULL");
+ /* Get the tableoid that was passed up as a resjunk column */
+ tableoid_datum = ExecGetJunkAttribute(planSlot,
+ fmstate->tableoidAttno,
+ &isNull);
+ /* shouldn't ever get a null result... */
+ if (isNull)
+ elog(ERROR, "tableoid is NULL");
+
+ /* ... and should be always a valid */
+ if (!OidIsValid(DatumGetObjectId(tableoid_datum)))
+ elog(ERROR, "tableoid is invalid");
+
/* Convert parameters needed by prepared statement to text form */
p_values = convert_prep_stmt_params(fmstate,
- (ItemPointer) DatumGetPointer(datum),
+ (ItemPointer) DatumGetPointer(ctid_datum),
+ DatumGetObjectId(tableoid_datum),
slot);
/*
@@ -1896,7 +1924,8 @@ postgresExecForeignDelete(EState *estate,
TupleTableSlot *planSlot)
{
PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
- Datum datum;
+ Datum ctid_datum;
+ Datum tableoid_datum;
bool isNull;
const char **p_values;
PGresult *res;
@@ -1908,16 +1937,29 @@ postgresExecForeignDelete(EState *estate,
prepare_foreign_modify(fmstate);
/* Get the ctid that was passed up as a resjunk column */
- datum = ExecGetJunkAttribute(planSlot,
- fmstate->ctidAttno,
- &isNull);
+ ctid_datum = ExecGetJunkAttribute(planSlot,
+ fmstate->ctidAttno,
+ &isNull);
/* shouldn't ever get a null result... */
if (isNull)
elog(ERROR, "ctid is NULL");
+ /* Get the tableoid that was passed up as a resjunk column */
+ tableoid_datum = ExecGetJunkAttribute(planSlot,
+ fmstate->tableoidAttno,
+ &isNull);
+ /* shouldn't ever get a null result... */
+ if (isNull)
+ elog(ERROR, "tableoid is NULL");
+
+ /* ... and should be always a valid */
+ if (!OidIsValid(DatumGetObjectId(tableoid_datum)))
+ elog(ERROR, "tableoid is invalid");
+
/* Convert parameters needed by prepared statement to text form */
p_values = convert_prep_stmt_params(fmstate,
- (ItemPointer) DatumGetPointer(datum),
+ (ItemPointer) DatumGetPointer(ctid_datum),
+ DatumGetObjectId(tableoid_datum),
NULL);
/*
@@ -3338,7 +3380,7 @@ create_foreign_modify(EState *estate,
fmstate->attinmeta = TupleDescGetAttInMetadata(tupdesc);
/* Prepare for output conversion of parameters used in prepared stmt. */
- n_params = list_length(fmstate->target_attrs) + 1;
+ n_params = list_length(fmstate->target_attrs) + 2;
fmstate->p_flinfo = (FmgrInfo *) palloc0(sizeof(FmgrInfo) * n_params);
fmstate->p_nums = 0;
@@ -3346,16 +3388,30 @@ create_foreign_modify(EState *estate,
{
Assert(subplan != NULL);
- /* Find the ctid resjunk column in the subplan's result */
+ /*
+ * Find the ctid, tableoid resjunk columns in the subplan's result and
+ * record those as transmittable parameters.
+ */
+
+
+ /* First transmittable parameter will be ctid */
fmstate->ctidAttno = ExecFindJunkAttributeInTlist(subplan->targetlist,
"ctid");
if (!AttributeNumberIsValid(fmstate->ctidAttno))
elog(ERROR, "could not find junk ctid column");
-
- /* First transmittable parameter will be ctid */
getTypeOutputInfo(TIDOID, &typefnoid, &isvarlena);
fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
fmstate->p_nums++;
+
+ /* Second transmittable parameter will be tableoid */
+ fmstate->tableoidAttno =
+ ExecFindJunkAttributeInTlist(subplan->targetlist,
+ "tableoid");
+ if (!AttributeNumberIsValid(fmstate->tableoidAttno))
+ elog(ERROR, "could not find junk tableoid column");
+ getTypeOutputInfo(OIDOID, &typefnoid, &isvarlena);
+ fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
+ fmstate->p_nums++;
}
if (operation == CMD_INSERT || operation == CMD_UPDATE)
@@ -3429,13 +3485,14 @@ prepare_foreign_modify(PgFdwModifyState *fmstate)
* Create array of text strings representing parameter values
*
* tupleid is ctid to send, or NULL if none
+ * tableoid is tableoid to send or InvalidOid if none
* slot is slot to get remaining parameters from, or NULL if none
*
* Data is constructed in temp_cxt; caller should reset that after use.
*/
static const char **
convert_prep_stmt_params(PgFdwModifyState *fmstate,
- ItemPointer tupleid,
+ ItemPointer tupleid, Oid tableoid,
TupleTableSlot *slot)
{
const char **p_values;
@@ -3455,6 +3512,15 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
pindex++;
}
+ /* 2nd parameter should be tableoid, if it's in use */
+ if (OidIsValid(tableoid))
+ {
+ /* don't need set_transmission_modes for TID output */
+ p_values[pindex] = OutputFunctionCall(&fmstate->p_flinfo[pindex],
+ ObjectIdGetDatum(tableoid));
+ pindex++;
+ }
+
/* get following parameters from slot */
if (slot != NULL && fmstate->target_attrs != NIL)
{
@@ -5553,6 +5619,7 @@ make_tuple_from_result_row(PGresult *res,
bool *nulls;
ItemPointer ctid = NULL;
Oid oid = InvalidOid;
+ Oid tableoid = InvalidOid;
ConversionLocation errpos;
ErrorContextCallback errcallback;
MemoryContext oldcontext;
@@ -5646,6 +5713,18 @@ make_tuple_from_result_row(PGresult *res,
oid = DatumGetObjectId(datum);
}
}
+ else if (i == TableOidAttributeNumber)
+ {
+ /* tableoid */
+ if (valstr != NULL)
+ {
+ Datum datum;
+
+ datum = DirectFunctionCall1(oidin, CStringGetDatum(valstr));
+ tableoid = DatumGetObjectId(datum);
+ }
+ }
+
errpos.cur_attno = 0;
j++;
@@ -5695,6 +5774,9 @@ make_tuple_from_result_row(PGresult *res,
if (OidIsValid(oid))
HeapTupleSetOid(tuple, oid);
+ if (OidIsValid(tableoid))
+ tuple->t_tableOid = tableoid;
+
/* Clean up */
MemoryContextReset(temp_context);
diff --git a/src/backend/executor/nodeForeignscan.c b/src/backend/executor/nodeForeignscan.c
index a2a28b7..8ebfdfd 100644
--- a/src/backend/executor/nodeForeignscan.c
+++ b/src/backend/executor/nodeForeignscan.c
@@ -58,13 +58,14 @@ ForeignNext(ForeignScanState *node)
* If any system columns are requested, we have to force the tuple into
* physical-tuple form to avoid "cannot extract system attribute from
* virtual tuple" errors later. We also insert a valid value for
- * tableoid, which is the only actually-useful system column.
+ * tableoid, in case FDW has not set it as per its needs.
*/
if (plan->fsSystemCol && !TupIsNull(slot))
{
HeapTuple tup = ExecMaterializeSlot(slot);
- tup->t_tableOid = RelationGetRelid(node->ss.ss_currentRelation);
+ if (!OidIsValid(tup->t_tableOid))
+ tup->t_tableOid = RelationGetRelid(node->ss.ss_currentRelation);
}
return slot;
--
1.7.9.5
