Re: Writable foreign tables: how to identify rows

On Wed, Mar 6, 2013 at 6:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.
>

+1 for adding a new system attribute. We did something similar in
Postgres-XC, though problem there was much simpler because we always
knew that the remote FDW is a Postgres instance running the same
version. So we added a new system column "node_id" which does not get
any disk storage, but gets set during execution time depending on
which node the tuple belongs to. The ctid system column of course is
set to the remote ctid.

In the context of postgres_fdw, I am not sure if we need an additional
system column like a node_id. Would there be a possibility where
tuples to-be-modified are coming from different foreign tables and at
runtime we need to decide where to execute the UPDATE/DELETE operation
? If we start supporting inheritance involving foreign tables as child
tables, this will become a reality.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee