(Secondary) DB disconnections with sucker-punch

[j.j.f...@gmail.com]

So I have a Rail 4.2 app, it has 2 DBs, one for Rails, one for a load of data. I have a worker which inserts data into the data DB and uses the Postgres Notify mechanism to let the application know it is finished. I then use Sucker Punch to handle the notifications (send them to Pusher in fact). Works fine under Puma, not under Passenger. The stripped down code which exhibits the behaviour is

class UpdatesJob
  include SuckerPunch::Job

  def perform
    SecondDB::Base.connection_pool.with_connection do |conn|
      pgconn = conn.raw_connection
      pgconn.async_exec 'LISTEN events'
      begin
        loop do
          pgconn.wait_for_notify do |channel, pid, json|
            logger.warn('event')
          end
        end
      rescue Exception => e
        logger.warn('loop exception %s' % e.message)
      else
        logger.warn('loop exit without exception')
      ensure
        logger.warn('ensure UNLISTEN')
        pgconn.async_exec 'UNLISTEN events'
      end
    end
  end
end

I have an UpdateJob.perform_async in a initialiser.

The behaviour under passenger is the following


  event ... event, ensure UNLISTEN

About 12 events, 5 minutes, then an exception which is swallowed by the with_connection wrapper and drops me into the ensure. Removing the with_connection allows me to catch the exception, SSL corrupt response mac or some such, turn off SSL, server closed connection.

I have no idea what is going on here, this is on real hardware, not Heroku, Debian stable but repeatable on my Ubuntu laptop.

Any idea what would be happening here?

Thanks in advance!

[Hongli Lai]

Maybe you need to reestablish the database connection after forking.
https://www.phusionpassenger.com/library/indepth/ruby/spawn_methods/#unintentional-file-descriptor-sharing

> --
> You received this message because you are subscribed to the Google Groups
> "Phusion Passenger Discussions" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to phusion-passen...@googlegroups.com.
> To post to this group, send email to phusion-...@googlegroups.com.
> Visit this group at https://groups.google.com/group/phusion-passenger.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/phusion-passenger/2fedb37b-ca20-4ef5-b18d-c9ad3b5494fc%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Phusion B.V. | Web Application deployment, scaling, and monitoring solutions

Web: http://www.phusion.nl/
E-mail: in...@phusion.nl
Chamber of commerce no: 63501007 (The Netherlands)

[j.j.f...@gmail.com]

Hongli,

Ah, on that page I see that Passenger only reestabishes the primary ActiveRecord DB,
I will investigate further -- many thanks

[Hongli Lai]

It does. But what I meant to say is that that page explains why
reestablishment is needed in the first place. Passenger takes care of
reestablishing the primary connection, so if you have secondary
connections then you need to reestablish those yourself, using the
same technique that Passenger uses to reestablish the primary.

[j.j.f...@gmail.com]

For future readers who happen across this.  I think that the basic problem was that my secondary database connection was was being shared across passenger forks, so when one of the processes terminated due to inactivity, it closed its connection and since that was shared, both connections.  The "solution" was to turn off smart forking, or rather replace it with direct forking: "PassengerSpawnMethod direct". While this fixed the terminating connection issue, it bought up another -- too many connection, each event would result in multiple calls to my event code.  And this is to be expected, when passenger is running multiple rails processes, each will be running my listen code.  Time for a refactor.  So I move the code out of the main rails execution environment and into a daemon instead, it is now independent of rails, so I can have multiple rails processes under Passenger as well as smart spawning (which save memory).

A big thank you to Hongli Lai for hints and pointers to the documentation.