Re: Writable foreign tables: how to identify rows

On 2013-03-13 09:51:59 -0500, Merlin Moncure wrote:
> On Wed, Mar 13, 2013 at 9:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> >> On Wed, Mar 6, 2013 at 11:06 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

ISTM thats just mostly the same semantics as you have locally. If you
locally do an UPDATE it will use the ctid for location of the updated
row.
If it got updated since (which you can detect by checking whether xmax
has committed) the behaviour depends on the transaction isolation level. On
repeatable_read upwards it errors out, otherwise it will do the
EvalPlanQual magic. Which is to make sure the WHERE condition still fits
the row. Thats obviously where the cases start to differ. But thats also
the case if you use the primary key since you obviously can have more
quals than just that on the origin side.
Perhaps pgsql-fdw should make sure the update was performed *without*
following the ctid chain to a new valid tuple?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services