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

2013/3/6 Tom Lane <tgl@sss.pgh.pa.us>:
> 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?
>
Sorry, -1 for me.

The proposed design tried to kill two birds with one stone.
The reason why the new GetForeignRelWidth() can reserve multiple slot
for pseudo-columns is, that intends to push-down complex calculation in
target-list to the remote computing resource.

For example, please assume the third target-entry below is very complex
calculation, thus, it consumes much CPU cycles.
 SELECT x, y, (x-y)^2 from remote_table;

FDW driver will able to replace it with just a reference to a pseudo-column
that shall hold the calculation result of (x-y)^2 in remote side. It does not
take a calculation in local side, it can assist CPU off-load.

If we have a particular system attribute to carry remote "rowid" on foreign-
table declaration time, it will loose opportunity of such kind of optimization.

When we handle a query including sub-queries, it generates its TupleDesc
in run-time without problems. I don't think we have no special reason that
we cannot admit foreign-tables to adopt an alternative TupleDesc being
constructed in run-time.

Thanks,
-- 
KaiGai Kohei <kaigai@kaigai.gr.jp>