Postgresql – Using TCP keepalive for terminating orphan postgres queries not working

killpostgresqlpostgresql-8.4tcpip

Problem: A user of some application executes a report which involves a heavy sql query. After some minutes, the user then quits the application (or even worse, the app crashes, or the connection to the database server is lost). The query however continues its execution, even when it is no more needed.

So I've read about these types of situations and how to handle them, and it is suggested to use TCP Keepalive configuration.

The problem is that it looks like postgres is ignoring this configuration. I've lowered the original configuration values both at "postgres level" and at "OS level", with no luck:

  • postgresql.conf
    • tcp_keepalives_idle = 60
    • tcp_keepalives_interval = 6
    • tcp_keepalives_count = 10
  • Ubuntu
    • echo 60 > /proc/sys/net/ipv4/tcp_keepalive_time
    • echo 10 > /proc/sys/net/ipv4/tcp_keepalive_intvl
    • echo 6 > /proc/sys/net/ipv4/tcp_keepalive_probe

I even tried reloading/restarting postgres but nothing worked, the query keeps running past two minutes until the very end. ¿Is there any additional step I am missing?

EDIT: Result of pg_stat_activity:

  datid   |                  datname                   | procpid | usesysid | usename  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          current_query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | waiting |          xact_start           |          query_start          |         backend_start         | client_addr | client_port 
----------+--------------------------------------------+---------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
467061608 | libertya_prod_hts_rg1_20161004_qafuncional |   16716 |    16384 | libertya | SELECT dt.signo_issotrx, dt.name as tipodoc, i.ad_org_id, i.ad_client_id, i.documentno, i.c_invoice_id as doc_id, i.c_order_id, i.c_bpartner_id, bp.name as bpartner, i.issotrx, i.dateacct, i.dateinvoiced as datedoc, p.netdays, i.dateinvoiced + (p.netdays::text || ' days'::text)::interval AS duedate, paymenttermduedays(i.c_paymentterm_id, i.dateinvoiced::timestamp with time zone, now()) AS daysdue, i.dateinvoiced + (p.discountdays::text || ' days'::text)::interval AS discountdate, round(i.grandtotal * p.discount * 0.01::numeric, 2) AS discountamt, i.grandtotal AS grandtotalmulticurrency, invoicepaid(i.c_invoice_id, i.c_currency_id, 1) AS paidamtmulticurrency, invoiceopen(i.c_invoice_id, 0, i.c_currency_id,i.c_conversiontype_id) AS openamtmulticurrency, currencybase(i.grandtotal,i.c_currency_id,i.dateinvoiced, i.ad_client_id, i.ad_org_id) AS grandtotal, invoicepaid(i.c_invoice_id, 118, 1) AS paidamt, invoiceopen(i.c_invoice_id, 0,118,i.c_conversiontype_id) AS openamt, i.c_currency_id, i.c_conversiontype_id, i. | f       | 2017-04-07 08:06:23.896404-03 | 2017-04-07 08:06:24.513244-03 | 2017-04-07 08:06:02.817331-03 | 127.0.0.1   |       59354

Best Answer

Postgres 9.6+

Pg 9.6 brings idle_in_transaction_session_timeout:

Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.

The default value of 0 disables this feature.

It can be set easily,

SET idle_in_transaction_session_timeout = '5min';
Related Question