Обсуждение: Using FDW AddForeignUpdateTargets for a hidden pseudo-column
Hi all, I have a data store where tuples have unique identities that normally are not visible. I also have a FDW to work with this data store. As per the docs to implement updates for this data store I have AddForeignUpdateTargets() function that adds an artificial column to the target list. It seems to me that I cannot re-use a system attribute number for this artificial resjunk column (as, for instance, postgres_fdw uses SelfItemPointerAttributeNumber). These attributes have specific meaning not compatible with my tuple identity. On other hand using a regular AttrNumber might confuse the query planner. In contrast e..g with Oracle FDW that can use a unique key to identify the row, in my data store the tuple identity is normally not visible. So the data planner might break if it sees a Var node with an unexpected varattno number. What is the best approach to handle such a case? 1. Give up on this entirely and require a unique key for any table used thru FDW. 2. Force the FDW to expose the identity column either explicitly by the user who creates a foreign table or automatically adding it in the corresponding trigger (preferably still making it hidden for normal scans). 3. Modify the postgresql core to nicely handle the case of an unknown target column added by a FDW. 4. Something else? Regards, Aleksey
Aleksey Demakov wrote: > I have a data store where tuples have unique identities that normally are not visible. > I also have a FDW to work with this data store. As per the docs to implement updates > for this data store I have AddForeignUpdateTargets() function that adds an artificial > column to the target list. > > It seems to me that I cannot re-use a system attribute number for this artificial resjunk > column (as, for instance, postgres_fdw uses SelfItemPointerAttributeNumber). These > attributes have specific meaning not compatible with my tuple identity. > > On other hand using a regular AttrNumber might confuse the query planner. In contrast > e..g with Oracle FDW that can use a unique key to identify the row, in my data store > the tuple identity is normally not visible. So the data planner might break if it sees a > Var node with an unexpected varattno number. > > What is the best approach to handle such a case? > > 1. Give up on this entirely and require a unique key for any table used thru FDW. > > 2. Force the FDW to expose the identity column either explicitly by the user who > creates a foreign table or automatically adding it in the corresponding trigger > (preferably still making it hidden for normal scans). > > 3. Modify the postgresql core to nicely handle the case of an unknown target > column added by a FDW. > > 4. Something else? When implementing this for oracle_fdw, I went with your solution #1. The downside is that anything that does not have a unique key cannot be modified. I first thought of using the internal ROWID column that's probably similar to your case, but that wouldn't fit into a tid's 6 bytes, and I found that I could only add resjunk columns for existing columns of the table. Making the internal ROWID an explicit column in the foreign table seemed just too ugly. I don't know if #3 would be difficult, but it sure would make things easier for FDW developers. Yours, Laurenz Albe
--On 14. Juni 2016 10:32:13 +0000 Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > I first thought of using the internal ROWID column that's probably > similar to your case, but that wouldn't fit into a tid's 6 bytes, and I > found that I could only add resjunk columns for existing columns of the > table. > Making the internal ROWID an explicit column in the foreign table seemed > just too ugly. The Informix FDW uses SelfItemPointerAttributeNumber. Luckily the Informix ROWID is a 4 byte encoded identifier (3 first significant bytes are the logical page number, last significant bytes is the slot number within that page). Maybe you can find a way of logically addressing your data, too? It only needs to fit within 6 bytes, afaik. -- Thanks Bernd
A very quick and dirty hack I did in src/backend/optimizer/plan/initsplan.c (in 9.5.3):
--- initsplan.c.orig 2016-06-14 19:08:27.000000000 +0600
+++ initsplan.c 2016-06-14 19:10:55.000000000 +0600
@@ -185,9 +185,12 @@ if (IsA(node, Var)) { Var *var = (Var *) node;
- RelOptInfo *rel = find_base_rel(root, var->varno);
+ RelOptInfo *rel; int attno = var->varattno;
+ if (var->varno == INDEX_VAR)
+ continue;
+ rel = find_base_rel(root, var->varno); if (bms_is_subset(where_needed, rel->relids))
continue; Assert(attno >= rel->min_attr && attno <= rel->max_attr);
And then in my FDW I add the tuple id column like this:
static void
MyAddForeignUpdateTargets(Query *parsetree, RangeTblEntry *target_rte,
Relationtarget_relation)
{Var *var;TargetEntry *tle;
/* Make a Var representing the desired value */var = makeVar(INDEX_VAR, /* instead of parsetree->resultRelation,*/
target_relation->rd_att->natts + 1, INT8OID, -1, InvalidOid,
0);
/* Wrap it in a resjunk TLE with the right name ... */tle = makeTargetEntry((Expr *) var,
list_length(parsetree->targetList)+ 1, pstrdup(MY_FDW_TUPLE_ID), true);
/* ... and add it to the query's targetlist */parsetree->targetList = lappend(parsetree->targetList, tle);
}
I was able to run successfully a couple of very simple tests with these. This seems to
indicate that tweaking the core to handle this case properly is doable.
The question is if this approach is conceptually correct and if so what are the other
required places to patch.
Regards,
Aleksey
Bernd Helmle <mailings@oopsware.de> writes:
> --On 14. Juni 2016 10:32:13 +0000 Albe Laurenz <laurenz.albe@wien.gv.at>
> wrote:
>> I first thought of using the internal ROWID column that's probably
>> similar to your case, but that wouldn't fit into a tid's 6 bytes, and I
>> found that I could only add resjunk columns for existing columns of the
>> table.
>> Making the internal ROWID an explicit column in the foreign table seemed
>> just too ugly.
> The Informix FDW uses SelfItemPointerAttributeNumber. Luckily the Informix
> ROWID is a 4 byte encoded identifier (3 first significant bytes are the
> logical page number, last significant bytes is the slot number within that
> page). Maybe you can find a way of logically addressing your data, too? It
> only needs to fit within 6 bytes, afaik.
There's been some speculation about allowing FDWs to control the type of
the CTID column created for a foreign table, but it hasn't gotten past
the speculation stage yet.
regards, tom lane