MIME-Version: 1.0
Date: Tue, 09 Aug 2022 14:54:14 +0300
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>, PostgreSQL mailing lists
 <pgsql-bugs@lists.postgresql.org>
Subject: Re: foreign join error "variable not found in subplan target list"
In-Reply-To: 
 <CAMbWs4_k4yFmOJ+U-Ap7BvQTbdUYhKmVchV=yJFHdtw_10nQZg@mail.gmail.com>
References: <cfb17bf6dfdf876467bd5ef533852d18@postgrespro.ru>
 <CAPmGK16mosKq6h=TbQP8wKJu4HFdCBjrxAV+TLYfovuNmfNhHA@mail.gmail.com>
 <CAMbWs4_k4yFmOJ+U-Ap7BvQTbdUYhKmVchV=yJFHdtw_10nQZg@mail.gmail.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <fcc74118ee33ca5e213fa855ba70ff22@postgrespro.ru>
Content-Type: multipart/mixed;
 boundary="=_80fca6f263fa57f15543bb7d7a874928"
Archived-At: 
 <https://www.postgresql.org/message-id/fcc74118ee33ca5e213fa855ba70ff22%40postgrespro.ru>
Precedence: bulk

--=_80fca6f263fa57f15543bb7d7a874928
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=UTF-8;
 format=flowed

Richard Guo писал 2022-08-09 06:51:
> On Tue, Aug 9, 2022 at 9:59 AM Etsuro Fujita <etsuro.fujita@gmail.com>
> wrote:
> 
>> On Tue, Aug 9, 2022 at 12:26 AM Alexander Pyhalov
>> <a.pyhalov@postgrespro.ru> wrote:
>>> Using the following patch I'm able to get
>>> "ERROR:  variable not found in subplan target list"
>>> on foreign join pushdown for update returning.
>> 
>> Reproduced here.  Will look into this.
>> 
>> Thanks for the report!
> 
> A rough look shows to me that the part of plan that causes problem
> looks
> like:
> 
>  ->  Result
>        Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft2.c2
>        ->  Sort
>              Output: ft2.ctid, ft2.*, ft4.*, ft4.c1
>              Sort Key: ft4.c1
> 
> Note that for node 'Result', one of its target entries, 'ft2.c2', does
> not appear in the target list of its subplan, i.e. node 'Sort'.
> 
> I think something goes wrong in postgresGetForeignPlan() when we build
> the list of columns to be fetched from the foreign server, and fix the
> subplan's tlist with that, where we would include in columns specified
> in local conditions. In this case the local condition is 'ft2.c2 ===
> ft4.c1', and that's how we have entry 'ft2.c2' in Result's target
> list.
> 
> Thanks
> Richard

Hi.
The issue seems to appear due to the fact that Sort path doesn't provide 
all vars, needed by local_conds.
What if we check for this condition specifically?

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional
--=_80fca6f263fa57f15543bb7d7a874928
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
 name=0001-postgres_fdw-should-avoid-sorted-paths-which-don-t-p.patch
Content-Disposition: attachment;
 filename=0001-postgres_fdw-should-avoid-sorted-paths-which-don-t-p.patch;
 size=2649

