Re: Using FDW AddForeignUpdateTargets for a hidden pseudo-column

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