Message-ID: <5A1FFFD7.6080904@lab.ntt.co.jp>
Date: Thu, 30 Nov 2017 21:55:51 +0900
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Subject: Re: [HACKERS] postgres_fdw bug in 9.6
References: 
 <CAMkU=1y2G8VOVBHv3iXU2TMAj7-RyBFFW1uhkr5sm9LQ2=X35g@mail.gmail.com>
 <22168.1504041271@sss.pgh.pa.us>
 <20170905044336.18925.22634.pgcf@coridan.postgresql.org>
 <07ed8f27-12cf-8403-d23b-f7d907de4a83@lab.ntt.co.jp>
 <CAFjFpRd7fuanj3s-bXpeuLhaRJnFuEWCiwu_-Q4Ox7wGCAW_Mw@mail.gmail.com>
 <7031.1507215420@sss.pgh.pa.us> <25877.1511886076@sss.pgh.pa.us>
 <CA+TgmoaUu=QTvfoCVfidXH=CU4_e1nWTMj=jTQpB3eoHYAVpzw@mail.gmail.com>
 <CAFjFpRc4f9u60t7VgTiw1quoto5gmXJ0tHMwCfSM5dx253B8fw@mail.gmail.com>
 <27145.1511994722@sss.pgh.pa.us>
In-Reply-To: <27145.1511994722@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>,
        Robert Haas <robertmhaas@gmail.com>,
 Jeff Janes <jeff.janes@gmail.com>,
        David Steele <david@pgmasters.net>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Precedence: bulk

(2017/11/30 7:32), Tom Lane wrote:
> AFAICT, [1] just demonstrates that nobody had thought about the scenario
> up to that point, not that the subsequently chosen solution was a good
> one.  In that example,
>
> LockRows  (cost=100.00..101.18 rows=4 width=70)
>     Output: tab.a, tab.b, tab.ctid, foo.*, bar.*
>     ->   Nested Loop  (cost=100.00..101.14 rows=4 width=70)
>           Output: tab.a, tab.b, tab.ctid, foo.*, bar.*
>           Join Filter: (foo.a = tab.a)
>           ->   Seq Scan on public.tab  (cost=0.00..1.01 rows=1 width=14)
>                 Output: tab.a, tab.b, tab.ctid
>           ->   Foreign Scan  (cost=100.00..100.08 rows=4 width=64)
>                 Output: foo.*, foo.a, bar.*, bar.a
>                 Relations: (public.foo) INNER JOIN (public.bar)
>                 Remote SQL: SELECT l.a1, l.a2, r.a1, r.a2 FROM (SELECT ROW(l.a9), l.a9 FROM (SELECT a a9 FROM public.foo FOR UPDATE) l) l (a1, a2) INNER JOIN (SELECT ROW(r.a9), r.a9 FROM (SELECT a a9 FROM public.bar FOR UPDATE) r) r (a1, a2) ON ((l.a2 = r.a2))
>
> the output of the foreign join cannot change during EPQ, since the remote
> server already locked the rows before returning them.  The only thing that
> can change is the output of the local scan on public.tab.  Yes, we then
> need to re-verify that foo.a = tab.a ... but in this example, that's the
> responsibility of the NestLoop plan node, not the foreign join.

That's right, but is that true when the FDW uses late row locking?

Best regards,
Etsuro Fujita