5

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
1

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';
  • Thanks. However, the queries I'm refering to are Read Only ones (for filling reports), there are no transactions. pg_stat_activity displays them with the current query being executed, not as idle in transaction. – Federico Cristina Apr 6 '17 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.