Re: FDW Foreign Table Access: strange LOG message - Mailing list pgsql-general

From Albrecht Dreß
Subject Re: FDW Foreign Table Access: strange LOG message
Date
Msg-id 4LEJPNQI.ZWASSFGA.Q7QOROSI@UY3LDXOV.Y5MUB6GM.BGMWW4SO
Whole thread Raw
In response to Re: FDW Foreign Table Access: strange LOG message  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: FDW Foreign Table Access: strange LOG message
List pgsql-general
Hi Adrian & Tom:

Thanks a lot for your input!

Am 20.03.18 20:38 schrieb(en) Tom Lane:
>> My suspicion is it has to do with this:
>> postgres_fdw establishes a connection to a foreign server during the first query that uses a foreign table
associatedwith the foreign server. This connection is kept and re-used for subsequent queries in the same session. 
>
> Perhaps.  It's not entirely clear if these complaints are about the original user session or the sub-session opened
bypostgres_fdw. (Albrecht, if you're not sure either, enabling log_connections and log_disconnections might make it
clearer.)

Unfortunately, in my original post, I confused the primary and secondary (accessed via the FDW) data bases in my
examplewhen I anonymised their identifiers…  Sorry for the confusion!  The /real/ structure is 

     client ---[libpq]--> my_db ---[FDW]--> ext_db

and the LOG message is actually related to the “internal” connection, i.e. ext_db (*not* my_db) complains about “could
notreceive data” after accessing the table in ext_db through the FDW from my_db. 

The effect is not limited to Python, but happens with psql, too.  I.e. running (ext_table is the foreign table mapped
intomy_db, and a mapping exists only for user “user”) 

sudo -u user /usr/lib/postgresql/10/bin/psql my_db <<< "select * from ext_table limit 1;"

*also* triggers the LOG message

[time stamp/pid] user@ext_db LOG:  could not receive data from client: Connection reset by peer

> I don't see any such log messages when testing postgres_fdw here, which is odd; why are my results different?

Yes, that's strange!  I have the following deb packages (from the stretch-pgdg repo) installed:

postgresql-10            10.3-1.pgdg90+1 amd64
postgresql-client-10     10.3-1.pgdg90+1 amd64
postgresql-client-common 190.pgdg90+1    all
postgresql-common        190.pgdg90+1    all
libpq5:amd64             10.3-1.pgdg90+1 amd64

> If these are about the FDW connections, maybe the answer is that postgres_fdw ought to establish a backend-exit
callbackin which it can shut down its connections gracefully. 

Hmm, yes, sounds like a sensible explanation.

> If it's doing that now, I sure don't see where.

So, if I understand you correctly, this needs to be addressed in postgres_fdw, and there is nothing I can fix in my
setup(except for suppressing LOG messages) to get rid of the message? 

Thanks again,
Albrecht.
Attachment

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: postgresql-10.3 on unbuntu-17.10 - how??
Next
From: Adrian Klaver
Date:
Subject: Re: FDW Foreign Table Access: strange LOG message