Re: Writable foreign tables: how to identify rows

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> Perhaps pgsql-fdw should make sure the update was performed *without*
> following the ctid chain to a new valid tuple?

I did think about these issues before committing the patch ;-)

The basic theory in PG's existing design is to postpone locking rows as
long as possible; which means that when we do finally lock a target row,
we have to check if it's changed since we scanned it, and that leads
into the whole EvalPlanQual mess. I considered trying to make FDWs
duplicate that behavior, but gave up on it. In the first place, it's
hard to see how you even define "did the row change" unless you have
something exactly like ctids (including forward update chains). And
in the second place, this would mandate yet another round trip to the
remote server for each row to be updated.

In the patch as committed, the expectation (which is satisfied by
postgres_fdw) is that FDWs should lock rows that are candidates for
update/delete during the initial scan. This avoids an extra round trip
and justifies leaving EvalPlanQual out of the picture altogether.
The cost is that we may lock rows that we end up not updating, because
they fail locally-checked restriction or join conditions. I think on
balance that's a good trade-off.

regards, tom lane