Re: FDW Foreign Table Access: strange LOG message

On 03/20/2018 11:52 AM, Albrecht Dreß wrote:
> Hi all,
>
> I use Postgres 10.3 on a Debian Stretch system with foreign tables, and
> noticed strange LOG messages when accessing them.
>
> The data base setup is basically
>
> ---8<------------------------------------------------------------------------------------------
>
> CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public;
> CREATE SERVER ext_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
>     dbname 'ext_db', host 'localhost', updatable 'false');
> CREATE FOREIGN TABLE public.ext_table (
>     […]
> ) SERVER ext_server OPTIONS (schema_name 'public', table_name
> 'some_table', updatable 'false');
> ---8<------------------------------------------------------------------------------------------
>
>
> Now I use the following trivial Python2 (psycopg2) code to access the
> tables:
>
> ---8<------------------------------------------------------------------------------------------
>
> import psycopg2
> conn = psycopg2.connect(dbname='my_db')
> with conn.cursor() as csr:
>    csr.execute("SELECT * FROM […] LIMIT 1")
>    csr.fetchone()
> conn.close()
> ---8<------------------------------------------------------------------------------------------
>
>
> When I access a “local” table of my_db in the SELECT statement, there is
> no LOG message.  However, reading from ext_table works just fine, but
> the conn.close() statement above triggers the log message
>
> ---8<------------------------------------------------------------------------------------------
>
> [time stamp/pid] user(at)my_db LOG:  could not receive data from client:
> Connection reset by peer
> ---8<------------------------------------------------------------------------------------------
>
>
> Did I miss something in the setup here?  Or is there any other way to
> get rid of the message (which fills ~95% of my logs)?

My suspicion is it has to do with this:

https://www.postgresql.org/docs/10/static/postgres-fdw.html#id-1.11.7.43.10
"
F.34.2. Connection Management

postgres_fdw establishes a connection to a foreign server during the
first query that uses a foreign table associated with the foreign
server. This connection is kept and re-used for subsequent queries in
the same session. However, if multiple user identities (user mappings)
are used to access the foreign server, a connection is established for
each user mapping.
"

Guessing that conn.close() is not closing the internal FDW connection
properly. Not sure how to fix, I will ponder some though.

>
> Thanks in advance,
> Albrecht.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com