Re: Writable foreign tables: how to identify rows

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