RnJvbSBlMGNmN2JlMTY4MmZlYjhkYmNhODBhZGVmYmFhYzA2ZWY4MzE1YmE0IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBbGV4YW5kZXIgUHloYWxvdiA8YS5weWhhbG92QHBvc3RncmVz
cHJvLnJ1PgpEYXRlOiBUdWUsIDkgQXVnIDIwMjIgMTQ6NDc6MTYgKzAzMDAKU3ViamVjdDogW1BB
VENIXSBwb3N0Z3Jlc19mZHcgc2hvdWxkIGF2b2lkIHNvcnRlZCBwYXRocyB3aGljaCBkb24ndCBw
cm92aWRlCiBhbGwgcmVxdWlyZWQgdmFycwoKLS0tCiBjb250cmliL3Bvc3RncmVzX2Zkdy9wb3N0
Z3Jlc19mZHcuYyB8IDU2ICsrKysrKysrKysrKysrKysrKysrKysrKysrKysrCiAxIGZpbGUgY2hh
bmdlZCwgNTYgaW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL2NvbnRyaWIvcG9zdGdyZXNfZmR3
L3Bvc3RncmVzX2Zkdy5jIGIvY29udHJpYi9wb3N0Z3Jlc19mZHcvcG9zdGdyZXNfZmR3LmMKaW5k
ZXggMTYzMjAxNzBjZWUuLmUyMWRkZDc0ODVmIDEwMDY0NAotLS0gYS9jb250cmliL3Bvc3RncmVz
X2Zkdy9wb3N0Z3Jlc19mZHcuYworKysgYi9jb250cmliL3Bvc3RncmVzX2Zkdy9wb3N0Z3Jlc19m
ZHcuYwpAQCAtNTQxLDYgKzU0MSw3IEBAIHN0YXRpYyB2b2lkIG1lcmdlX2Zkd19vcHRpb25zKFBn
RmR3UmVsYXRpb25JbmZvICpmcGluZm8sCiAJCQkJCQkJICBjb25zdCBQZ0Zkd1JlbGF0aW9uSW5m
byAqZnBpbmZvX28sCiAJCQkJCQkJICBjb25zdCBQZ0Zkd1JlbGF0aW9uSW5mbyAqZnBpbmZvX2kp
Owogc3RhdGljIGludAlnZXRfYmF0Y2hfc2l6ZV9vcHRpb24oUmVsYXRpb24gcmVsKTsKK3N0YXRp
YyBib29sIHBhdGhfcHJvdmlkZXNfbG9jYWxfdmFycyhQYXRoICpwYXRoLCBQZ0Zkd1JlbGF0aW9u
SW5mbyAqZnBpbmZvKTsKIAogCiAvKgpAQCAtNTc3NSw2ICs1Nzc2LDU0IEBAIGZvcmVpZ25fam9p
bl9vayhQbGFubmVySW5mbyAqcm9vdCwgUmVsT3B0SW5mbyAqam9pbnJlbCwgSm9pblR5cGUgam9p
bnR5cGUsCiAJcmV0dXJuIHRydWU7CiB9CiAKKy8qCisgKiBEZXRlcm1pbmVzIGlmIGFsbCB2YXJz
LCB1c2VkIGJ5IGZwaW5mby0+bG9jYWxfY29uZHMsCisgKiBhcmUgcHJvdmlkZWQgYnkgcGF0aC4K
KyAqLworc3RhdGljIGJvb2wKK3BhdGhfcHJvdmlkZXNfbG9jYWxfdmFycyhQYXRoICpwYXRoLCBQ
Z0Zkd1JlbGF0aW9uSW5mbyAqZnBpbmZvKQoreworCUxpc3QJICAgKnZhcnNfcHJvdmlkZWQ7CisJ
TGlzdENlbGwgICAqbGMxOworCUxpc3RDZWxsICAgKmxjMjsKKwlMaXN0Q2VsbCAgICpsYzM7CisK
KwlpZiAocGF0aCA9PSBOVUxMKQorCQlyZXR1cm4gZmFsc2U7CisKKwkvKiBHZXQgYWxsIHZhcnMs
IHByb3ZpZGVkIGJ5IHBhdGggKi8KKwl2YXJzX3Byb3ZpZGVkID0gcHVsbF92YXJfY2xhdXNlKChO
b2RlICopIHBhdGgtPnBhdGh0YXJnZXQtPmV4cHJzLCBQVkNfSU5DTFVERV9QTEFDRUhPTERFUlMp
OworCisJZm9yZWFjaChsYzEsIGZwaW5mby0+bG9jYWxfY29uZHMpCisJeworCQlSZXN0cmljdElu
Zm8gKnJpID0gKFJlc3RyaWN0SW5mbyAqKSBsZmlyc3QobGMxKTsKKwkJTGlzdAkgICAqdmFyc191
c2VkOworCisJCS8qIEdldCB2YXJzLCB1c2VkIGJ5IHRoaXMgcmVzdHJpY3Rpb24gY2xhdXNlICov
CisJCXZhcnNfdXNlZCA9IHB1bGxfdmFyX2NsYXVzZSgoTm9kZSAqKSByaS0+Y2xhdXNlLCBQVkNf
SU5DTFVERV9QTEFDRUhPTERFUlMpOworCQlmb3JlYWNoKGxjMiwgdmFyc191c2VkKQorCQl7CisJ
CQlWYXIJCSAgICp1c2VkID0gbGZpcnN0KGxjMik7CisJCQlib29sCQlmb3VuZCA9IGZhbHNlOwor
CisJCQlmb3JlYWNoKGxjMywgdmFyc19wcm92aWRlZCkKKwkJCXsKKwkJCQlWYXIJCSAgICpwcm92
aWRlZCA9IGxmaXJzdChsYzMpOworCisJCQkJaWYgKHByb3ZpZGVkLT52YXJubyA9PSB1c2VkLT52
YXJubyAmJiBwcm92aWRlZC0+dmFyYXR0bm8gPT0gdXNlZC0+dmFyYXR0bm8pCisJCQkJeworCQkJ
CQlmb3VuZCA9IHRydWU7CisJCQkJCWJyZWFrOworCQkJCX0KKwkJCX0KKwkJCWlmICghZm91bmQp
CisJCQkJcmV0dXJuIGZhbHNlOworCQl9CisJfQorCisJcmV0dXJuIHRydWU7Cit9CisKIHN0YXRp
YyB2b2lkCiBhZGRfcGF0aHNfd2l0aF9wYXRoa2V5c19mb3JfcmVsKFBsYW5uZXJJbmZvICpyb290
LCBSZWxPcHRJbmZvICpyZWwsCiAJCQkJCQkJCVBhdGggKmVwcV9wYXRoKQpAQCAtNTgxMiw2ICs1
ODYxLDEzIEBAIGFkZF9wYXRoc193aXRoX3BhdGhrZXlzX2Zvcl9yZWwoUGxhbm5lckluZm8gKnJv
b3QsIFJlbE9wdEluZm8gKnJlbCwKIAkJCQkJCQkJIHVzZWZ1bF9wYXRoa2V5cywKIAkJCQkJCQkJ
IC0xLjApOwogCisJCS8qCisJCSAqIFNvcnRlZCBwYXRoIHNob3VsZCBwcm92aWRlIGFsbCB2YXJz
IG5lZWRlZCBieSBsb2NhbCBjb25kaXRpb25zIG9yCisJCSAqIHdlJ2xsIG5vdCBiZSBhYmxlIHRv
IHVzZSBpdAorCQkgKi8KKwkJaWYgKHNvcnRlZF9lcHFfcGF0aCAhPSBOVUxMICYmICFwYXRoX3By
b3ZpZGVzX2xvY2FsX3ZhcnMoc29ydGVkX2VwcV9wYXRoLCAoUGdGZHdSZWxhdGlvbkluZm8gKikg
cmVsLT5mZHdfcHJpdmF0ZSkpCisJCQlyZXR1cm47CisKIAkJaWYgKElTX1NJTVBMRV9SRUwocmVs
KSkKIAkJCWFkZF9wYXRoKHJlbCwgKFBhdGggKikKIAkJCQkJIGNyZWF0ZV9mb3JlaWduc2Nhbl9w
YXRoKHJvb3QsIHJlbCwKLS0gCjIuMzQuMQoK
--=_80fca6f263fa57f15543bb7d7a874928--