Writable foreign tables: how to identify rows - Mailing list pgsql-hackers

One of the core problems for a writable-foreign-tables feature is how
to identify a previously-fetched row for UPDATE or DELETE actions.
In an ordinary Postgres table, we use the ctid system column for that,
but a remote table doesn't necessarily have such a thing.  (Oracle has
a "rowid" that acts a lot like our ctids, but I don't believe the
concept is common in other RDBMSes.)  Without any magic row identifier
such as these, I suppose an FDW would need to insist on knowing the
primary key for the remote table, so that it could update based on the
values of the pkey column(s).

The current writable-foreign-tables patch goes to great lengths to try
to cater for magic row identifiers of unspecified data types; which is
something I encouraged in the beginning, but now that I see the results
I think the messiness-to-usefulness quotient is awfully low.  Basically
what it's doing is hacking the TupleDesc associated with a foreign table
so that the descriptor (sometimes) includes extra columns.  I don't
think that's workable at all --- there are too many places that assume
that relation TupleDescs match up with what's in the catalogs.

I think if we're going to support magic row identifiers, they need to
be actual system columns, complete with negative attnums and entries
in pg_attribute.  This would require FDWs to commit to the data type
of a magic row identifier at foreign-table creation time, but that
doesn't seem like much of a restriction: probably any one FDW would
have only one possible way to handle a magic identifier.  So I'm
envisioning adding an FDW callback function that gets called at table
creation and returns an indication of which system columns the foreign
table should have, and then we actually make pg_attribute entries for
those columns.

For postgres_fdw, that would really be enough, since it could just
cause a "ctid" column to be created with the usual definition.  Then
it could put the remote ctid into the usual t_self field in returned
tuples.

Supporting magic identifiers that aren't of the TID data type is
considerably harder, mainly because it's not clear how heap_getsysattr()
could know how to fetch the column value.  I have some rough ideas
about that, but I suggest that we might want to punt on that extension
for the time being.

Thoughts?
        regards, tom lane