2013/3/6 Tom Lane <t...@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 <kai...@kaigai.gr.jp> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
