Thread: 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:

https://www.postgresql.org/message-id/4004.1521759312%40sss.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

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