Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eJidT-0005A0-Nh for pgsql-hackers@arkaria.postgresql.org; Tue, 28 Nov 2017 16:21:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eJidT-0000Q8-7H for pgsql-hackers@arkaria.postgresql.org; Tue, 28 Nov 2017 16:21:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eJidT-0000Pv-0i for pgsql-hackers@lists.postgresql.org; Tue, 28 Nov 2017 16:21:27 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eJidP-0005UY-JA for pgsql-hackers@postgresql.org; Tue, 28 Nov 2017 16:21:26 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id vASGLGCZ025878; Tue, 28 Nov 2017 11:21:16 -0500 From: Tom Lane To: Robert Haas cc: Etsuro Fujita , Jeff Janes , David Steele , Ashutosh Bapat , pgsql-hackers Subject: Re: [HACKERS] postgres_fdw bug in 9.6 In-reply-to: <7031.1507215420@sss.pgh.pa.us> References: <22168.1504041271@sss.pgh.pa.us> <20170905044336.18925.22634.pgcf@coridan.postgresql.org> <07ed8f27-12cf-8403-d23b-f7d907de4a83@lab.ntt.co.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <25876.1511886076.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Tue, 28 Nov 2017 11:21:16 -0500 Message-ID: <25877.1511886076@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk I wrote: > Robert Haas writes: >> On Tue, Aug 29, 2017 at 5:14 PM, Tom Lane 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