Tom, > 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).
Well, a good test case for this could be the various key-value stores, where the foreign row identifier (FRI) is an immutable string key of arbitrary size. If we can support that, there's a lot of datastores we can support, and even a rule of "your target must have a single-column key" would be tolerable for non-postgres FDWs for quite a while. > 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. Per the above, it would be good to allow the "system column" to also be a column which is exposed to the user. > 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. Well, if it gets pgsql_fdw in, I'm for it. I'd always assumed that writeable non-postgres targets were going to be hard. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
