Re: [HACKERS] Using FDW AddForeignUpdateTargets for a hidden pseudo-column

[Tom Lane]

Bernd Helmle  writes:
> --On 14. Juni 2016 10:32:13 + Albe Laurenz 
> 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using FDW AddForeignUpdateTargets for a hidden pseudo-column

[Aleksey Demakov]

A very quick and dirty hack I did in src/backend/optimizer/plan/initsplan.c (in 
9.5.3):

--- initsplan.c.orig2016-06-14 19:08:27.0 +0600
+++ initsplan.c 2016-06-14 19:10:55.0 +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,
Relation 
target_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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using FDW AddForeignUpdateTargets for a hidden pseudo-column

[Bernd Helmle]

--On 14. Juni 2016 10:32:13 + Albe Laurenz 
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Using FDW AddForeignUpdateTargets for a hidden pseudo-column

[Albe Laurenz]

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers