Re: [HACKERS] postgres_fdw bug in 9.6

I wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Aug 29, 2017 at 5:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> We'd definitely need to do things that way in 9.6. I'm not quite sure
>>> whether it's too late to adopt the clean solution in v10.

>> It probably is now. Are you still planning to do something about this patch?

> It's still on my list, but I didn't get to it during the CF.

I got around to looking at this thread again. Perhaps as a result of
not having looked at it for awhile, the thing that had been nagging
at me from the very beginning (where I said "I kind of wonder why this
infrastructure exists at all") finally crystallized: I do not think
this infrastructure *should* exist, at least not for postgres_fdw.

The reason EPQ exists at all is basically to avoid applying the overhead
of SELECT FOR UPDATE in cases where we don't have to. If we chased up
to the end of every update chain and locked the frontmost tuple every
time, then EPQ rechecks wouldn't be needed because we'd already have
locked and qual-checked the right tuple. This is, I think, a reasonable
optimization on local tables; but it's far less clear that it would be
sane to do it like that on remote tables, because of the extra network
round trips required. Moreover, we already foreclosed the decision
because postgres_fdw always issues SELECT FOR UPDATE, not just SELECT,
to the remote side in any case where EPQ locking might happen.

Therefore, the remote side is already holding a tuple lock on the right
tuple, and it's already done anything EPQ-like that might need to have
happened, and it's guaranteed to have given us back a fully up-to-date
row value. This is true whether the remote query is for a single table
or for a join.

In short, we should get rid of all of this expensive and broken logic and
just make EPQ recheck on a foreign join be a no-op, just as it is for a
foreign base table. (Indeed, considering the fact that what we think is
a base table might be a join view on the far side, it's hard to come to
any conclusion but that making such a distinction is fundamentally wrong.)
If there are any cases where the join pushdown logic is failing to emit
"FOR UPDATE", we need to fix that, but a quick check says that it does
emit that at least in simple cases.

Comments?

regards, tom lane