Re: Writable foreign tables: how to identify rows

Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com> writes:
> I'm not sure whether postgres_fdw should support, but updatable views
> have no system column including ctid. So, we need magic identifier,
> perhaps it would be set of primary key value(s), to support updating
> remote updatable views via foreign tables.

Yeah, I considered that. I thought seriously about proposing that we
forget magic row identifiers altogether, and instead make postgres_fdw
require a remote primary key for a foreign table to be updatable. That
would solve the immediate problem since we'd no longer need any system
columns at all. On balance though I think it's better for postgres_fdw
to use ctid for this purpose, at least for now: ctid is
better-performing and more reliable than a PK (since someone might
mis-describe the PK, or change a row's PK value underneath us).
Perhaps in a future release we could add a table option to use PK row
identification, but I don't want to try to implement both methods in
postgres_fdw right now.

On the other hand, I don't have a problem with decreeing that
non-Postgres FDWs need to use PK row identification in the first
release; which would be the consequence if we don't do anything about
allowing new system columns in 9.3. We will certainly need that style
of row identification to be written and tested anyway. It won't stop
us from extending things later.

regards, tom lane