Re: Writable foreign tables: how to identify rows

On Wed, Mar 13, 2013 at 9:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Wed, Mar 6, 2013 at 11:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> 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.
>
>> IMO, Utilizing anything but this for remote record identification is
>> an implementation specific optimization.  Aren't the semantics
>> different though?  If you go:
>
>> update foo set id = 1 where id = 1;
>
>> the primary key would not change, but the ctid would.  or is that
>> already a handled?
>
> In postgres_fdw as it currently stands, the remote ctid would change.
> I'm not sure we should posit that as a universal property of FDWs
> though.  It's not even a meaningful question for FDWs with no underlying
> rowid concept.

I just find it odd that rowid concept is used at all without strong
guarantee that the record you are referencing is the one you are
supposed to be referencing.  Basically I'm saying PKEY semantics are
the correct ones and that ctid is ok to use iff they match the pkey
ones.  I don't think this is possible unless you maintain a remote
lock on the ctid between when you fetch it and do some other
operation.

merlin