Re: Problem while updating a foreign table pointing to apartitioned table on foreign server
| От | Kyotaro HORIGUCHI |
|---|---|
| Тема | Re: Problem while updating a foreign table pointing to apartitioned table on foreign server |
| Дата | |
| Msg-id | 20180824.165827.226719997.horiguchi.kyotaro@lab.ntt.co.jp обсуждение исходный текст |
| Ответ на | Re: Problem while updating a foreign table pointing to apartitioned table on foreign server (Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>) |
| Ответы |
Re: Problem while updating a foreign table pointing to a partitionedtable on foreign server
|
| Список | pgsql-hackers |
Hello.
At Tue, 21 Aug 2018 11:01:32 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> wrote in
<20180821.110132.261184472.horiguchi.kyotaro@lab.ntt.co.jp>
> > You wrote:
> > > Several places seems to be assuming that fdw_scan_tlist may be
> > > used foreign scan on simple relation but I didn't find that
> > > actually happens.
> >
> > Yeah, currently, postgres_fdw and file_fdw don't use that list for
> > simple foreign table scans, but it could be used to improve the
> > efficiency for those scans, as explained in fdwhandler.sgml:
...
> I'll put more consideration on using fdw_scan_tlist in the
> documented way.
Done. postgres_fdw now generates full fdw_scan_tlist (as
documented) for foreign relations with junk columns having a
small change in core side. However it is far less invasive than
the previous version and I believe that it dones't harm
maybe-existing use of fdw_scan_tlist on non-join rels (that is,
in the case of a subset of relation columns).
The previous patch didn't show "tableoid" in the Output list (as
"<added_junk>") of explain output but this does correctly by
referring to rte->eref->colnames. I believe no other FDW has
expanded foreign relation even if it uses fdw_scan_tlist for
ForeignScan on a base relation so it won't harm them.
One arguable behavior change is about wholrow vars. Currently it
refferes local tuple with all columns but it is explicitly
fetched as ROW() after this patch applied. This could be fixed
but not just now.
Part of 0004-:
- Output: f1, ''::text, ctid, rem1.*
- Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+ Output: f1, ''::text, tableoid, ctid, rem1.*
+ Remote SQL: SELECT f1, tableoid, ctid, ROW(f1, f2) FROM public.loc1 FOR UPDATE
Since this uses fdw_scan_tlist so it is theoretically
back-patchable back to 9.6. This patch applies on top of the
current master.
Please find the attached three files.
0001-Add-test-for-postgres_fdw-foreign-parition-update.patch
This should fail for unpatched postgres_fdw. (Just for demonstration)
0002-Core-side-modification-for-PgFDW-foreign-update-fix.patch
Core side change which allows fdw_scan_tlist to have extra
columns that is not defined in the base relation.
0003-Fix-of-foreign-update-bug-of-PgFDW.patch
Fix of postgres_fdw for this problem.
0004-Regtest-change-for-PgFDW-foreign-update-fix.patch
Regression test change separated for readability.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
From fba71c319d1008f6dc198b8585c41f7ff0a708f1 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Fri, 24 Aug 2018 15:39:14 +0900
Subject: [PATCH 1/4] Add test for postgres_fdw foreign parition update
This add a test for the failure of updating foreign partitioned table
due to lack of distinction of remote child tables. This should fail.
---
contrib/postgres_fdw/expected/postgres_fdw.out | 62 ++++++++++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 30 +++++++++++++
2 files changed, 92 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index d912bd9d54..dd4864f006 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7568,6 +7568,68 @@ drop table loct1;
drop table loct2;
drop table parent;
-- ===================================================================
+-- test update foreign partiton table
+-- ===================================================================
+CREATE TABLE p1 (a int, b int);
+CREATE TABLE c1 (LIKE p1) INHERITS (p1);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging column "b" with inherited definition
+CREATE TABLE c2 (LIKE p1) INHERITS (p1);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging column "b" with inherited definition
+CREATE FOREIGN TABLE fp1 (a int, b int)
+ SERVER loopback OPTIONS (table_name 'p1');
+INSERT INTO c1 VALUES (0, 1);
+INSERT INTO c2 VALUES (1, 1);
+SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ toiddiff | ctid | a | b
+----------+-------+---+---
+ 0 | (0,1) | 0 | 1
+ 0 | (0,1) | 1 | 1
+(2 rows)
+
+-- random() causes non-direct foreign update
+EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Update on public.fp1
+ Remote SQL: UPDATE public.p1 SET b = $2 WHERE ctid = $1
+ -> Foreign Scan on public.fp1
+ Output: a, (b + 1), ctid
+ Filter: (random() <= '1'::double precision)
+ Remote SQL: SELECT a, b, ctid FROM public.p1 WHERE ((a = 0)) FOR UPDATE
+(6 rows)
+
+UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
+-- Only one tuple should be updated
+SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ toiddiff | ctid | a | b
+----------+-------+---+---
+ 0 | (0,2) | 0 | 2
+ 0 | (0,1) | 1 | 1
+(2 rows)
+
+-- Reset ctid
+TRUNCATE c1;
+TRUNCATE c2;
+INSERT INTO c1 VALUES (0, 1);
+INSERT INTO c2 VALUES (1, 1);
+DELETE FROM fp1 WHERE a = 1 and random() <= 1;
+-- Only one tuple should be deleted
+SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+ toiddiff | ctid | a | b
+----------+-------+---+---
+ 0 | (0,1) | 0 | 1
+(1 row)
+
+-- cleanup
+DROP FOREIGN TABLE fp1;
+DROP TABLE p1 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table c1
+drop cascades to table c2
+-- ===================================================================
-- test tuple routing for foreign-table partitions
-- ===================================================================
-- Test insert tuple routing
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index c0b0dd949b..a821173a90 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1846,6 +1846,36 @@ drop table loct1;
drop table loct2;
drop table parent;
+-- ===================================================================
+-- test update foreign partiton table
+-- ===================================================================
+CREATE TABLE p1 (a int, b int);
+CREATE TABLE c1 (LIKE p1) INHERITS (p1);
+CREATE TABLE c2 (LIKE p1) INHERITS (p1);
+CREATE FOREIGN TABLE fp1 (a int, b int)
+ SERVER loopback OPTIONS (table_name 'p1');
+INSERT INTO c1 VALUES (0, 1);
+INSERT INTO c2 VALUES (1, 1);
+SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+-- random() causes non-direct foreign update
+EXPLAIN (VERBOSE, COSTS OFF)
+ UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
+UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
+-- Only one tuple should be updated
+SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+-- Reset ctid
+TRUNCATE c1;
+TRUNCATE c2;
+INSERT INTO c1 VALUES (0, 1);
+INSERT INTO c2 VALUES (1, 1);
+DELETE FROM fp1 WHERE a = 1 and random() <= 1;
+-- Only one tuple should be deleted
+SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, * FROM fp1;
+
+-- cleanup
+DROP FOREIGN TABLE fp1;
+DROP TABLE p1 CASCADE;
+
-- ===================================================================
-- test tuple routing for foreign-table partitions
-- ===================================================================
--
2.16.3
From 4e4759fbafc6c364b48cb35e8403725c56a69932 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Fri, 24 Aug 2018 13:07:08 +0900
Subject: [PATCH 2/4] Core side modification for PgFDW foreign update fix
Currently core doesn't allow add a column that is not in relation
definition to columns in fdw_scan_tlist. This patch allows that.
---
src/backend/optimizer/util/plancat.c | 72 +++++++++++++++++++++++++++++++++++-
src/backend/utils/adt/ruleutils.c | 35 +++++++++++++++---
2 files changed, 101 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 8369e3ad62..abef30dfd4 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -33,6 +33,7 @@
#include "foreign/fdwapi.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/plancat.h"
@@ -58,7 +59,15 @@ int constraint_exclusion = CONSTRAINT_EXCLUSION_PARTITION;
/* Hook for plugins to get control in get_relation_info() */
get_relation_info_hook_type get_relation_info_hook = NULL;
+/* context type for max_varattno() */
+typedef struct
+{
+ AttrNumber maxattrnum;
+ Index varno;
+} max_varattno_context;
+static bool max_varattno_walker(Node *node, max_varattno_context *context);
+static AttrNumber max_varattno(List *tlist, Index varno);
static void get_relation_foreign_keys(PlannerInfo *root, RelOptInfo *rel,
Relation relation, bool inhparent);
static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
@@ -76,6 +85,43 @@ static PartitionScheme find_partition_scheme(PlannerInfo *root, Relation rel);
static void set_baserel_partition_key_exprs(Relation relation,
RelOptInfo *rel);
+/*
+ * max_varattno
+ * Find the largest varattno in targetlist
+ *
+ * FDWs may add junk columns for internal usage. This function finds the
+ * maximum attribute number in the tlist.
+ */
+static AttrNumber
+max_varattno(List *tlist, Index varno)
+{
+ max_varattno_context context;
+
+ context.maxattrnum = FirstLowInvalidHeapAttributeNumber;
+ context.varno = varno;
+
+ max_varattno_walker((Node*) tlist, &context);
+
+ return context.maxattrnum;
+}
+
+static bool
+max_varattno_walker(Node *node, max_varattno_context *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (var->varno == context->varno && var->varlevelsup == 0 &&
+ context->maxattrnum < var->varattno)
+ context->maxattrnum = var->varattno;
+ return false;
+ }
+ return expression_tree_walker(node, max_varattno_walker, (void *)context);
+}
+
/*
* get_relation_info -
* Retrieves catalog information for a given relation.
@@ -112,6 +158,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
Relation relation;
bool hasindex;
List *indexinfos = NIL;
+ AttrNumber max_attrnum;
/*
* We need not lock the relation since it was already locked, either by
@@ -126,8 +173,18 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary or unlogged relations during recovery")));
+ max_attrnum = RelationGetNumberOfAttributes(relation);
+
+ /* Foreign table may have exanded this relation with junk columns */
+ if (root->simple_rte_array[varno]->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ AttrNumber maxattno = max_varattno(root->parse->targetList, varno);
+ if (max_attrnum < maxattno)
+ max_attrnum = maxattno;
+ }
+
rel->min_attr = FirstLowInvalidHeapAttributeNumber + 1;
- rel->max_attr = RelationGetNumberOfAttributes(relation);
+ rel->max_attr = max_attrnum;
rel->reltablespace = RelationGetForm(relation)->reltablespace;
Assert(rel->max_attr >= rel->min_attr);
@@ -1575,6 +1632,19 @@ build_physical_tlist(PlannerInfo *root, RelOptInfo *rel)
relation = heap_open(rte->relid, NoLock);
numattrs = RelationGetNumberOfAttributes(relation);
+
+ /*
+ * Foreign tables may have expanded with some junk columns. Punt
+ * in the case.
+ */
+ if (numattrs < rel->max_attr)
+ {
+ Assert(root->simple_rte_array[rel->relid]->relkind ==
+ RELKIND_FOREIGN_TABLE);
+ heap_close(relation, NoLock);
+ break;
+ }
+
for (attrno = 1; attrno <= numattrs; attrno++)
{
Form_pg_attribute att_tup = TupleDescAttr(relation->rd_att,
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 03e9a28a63..d9d525e896 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -3815,16 +3815,42 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte,
tupdesc = RelationGetDescr(rel);
ncolumns = tupdesc->natts;
+
+ /* eref may hold names of junk columns */
+ if (ncolumns < list_length(rte->eref->colnames))
+ ncolumns = list_length(rte->eref->colnames);
+
real_colnames = (char **) palloc(ncolumns * sizeof(char *));
for (i = 0; i < ncolumns; i++)
{
- Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+ if (i < tupdesc->natts)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
- if (attr->attisdropped)
- real_colnames[i] = NULL;
+ if (attr->attisdropped)
+ real_colnames[i] = NULL;
+ else
+ real_colnames[i] = pstrdup(NameStr(attr->attname));
+ }
else
- real_colnames[i] = pstrdup(NameStr(attr->attname));
+ {
+ /*
+ * This columns is an extended column, the name of which may
+ * be stored in eref
+ */
+ if (i < list_length(rte->eref->colnames))
+ {
+ char *cname = strVal(list_nth(rte->eref->colnames, i));
+
+ if (cname[0] == '\0')
+ real_colnames[i] = NULL;
+ else
+ real_colnames[i] = cname;
+ }
+ else
+ real_colnames[i] = NULL;
+ }
}
relation_close(rel, AccessShareLock);
}
@@ -4152,7 +4178,6 @@ set_join_column_names(deparse_namespace *dpns, RangeTblEntry *rte,
for (jc = 0; jc < rightcolinfo->num_new_cols; jc++)
{
char *child_colname = rightcolinfo->new_colnames[jc];
-
if (!rightcolinfo->is_new_col[jc])
{
/* Advance ic to next non-dropped old column of right child */
--
2.16.3
From 91d6ab8b8597f3df4003e3d946157124b9df1c02 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Fri, 24 Aug 2018 16:17:24 +0900
Subject: [PATCH 3/4] Fix of foreign update bug of PgFDW
Postgres_fdw wrongly behavoes in updating foreign tables on a remote
partitioned table when direct modify is not used. This is because
postgres_fdw is forgetting that two different tuples with the same
ctid may come in the case. With this patch it uses remote tableoid in
addition to ctid to distinguish a remote tuple.
---
contrib/postgres_fdw/deparse.c | 153 +++++++++++--------
contrib/postgres_fdw/postgres_fdw.c | 291 +++++++++++++++++++++++++++++++-----
2 files changed, 346 insertions(+), 98 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 6001f4d25e..6e8cd016a3 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -1037,6 +1037,15 @@ deparseSelectSql(List *tlist, bool is_subquery, List **retrieved_attrs,
*/
deparseExplicitTargetList(tlist, false, retrieved_attrs, context);
}
+ else if (tlist != NIL)
+ {
+ /*
+ * The given tlist is that of base relation's expanded with junk
+ * columns.
+ */
+ context->params_list = NULL;
+ deparseExplicitTargetList(tlist, false, retrieved_attrs, context);
+ }
else
{
/*
@@ -1088,6 +1097,42 @@ deparseFromExpr(List *quals, deparse_expr_cxt *context)
}
}
+/*
+ * Adds one element in target/returning list if it is in attrs_used.
+ *
+ * If deparsestr is given, just use it. Otherwise resolves the name using rte.
+ */
+static inline void
+deparseAddTargetListItem(StringInfo buf,
+ List **retrieved_attrs, Bitmapset *attrs_used,
+ Index rtindex, AttrNumber attnum,
+ char *deparsestr, RangeTblEntry *rte,
+ bool is_returning, bool qualify_col,
+ bool have_wholerow, bool *first)
+{
+ if (!have_wholerow &&
+ !bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber, attrs_used))
+ return;
+
+ if (!*first)
+ appendStringInfoString(buf, ", ");
+ else if (is_returning)
+ appendStringInfoString(buf, " RETURNING ");
+ *first = false;
+
+ if (deparsestr)
+ {
+ if (qualify_col)
+ ADD_REL_QUALIFIER(buf, rtindex);
+
+ appendStringInfoString(buf, deparsestr);
+ }
+ else
+ deparseColumnRef(buf, rtindex, attnum, rte, qualify_col);
+
+ *retrieved_attrs = lappend_int(*retrieved_attrs, attnum);
+}
+
/*
* Emit a target list that retrieves the columns specified in attrs_used.
* This is used for both SELECT and RETURNING targetlists; the is_returning
@@ -1128,58 +1173,28 @@ deparseTargetList(StringInfo buf,
if (attr->attisdropped)
continue;
- if (have_wholerow ||
- bms_is_member(i - FirstLowInvalidHeapAttributeNumber,
- attrs_used))
- {
- if (!first)
- appendStringInfoString(buf, ", ");
- else if (is_returning)
- appendStringInfoString(buf, " RETURNING ");
- first = false;
-
- deparseColumnRef(buf, rtindex, i, rte, qualify_col);
-
- *retrieved_attrs = lappend_int(*retrieved_attrs, i);
- }
+ deparseAddTargetListItem(buf, retrieved_attrs, attrs_used,
+ rtindex, i, NULL, rte,
+ is_returning, qualify_col, have_wholerow,
+ &first);
}
/*
- * Add ctid and oid if needed. We currently don't support retrieving any
- * other system columns.
+ * Add ctid, oid and tableoid if needed. The attribute name and number are
+ * assigned in postgresAddForeignUpdateTargets. We currently don't support
+ * retrieving any other system columns.
*/
- if (bms_is_member(SelfItemPointerAttributeNumber - FirstLowInvalidHeapAttributeNumber,
- attrs_used))
- {
- if (!first)
- appendStringInfoString(buf, ", ");
- else if (is_returning)
- appendStringInfoString(buf, " RETURNING ");
- first = false;
-
- if (qualify_col)
- ADD_REL_QUALIFIER(buf, rtindex);
- appendStringInfoString(buf, "ctid");
-
- *retrieved_attrs = lappend_int(*retrieved_attrs,
- SelfItemPointerAttributeNumber);
- }
- if (bms_is_member(ObjectIdAttributeNumber - FirstLowInvalidHeapAttributeNumber,
- attrs_used))
- {
- if (!first)
- appendStringInfoString(buf, ", ");
- else if (is_returning)
- appendStringInfoString(buf, " RETURNING ");
- first = false;
-
- if (qualify_col)
- ADD_REL_QUALIFIER(buf, rtindex);
- appendStringInfoString(buf, "oid");
-
- *retrieved_attrs = lappend_int(*retrieved_attrs,
- ObjectIdAttributeNumber);
- }
+ deparseAddTargetListItem(buf, retrieved_attrs, attrs_used,
+ rtindex, tupdesc->natts + 1, "tableoid",
+ NULL, is_returning, qualify_col, false, &first);
+
+ deparseAddTargetListItem(buf, retrieved_attrs, attrs_used,
+ rtindex, SelfItemPointerAttributeNumber, "ctid",
+ NULL, is_returning, qualify_col, false, &first);
+
+ deparseAddTargetListItem(buf, retrieved_attrs, attrs_used,
+ rtindex, ObjectIdAttributeNumber, "oid",
+ NULL, is_returning, qualify_col, false, &first);
/* Don't generate bad syntax if no undropped columns */
if (first && !is_returning)
@@ -1728,7 +1743,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
deparseRelation(buf, rel);
appendStringInfoString(buf, " SET ");
- pindex = 2; /* ctid is always the first param */
+ pindex = 3; /* tableoid and ctid always precede */
first = true;
foreach(lc, targetAttrs)
{
@@ -1742,7 +1757,7 @@ deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
appendStringInfo(buf, " = $%d", pindex);
pindex++;
}
- appendStringInfoString(buf, " WHERE ctid = $1");
+ appendStringInfoString(buf, " WHERE tableoid = $1 AND ctid = $2");
deparseReturningList(buf, rte, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_update_after_row,
@@ -1858,7 +1873,7 @@ deparseDeleteSql(StringInfo buf, RangeTblEntry *rte,
{
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
- appendStringInfoString(buf, " WHERE ctid = $1");
+ appendStringInfoString(buf, " WHERE tableoid = $1 AND ctid = $2");
deparseReturningList(buf, rte, rtindex, rel,
rel->trigdesc && rel->trigdesc->trig_delete_after_row,
@@ -2160,9 +2175,11 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
}
else
{
- char *colname = NULL;
+ char *colname = NULL;
List *options;
ListCell *lc;
+ Relation rel;
+ int natts;
/* varno must not be any of OUTER_VAR, INNER_VAR and INDEX_VAR. */
Assert(!IS_SPECIAL_VARNO(varno));
@@ -2171,16 +2188,34 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
* If it's a column of a foreign table, and it has the column_name FDW
* option, use that value.
*/
- options = GetForeignColumnOptions(rte->relid, varattno);
- foreach(lc, options)
- {
- DefElem *def = (DefElem *) lfirst(lc);
+ rel = heap_open(rte->relid, NoLock);
+ natts = RelationGetNumberOfAttributes(rel);
+ heap_close(rel, NoLock);
- if (strcmp(def->defname, "column_name") == 0)
+ if (rte->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ if (varattno > 0 && varattno <= natts)
{
- colname = defGetString(def);
- break;
+ options = GetForeignColumnOptions(rte->relid, varattno);
+ foreach(lc, options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "column_name") == 0)
+ {
+ colname = defGetString(def);
+ break;
+ }
+ }
}
+ else if (varattno == natts + 1)
+ {
+ /* This should be an additional junk column */
+ colname = "tableoid";
+ }
+ else
+ elog(ERROR, "name resolution failed for attribute %d of relation %u",
+ varattno, rte->relid);
}
/*
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 0803c30a48..2148867da8 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -179,6 +179,7 @@ typedef struct PgFdwModifyState
/* info about parameters for prepared statement */
AttrNumber ctidAttno; /* attnum of input resjunk ctid column */
+ AttrNumber toidAttno; /* attnum of input resjunk tableoid column */
int p_nums; /* number of parameters to transmit */
FmgrInfo *p_flinfo; /* output conversion functions for them */
@@ -283,6 +284,12 @@ static void postgresGetForeignRelSize(PlannerInfo *root,
static void postgresGetForeignPaths(PlannerInfo *root,
RelOptInfo *baserel,
Oid foreigntableid);
+static List *generate_scan_tlist_for_relation(PlannerInfo *root,
+ RelOptInfo *foreignrel,
+ Oid foreigntableoid,
+ PgFdwRelationInfo *fpinfo,
+ List *tlist,
+ List *recheck_quals);
static ForeignScan *postgresGetForeignPlan(PlannerInfo *root,
RelOptInfo *foreignrel,
Oid foreigntableid,
@@ -392,6 +399,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,
+ Oid tableoid,
ItemPointer tupleid,
TupleTableSlot *slot);
static void store_returning_result(PgFdwModifyState *fmstate,
@@ -1117,6 +1125,109 @@ postgresGetForeignPaths(PlannerInfo *root,
}
}
+/*
+ * generate_scan_tlist_for_relation :
+ * Constructs fdw_scan_tlist from the followig sources.
+ *
+ * We may have appended tableoid and ctid junk columns to the parse
+ * targetlist. We need to give alternative scan tlist to planner in the
+ * case. This function returns the tlist consists of the following attributes
+ * in the order.
+ *
+ * 1. Relation attributes requested by user and needed for recheck
+ - fpinfo->attrs_used, fdw_recheck_quals and given tlist.
+ * 2. Junk columns and others in root->processed_tlist which are not added by 1
+ *
+ * If no junk column exists, returns NIL.
+ */
+static List *
+generate_scan_tlist_for_relation(PlannerInfo *root,
+ RelOptInfo *foreignrel, Oid foreigntableoid,
+ PgFdwRelationInfo *fpinfo,
+ List *tlist, List *recheck_quals)
+{
+ Index frelid = foreignrel->relid;
+ List *fdw_scan_tlist = NIL;
+ Relation frel;
+ int base_nattrs;
+ ListCell *lc;
+ Bitmapset *attrs = NULL;
+ int attnum;
+
+ /*
+ * RelOptInfo has expanded number of attributes. Check it against the base
+ * relations's attribute number to determine the necessity for alternative
+ * scan target list.
+ */
+ frel = heap_open(foreigntableoid, NoLock);
+ base_nattrs = RelationGetNumberOfAttributes(frel);
+ heap_close(frel, NoLock);
+
+ if (base_nattrs == foreignrel->max_attr)
+ return NIL;
+
+ /* We have junk columns. Construct alternative scan target list. */
+
+ /* collect needed relation attributes */
+ attrs = bms_copy(fpinfo->attrs_used);
+ pull_varattnos((Node *)recheck_quals, frelid, &attrs);
+ pull_varattnos((Node *)tlist, frelid, &attrs);
+
+ /* Add relation's attributes */
+ while ((attnum = bms_first_member(attrs)) >= 0)
+ {
+ TargetEntry *tle;
+ Form_pg_attribute attr;
+ Var *var;
+ char *name = NULL;
+
+ attnum += FirstLowInvalidHeapAttributeNumber;
+ if (attnum < 1)
+ continue;
+ if (attnum > base_nattrs)
+ break;
+
+ attr = TupleDescAttr(frel->rd_att, attnum - 1);
+ if (attr->attisdropped)
+ var = (Var *) makeNullConst(INT4OID, -1, InvalidOid);
+ else
+ {
+ var = makeVar(frelid, attnum,
+ attr->atttypid, attr->atttypmod,
+ attr->attcollation, 0);
+ name = pstrdup(NameStr(attr->attname));
+ }
+
+ tle = makeTargetEntry((Expr *)var,
+ list_length(fdw_scan_tlist) + 1,
+ name,
+ false);
+ fdw_scan_tlist = lappend(fdw_scan_tlist, tle);
+ }
+
+ /* Add junk attributes */
+ foreach (lc, root->processed_tlist)
+ {
+ TargetEntry *tle = lfirst_node(TargetEntry, lc);
+ Var *var = (Var *) tle->expr;
+
+ /*
+ * We aren't interested in non Vars, vars of other rels and base
+ * attributes.
+ */
+ if (IsA(var, Var) && var->varno == frelid &&
+ (var->varattno > base_nattrs || var->varattno < 1))
+ {
+ Assert(tle->resjunk);
+ tle = copyObject(tle);
+ tle->resno = list_length(fdw_scan_tlist) + 1;
+ fdw_scan_tlist = lappend(fdw_scan_tlist, tle);
+ }
+ }
+
+ return fdw_scan_tlist;
+}
+
/*
* postgresGetForeignPlan
* Create ForeignScan plan node which implements selected best path
@@ -1140,10 +1251,11 @@ postgresGetForeignPlan(PlannerInfo *root,
List *fdw_recheck_quals = NIL;
List *retrieved_attrs;
StringInfoData sql;
- ListCell *lc;
if (IS_SIMPLE_REL(foreignrel))
{
+ ListCell *lc;
+
/*
* For base relations, set scan_relid as the relid of the relation.
*/
@@ -1191,6 +1303,17 @@ postgresGetForeignPlan(PlannerInfo *root,
* should recheck all the remote quals.
*/
fdw_recheck_quals = remote_exprs;
+
+ /*
+ * We may have put tableoid and ctid as junk columns to the
+ * targetlist. Generate fdw_scan_tlist in the case.
+ */
+ fdw_scan_tlist = generate_scan_tlist_for_relation(root,
+ foreignrel,
+ foreigntableid,
+ fpinfo,
+ tlist,
+ fdw_recheck_quals);
}
else
{
@@ -1383,16 +1506,12 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
* into local representation and error reporting during that process.
*/
if (fsplan->scan.scanrelid > 0)
- {
fsstate->rel = node->ss.ss_currentRelation;
- fsstate->tupdesc = RelationGetDescr(fsstate->rel);
- }
else
- {
fsstate->rel = NULL;
- fsstate->tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
- }
+ /* Always use the tuple descriptor privided by core */
+ fsstate->tupdesc = node->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
/*
@@ -1543,22 +1662,30 @@ postgresAddForeignUpdateTargets(Query *parsetree,
Var *var;
const char *attrname;
TargetEntry *tle;
+ int varattno = RelationGetNumberOfAttributes(target_relation) + 1;
/*
- * In postgres_fdw, what we need is the ctid, same as for a regular table.
+ * In postgres_fdw, what we need is the tableoid and ctid, same as for a
+ * regular table.
*/
- /* Make a Var representing the desired value */
+ /*
+ * Table OID is needed to retrieved as a non-system junk column in the
+ * returning tuple. We add it as a column after all regular columns.
+ */
+ attrname = "tableoid";
var = makeVar(parsetree->resultRelation,
- SelfItemPointerAttributeNumber,
- TIDOID,
+ varattno++,
+ OIDOID,
-1,
InvalidOid,
0);
- /* Wrap it in a resjunk TLE with the right name ... */
- attrname = "ctid";
-
+ /*
+ * Wrap it in a resjunk TLE with a name accessible later by FDW. Doesn't
+ * seem that we explicitly free this tle but give pstrdup'ed string here
+ * just in case.
+ */
tle = makeTargetEntry((Expr *) var,
list_length(parsetree->targetList) + 1,
pstrdup(attrname),
@@ -1566,6 +1693,29 @@ postgresAddForeignUpdateTargets(Query *parsetree,
/* ... and add it to the query's targetlist */
parsetree->targetList = lappend(parsetree->targetList, tle);
+
+ /* ... also needs to have colname entry */
+ target_rte->eref->colnames =
+ lappend(target_rte->eref->colnames, makeString(pstrdup(attrname)));
+
+
+ /* Do the same for ctid */
+ attrname = "ctid";
+ var = makeVar(parsetree->resultRelation,
+ SelfItemPointerAttributeNumber,
+ TIDOID,
+ -1,
+ InvalidOid,
+ 0);
+
+ tle = makeTargetEntry((Expr *) var,
+ list_length(parsetree->targetList) + 1,
+ pstrdup(attrname),
+ true);
+
+ parsetree->targetList = lappend(parsetree->targetList, tle);
+ target_rte->eref->colnames =
+ lappend(target_rte->eref->colnames, makeString(pstrdup(attrname)));
}
/*
@@ -1769,7 +1919,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, InvalidOid, NULL, slot);
/*
* Execute the prepared statement.
@@ -1824,7 +1974,7 @@ postgresExecForeignUpdate(EState *estate,
TupleTableSlot *planSlot)
{
PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
- Datum datum;
+ Datum toiddatum, ctiddatum;
bool isNull;
const char **p_values;
PGresult *res;
@@ -1835,17 +1985,26 @@ postgresExecForeignUpdate(EState *estate,
prepare_foreign_modify(fmstate);
/* Get the ctid that was passed up as a resjunk column */
- datum = ExecGetJunkAttribute(planSlot,
- fmstate->ctidAttno,
- &isNull);
+ toiddatum = ExecGetJunkAttribute(planSlot,
+ fmstate->toidAttno,
+ &isNull);
+ /* shouldn't ever get a null result... */
+ if (isNull)
+ elog(ERROR, "tableoid is NULL");
+
+ /* Get the ctid that was passed up as a resjunk column */
+ ctiddatum = ExecGetJunkAttribute(planSlot,
+ fmstate->ctidAttno,
+ &isNull);
/* shouldn't ever get a null result... */
if (isNull)
elog(ERROR, "ctid is NULL");
/* Convert parameters needed by prepared statement to text form */
p_values = convert_prep_stmt_params(fmstate,
- (ItemPointer) DatumGetPointer(datum),
- slot);
+ DatumGetObjectId(toiddatum),
+ (ItemPointer) DatumGetPointer(ctiddatum),
+ slot);
/*
* Execute the prepared statement.
@@ -1900,7 +2059,7 @@ postgresExecForeignDelete(EState *estate,
TupleTableSlot *planSlot)
{
PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
- Datum datum;
+ Datum ctiddatum, toiddatum;
bool isNull;
const char **p_values;
PGresult *res;
@@ -1911,17 +2070,26 @@ postgresExecForeignDelete(EState *estate,
prepare_foreign_modify(fmstate);
/* Get the ctid that was passed up as a resjunk column */
- datum = ExecGetJunkAttribute(planSlot,
- fmstate->ctidAttno,
- &isNull);
+ toiddatum = ExecGetJunkAttribute(planSlot,
+ fmstate->toidAttno,
+ &isNull);
+ /* shouldn't ever get a null result... */
+ if (isNull)
+ elog(ERROR, "tableoid is NULL");
+
+ /* Get the ctid that was passed up as a resjunk column */
+ ctiddatum = ExecGetJunkAttribute(planSlot,
+ fmstate->ctidAttno,
+ &isNull);
/* shouldn't ever get a null result... */
if (isNull)
elog(ERROR, "ctid is NULL");
/* Convert parameters needed by prepared statement to text form */
p_values = convert_prep_stmt_params(fmstate,
- (ItemPointer) DatumGetPointer(datum),
- NULL);
+ DatumGetObjectId(toiddatum),
+ (ItemPointer) DatumGetPointer(ctiddatum),
+ NULL);
/*
* Execute the prepared statement.
@@ -2303,6 +2471,28 @@ postgresPlanDirectModify(PlannerInfo *root,
returningList);
}
+ /*
+ * The junk columns in the targetlist is no longer needed for FDW direct
+ * moidfy. Strip them so that the planner doesn't bother.
+ */
+ if (fscan->scan.scanrelid > 0 && fscan->fdw_scan_tlist != NIL)
+ {
+ List *newtlist = NIL;
+ ListCell *lc;
+
+ fscan->fdw_scan_tlist = NIL;
+ foreach (lc, subplan->targetlist)
+ {
+ TargetEntry *tle = lfirst_node(TargetEntry, lc);
+
+ /* once found junk, all the rest are also junk */
+ if (tle->resjunk)
+ continue;
+ newtlist = lappend(newtlist, tle);
+ }
+ subplan->targetlist = newtlist;
+ }
+
/*
* Construct the SQL command string.
*/
@@ -2349,7 +2539,7 @@ postgresPlanDirectModify(PlannerInfo *root,
/*
* Update the foreign-join-related fields.
*/
- if (fscan->scan.scanrelid == 0)
+ if (fscan->fdw_scan_tlist != NIL || fscan->scan.scanrelid == 0)
{
/* No need for the outer subplan. */
fscan->scan.plan.lefttree = NULL;
@@ -3345,7 +3535,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;
@@ -3353,13 +3543,24 @@ create_foreign_modify(EState *estate,
{
Assert(subplan != NULL);
+ /* Find the remote tableoid resjunk column in the subplan's result */
+ fmstate->toidAttno = ExecFindJunkAttributeInTlist(subplan->targetlist,
+ "tableoid");
+ if (!AttributeNumberIsValid(fmstate->toidAttno))
+ elog(ERROR, "could not find junk tableoid column");
+
+ /* First transmittable parameter will be table oid */
+ getTypeOutputInfo(OIDOID, &typefnoid, &isvarlena);
+ fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
+ fmstate->p_nums++;
+
/* Find the ctid resjunk column in the subplan's result */
fmstate->ctidAttno = ExecFindJunkAttributeInTlist(subplan->targetlist,
"ctid");
if (!AttributeNumberIsValid(fmstate->ctidAttno))
elog(ERROR, "could not find junk ctid column");
- /* First transmittable parameter will be ctid */
+ /* Second transmittable parameter will be ctid */
getTypeOutputInfo(TIDOID, &typefnoid, &isvarlena);
fmgr_info(typefnoid, &fmstate->p_flinfo[fmstate->p_nums]);
fmstate->p_nums++;
@@ -3442,6 +3643,7 @@ prepare_foreign_modify(PgFdwModifyState *fmstate)
*/
static const char **
convert_prep_stmt_params(PgFdwModifyState *fmstate,
+ Oid tableoid,
ItemPointer tupleid,
TupleTableSlot *slot)
{
@@ -3453,10 +3655,15 @@ convert_prep_stmt_params(PgFdwModifyState *fmstate,
p_values = (const char **) palloc(sizeof(char *) * fmstate->p_nums);
- /* 1st parameter should be ctid, if it's in use */
- if (tupleid != NULL)
+ /* First two parameters should be tableoid and ctid, if it's in use */
+ if (tableoid != InvalidOid)
{
+ Assert (tupleid != NULL);
+
/* don't need set_transmission_modes for TID output */
+ p_values[pindex] = OutputFunctionCall(&fmstate->p_flinfo[pindex],
+ ObjectIdGetDatum(tableoid));
+ pindex++;
p_values[pindex] = OutputFunctionCall(&fmstate->p_flinfo[pindex],
PointerGetDatum(tupleid));
pindex++;
@@ -3685,8 +3892,8 @@ rebuild_fdw_scan_tlist(ForeignScan *fscan, List *tlist)
new_tlist = lappend(new_tlist,
makeTargetEntry(tle->expr,
list_length(new_tlist) + 1,
- NULL,
- false));
+ tle->resname,
+ tle->resjunk));
}
fscan->fdw_scan_tlist = new_tlist;
}
@@ -5576,12 +5783,18 @@ make_tuple_from_result_row(PGresult *res,
*/
oldcontext = MemoryContextSwitchTo(temp_context);
- if (rel)
- tupdesc = RelationGetDescr(rel);
+ /*
+ * If fdw_scan_tlist is provided for base relation, use the tuple
+ * descriptor given from planner.
+ */
+ if (!rel ||
+ (fsstate &&
+ castNode(ForeignScan, fsstate->ss.ps.plan)->fdw_scan_tlist != NULL))
+ tupdesc = fsstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
else
{
- Assert(fsstate);
- tupdesc = fsstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor;
+ Assert(rel);
+ tupdesc = RelationGetDescr(rel);
}
values = (Datum *) palloc0(tupdesc->natts * sizeof(Datum));
@@ -5623,7 +5836,7 @@ make_tuple_from_result_row(PGresult *res,
errpos.cur_attno = i;
if (i > 0)
{
- /* ordinary column */
+ /* ordinary column and tableoid */
Assert(i <= tupdesc->natts);
nulls[i - 1] = (valstr == NULL);
/* Apply the input function even to nulls, to support domains */
--
2.16.3
From 3a70747cef5235fece5f48ff0c0988353f539e97 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Fri, 24 Aug 2018 16:17:35 +0900
Subject: [PATCH 4/4] Regtest change for PgFDW foreign update fix
---
contrib/postgres_fdw/expected/postgres_fdw.out | 172 ++++++++++++-------------
1 file changed, 86 insertions(+), 86 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index dd4864f006..db19e206e6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -5497,15 +5497,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 tableoid = $1 AND ctid = $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, tableoid, ctid
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, tableoid, ctid FROM "S 1"."T 1" FOR UPDATE
(7 rows)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *;
@@ -5532,13 +5532,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 tableoid = $1 AND ctid = $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.tableoid,
ft2.ctid,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.tableoid, ft2.ctid
+ Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, tableoid, ctid 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)
@@ -5570,24 +5570,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 tableoid = $1 AND ctid = $2 RETURNING "C 1", c2, c3
-> Foreign Scan
- Output: ft2.ctid, ft4.*, ft5.*
+ Output: ft2.tableoid, ft2.ctid, 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.*)::textIS 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.tableoid, 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
"S1"."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.tableoid, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft5.c1
-> Nested Loop
- Output: ft2.ctid, ft4.*, ft4.c1
+ Output: ft2.tableoid, ft2.ctid, 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.tableoid, ft2.ctid, ft2.c2
+ Remote SQL: SELECT "C 1", c2, tableoid, ctid 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"
@@ -6229,13 +6229,13 @@ SELECT * FROM foreign_tbl;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 5;
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
Update on public.foreign_tbl
- Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
+ Remote SQL: UPDATE public.base_tbl SET b = $3 WHERE tableoid = $1 AND ctid = $2 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
- Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid
- Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
+ Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.tableoid, foreign_tbl.ctid
+ Remote SQL: SELECT a, b, tableoid, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
(5 rows)
UPDATE rw_view SET b = b + 5; -- should fail
@@ -6243,13 +6243,13 @@ ERROR: new row violates check option for view "rw_view"
DETAIL: Failing row contains (20, 20).
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 15;
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
Update on public.foreign_tbl
- Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
+ Remote SQL: UPDATE public.base_tbl SET b = $3 WHERE tableoid = $1 AND ctid = $2 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
- Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid
- Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
+ Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.tableoid, foreign_tbl.ctid
+ Remote SQL: SELECT a, b, tableoid, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE
(5 rows)
UPDATE rw_view SET b = b + 15; -- ok
@@ -6316,14 +6316,14 @@ SELECT * FROM foreign_tbl;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 5;
- QUERY PLAN
-----------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Update on public.parent_tbl
Foreign Update on public.foreign_tbl
- Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
+ Remote SQL: UPDATE public.child_tbl SET b = $3 WHERE tableoid = $1 AND ctid = $2 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
- Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid
- Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
+ Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.tableoid, foreign_tbl.ctid
+ Remote SQL: SELECT a, b, tableoid, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
UPDATE rw_view SET b = b + 5; -- should fail
@@ -6331,14 +6331,14 @@ ERROR: new row violates check option for view "rw_view"
DETAIL: Failing row contains (20, 20).
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE rw_view SET b = b + 15;
- QUERY PLAN
-----------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Update on public.parent_tbl
Foreign Update on public.foreign_tbl
- Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
+ Remote SQL: UPDATE public.child_tbl SET b = $3 WHERE tableoid = $1 AND ctid = $2 RETURNING a, b
-> Foreign Scan on public.foreign_tbl
- Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid
- Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
+ Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.tableoid, foreign_tbl.ctid
+ Remote SQL: SELECT a, b, tableoid, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE
(6 rows)
UPDATE rw_view SET b = b + 15; -- ok
@@ -6808,13 +6808,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 tableoid = $1 AND ctid = $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, tableoid, ctid, rem1.*
+ Remote SQL: SELECT f1, tableoid, ctid, ROW(f1, f2) FROM public.loc1 FOR UPDATE
(5 rows)
EXPLAIN (verbose, costs off)
@@ -6832,13 +6832,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 tableoid = $1 AND ctid = $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, tableoid, ctid, rem1.*
+ Remote SQL: SELECT f1, tableoid, ctid, ROW(f1, f2) FROM public.loc1 FOR UPDATE
(5 rows)
EXPLAIN (verbose, costs off)
@@ -6866,13 +6866,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 tableoid = $1 AND ctid = $2
-> Foreign Scan on public.rem1
- Output: ctid, rem1.*
- Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+ Output: tableoid, ctid, rem1.*
+ Remote SQL: SELECT tableoid, ctid, ROW(f1, f2) FROM public.loc1 FOR UPDATE
(5 rows)
DROP TRIGGER trig_row_before_delete ON rem1;
@@ -6890,13 +6890,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 tableoid = $1 AND ctid = $2 RETURNING f1, f2
-> Foreign Scan on public.rem1
- Output: ctid, rem1.*
- Remote SQL: SELECT f1, f2, ctid FROM public.loc1 FOR UPDATE
+ Output: tableoid, ctid, rem1.*
+ Remote SQL: SELECT tableoid, ctid, ROW(f1, f2) FROM public.loc1 FOR UPDATE
(5 rows)
DROP TRIGGER trig_row_after_delete ON rem1;
@@ -7147,12 +7147,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 tableoid = $1 AND ctid = $2
-> Hash Join
Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.*, foo.tableoid
Inner Unique: true
@@ -7171,12 +7171,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.ctid, 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.ctid
+ Remote SQL: SELECT f1, f2, f3, tableoid, ctid FROM public.loct2 FOR UPDATE
-> Hash
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
-> HashAggregate
@@ -7208,12 +7208,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 tableoid = $1 AND ctid = $2
-> Hash Join
Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1))
Hash Cond: (foo.f1 = bar.f1)
@@ -7233,14 +7233,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.ctid, (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.ctid
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.ctid
+ Remote SQL: SELECT f1, f2, f3, tableoid, ctid FROM public.loct2 FOR UPDATE
-> Sort
Output: (ROW(foo.f1)), foo.f1
Sort Key: foo.f1
@@ -7438,17 +7438,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 tableoid = $1 AND ctid = $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.ctid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, tableoid, ctid, ROW(f1, f2, f3) FROM public.loct2 FOR UPDATE
(9 rows)
update bar set f2 = f2 + 100;
@@ -7466,18 +7466,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 tableoid = $1 AND ctid = $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.ctid, bar2.*
+ Remote SQL: SELECT f2, tableoid, ctid, ROW(f1, f2, f3) FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE
(10 rows)
delete from bar where f2 < 400;
@@ -7591,14 +7591,14 @@ SELECT tableoid::int - (SELECT min(tableoid) FROM fp1)::int AS toiddiff, ctid, *
-- random() causes non-direct foreign update
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Update on public.fp1
- Remote SQL: UPDATE public.p1 SET b = $2 WHERE ctid = $1
+ Remote SQL: UPDATE public.p1 SET b = $3 WHERE tableoid = $1 AND ctid = $2
-> Foreign Scan on public.fp1
- Output: a, (b + 1), ctid
+ Output: a, (b + 1), tableoid, ctid
Filter: (random() <= '1'::double precision)
- Remote SQL: SELECT a, b, ctid FROM public.p1 WHERE ((a = 0)) FOR UPDATE
+ Remote SQL: SELECT a, b, tableoid, ctid FROM public.p1 WHERE ((a = 0)) FOR UPDATE
(6 rows)
UPDATE fp1 SET b = b + 1 WHERE a = 0 and random() <= 1;
--
2.16.3
В списке pgsql-hackers по дате отправления:
Следующее
От: Kyotaro HORIGUCHIДата:
Сообщение: Re: Problem while updating a foreign table pointing to apartitioned table on foreign server