Foreign table & Connection reset by peer

On 2/23/19 1:28 PM, Lukáš Sobotka wrote:

Hi guys,

I would be grateful for some advice about foreign tables. I attached
simplified script describing the problem for better understanding.

What I am trying to do:

I am copying tables between two databases and for copying is used
function which are called from bash script.

What is problem:

I am creating foreign tables, selecting from them, dropping them and
everything works well. But always when I am working with them and the
session ends, message about connection resetting appears in log file of
remote database. It is showing "postgres@adam_db LOG:  could not receive
data from client: Connection reset by peer". I am calling the command
often, so this message fill my log file quite a fast. I tried to find
some info about this message, without success.

Where can be problem?

See if the thread below applies:

/messages/by-id/4004.1521759312@sss.pgh.pa.us

Is there some different/correct way how to use foreign tables?
How can I closed connection properly?

For reproducing you can use attached script and after that execute next
command:
/psql -c 'SELECT * FROM ft_numbers'
"postgresql://localhost:5432/bety_db?user=postgres&password=postgres"/

I also tried to drop server after selection but it also logs the
message. Command which I executed:/
psql -c 'SELECT * FROM ft_numbers; DROP SERVER adam_server CASCADE;'
"postgresql://localhost:5432/bety_db?user=postgres&password=postgres"/

I tried to replicate problem in databases with different versions - all
of them ended with the message in log.Used versions:

* PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Ubuntu
9.6.10-1.pgdg16.04+1), compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
* PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
* PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit

Best regards,

Lukas

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks for quick answer. I am feeling a little embarrassed that I did not
find this before... The thread definitely helped me to understand more.

When I execute query with foreign tables, the connection is created
(according pg_stat_activity). The question is what I should do for ending
the connection. I can get PID of connection and force to end it by
pg_terminate_backend, but that is not "clean way".

Unfortunately I can not disable SSL encryption as it is mentioned in the
thread. But it is interesting for me, that the log message (resetting by
peer) is created only in encrypted connection, because in both way the
connection stayed defined in pg_stat_activity after query on foreign
table.

so 23. 2. 2019 v 22:48 odesílatel Adrian Klaver <adrian.klaver@aklaver.com>
napsal:

On 2/24/19 2:25 AM, Lukáš Sobotka wrote:

Thanks for quick answer. I am feeling a little embarrassed that I did
not find this before... The thread definitely helped me to understand more.

When I execute query with foreign tables, the connection is created
(according pg_stat_activity). The question is what I should do for
ending the connection. I can get PID of connection and force to end it

Not sure. In the original thread the OP was calling conn.close() from
inside a Python script and still seeing the message. The only way they
could prevent it was to disable SSL. From what I gather this is down in
the internals of the code and needs a patch to fix. Someone with more
knowledge of this will need to comment further.

by pg_terminate_backend, but that is not "clean way".

Unfortunately I can not disable SSL encryption as it is mentioned in the
thread. But it is interesting for me, that the log message (resetting by
peer) is created only in encrypted connection, because in both way the
connection stayed defined in pg_stat_activity after query on foreign table.

so 23. 2. 2019 v 22:48 odesílatel Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> napsal:

--
Adrian Klaver
adrian.klaver@aklaver.com