idle connection in postgres causing process to stuck or result in error

Question

We have Postgres as our backend database . Our process runs some job(i.e it does some insert/update in DB) and then sleep for an hour.

This what we have noticed. While the time our process is sleeping our Postgres connection status is seen as idle.

postgres  5045  0.3  0.4 231220 33780 ?        Ss   12:13   0:16 postgres: scp scp_test x.x.x.x(60400) idle

Now my question is?

If, I have a process that sleep for an hour .

Does Postgres close the idle connection after some time?

Because on the next run the process is not able to insert/update any record in DB.

Here how my code looks like.

  $logger  = Logger.new('log/checker.log')
  last_modified = Time.now
  while
    if (last_modified == File.mtime(file_path))
      $logger.info "Sleeping for 1 hour"
      sleep 3600
    else
      $logger.info "Inserting the file mtime changed .."
      last_modified = File.mtime(file_path)
      $logger.info "File modified ....."
      attributes = Test.testFile(file_path)
      index = 0 
      $logger.info "........Index set......"
      header_attributes = attributes.shift
      $logger.info "...........Header removed..........."
      trailer_attributes = attributes.pop
      $logger.info "...Trailer removed......."
      count = attributes.count
      $logger.info "............Count calculated #{count} ........."
      attributes.each_slice(50000) { |records|
        _records = initialize_records(records)
        _records.each { |record| 
          record.save 
          index += 1 
          $logger.info "Inserting ...... [#{index}/#{count}]"
       }
     }
     $logger.info "Completed insertion of #{count}" 
   end
 end

Tested this with

Ruby-2.2.2  -  ActiveRecord-4.2.6  - pg-0.18.0 
Ruby-2.3.0  -  ActiveRecord-4.2.6  - pg-0.18.0  
Jruby-9.0.5.0 - ActiveRecord-4.2.6 - activerecord-jdbc-adapter

Postgres version.

 PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

There is 1 difference between Ruby and JRuby output?

While both processes get stuck after they wake up from sleep

The Ruby process dies with PG::UnableToSend: SSL SYSCALL error: EOF detected error

but JRuby process gets stuck forever(doesn't die).

Not sure where the issue is, since I can't pin point at any specific library or code at this point.

Note: Works perfectly fine on a loopback interface. The postgres server in question is remote one..

score 1 · Accepted Answer · answered Apr 17 '16 at 16:38

1

Inside the data folder there is a file called postgresql.conf you have to configure it to send keepalive message to the client. Read this for more info.

Configuring postgresql.conf

Your postgresql.conf file should have a line like this tcp_keepalives_idle. If you want to send keepalive message to the client computer for every 5 minutes; Update tcp_keepalives_idle line like this

tcp_keepalives_idle = 300

Make sure to uncomment that line by removing the # mark.

answered Apr 17 '16 at 16:38

chalitha geekiyanage

6,464
5
25
32

If this is an issue with keepalive why don't I see any timeout plus it work perfectly fine on a loopback interface. I'm pretty sure the loopback connection work over *TCP socket* and **not** *unix socket* since I can sniff the packet over TCP via *tcpdump*. – Viren Apr 18 '16 at 07:02
this is a good answer, was going to suggest checking out Java socket timeout. can not explain why it works using **lo**, but maybe its handling so-timeout differently. have seen stale DB connection sockets with JRuby before, although never localhost so it would be interesting if you could confirm test and confirm what's going on (with timeouts). – kares Apr 19 '16 at 11:15

idle connection in postgres causing process to stuck or result in error

1 Answers1