How to make auto reconnect or retry execution?

[fbayaffo2097]

Hi, I'm having my application crash at some point with “Sequel::DatabaseDisconnectError”, I couldn't find is there any option here to do a retry for the query? I already use connection_validator once every 30 minutes but I don't want to use it before every connection with option (-1), is there any mechanism when Sequel::DatabaseDisconnectError make a new connection and execute the query and try it say 4 times if not then throw the error?

I also understand that connection_validator will not help me in the situation if, for example, connection to the pool was established 20 minutes ago, then I execute a query => I get Sequel::DatabaseDisconnectError and will continue to get it until another 10 minutes pass and connection_validator works to recheck connections?

Update: I use pgbouncer (I can't give it up because it's forbidden in cloud service)

[fbayaffo2097]

I also understand that connection_validator will not help me in the situation if, for example, connection to the pool was established 20 minutes ago, then I execute a query => I get Sequel::DatabaseDisconnectError and will continue to get it until another 10 minutes pass and connection_validator works to recheck connections?

[fbayaffo2097]

@jeremyevans I searched the questions and couldn't find anything similar, except for the recommendation using -1, but that doesn't work in my case, nor does rechecking connections frequently, since this error occurs rarely during the day, and checking connections frequently will affect performance in terms of response latency. I look forward to your help 🙏

[fbayaffo2097]

I could see that happening if you were getting a non-DatabaseDisconnectError message, or you were rescuing the DatabaseDisconnectError and not reraising it, before it was handled by the connection pool. Otherwise, I don't have an idea why. If you can post a minimal self-contained example showing the problem, I'll take a look.

This is top level stack trace:

PG::ConnectionBad: PQconsumeInput() server closed the connection unexpectedly (PG::ConnectionBad)
	This probably means the server terminated abnormally
	before or while processing the request.

  from sequel/adapters/postgres.rb:171:in `exec'
  from sequel/adapters/postgres.rb:171:in `block in execute_query'
  from sequel/database/logging.rb:43:in `log_connection_yield'
  from elastic_apm/spies/sequel.rb:68:in `log_connection_yield'
  from sequel/adapters/postgres.rb:171:in `execute_query'
  from sequel/adapters/postgres.rb:159:in `block in execute'
  from sequel/adapters/postgres.rb:136:in `check_disconnect_errors'
  from sequel/adapters/postgres.rb:159:in `execute'
  from sequel/adapters/postgres.rb:638:in `log_connection_execute'
  from sequel/database/transactions.rb:357:in `begin_new_transaction'
  from sequel/adapters/shared/postgres.rb:1168:in `begin_new_transaction'
  from sequel/database/transactions.rb:366:in `begin_transaction'
  from sequel/database/transactions.rb:254:in `_transaction'
  from sequel/database/transactions.rb:239:in `block in transaction'
  from sequel/connection_pool/sharded_threaded.rb:132:in `hold'
  from sequel/database/connecting.rb:283:in `synchronize'
  from sequel/database/transactions.rb:197:in `transaction'
  from sequel/model/base.rb:1954:in `checked_transaction'
  from sequel/model/base.rb:1500:in `block in save'
  from sequel/model/base.rb:1940:in `checked_save_failure'
  from sequel/model/base.rb:1500:in `save'
  from sequel/model/base.rb:254:in `create'

I don't really understand why it didn't catch the mistake at this:
from sequel/adapters/postgres.rb:136:in `check_disconnect_errors'

[fbayaffo2097]

Also I use Sentry in my project:

use Sentry::Rack::CaptureExceptions in my roda.rb,

and in my routes.rb I rescue any errors to sentry:

      hash_branch 'welcome' do |r|
            .....#here is list of my routes
            .....#Further on, the code calls the service object to create a record in the database
      rescue StandardError => e
        Sentry.capture_exception(e)
      end

[jeremyevans]

That's PG::ConnectionBad, not Sequel::DatabaseDisconnectError, assuming that is the entire error. I'm guessing it is not the entire error, maybe it is only showing the wrapped error. check_disconnect_errors will convert PG::ConnectionBad to Sequel::DatabaseDisconnectError. And hold in the connection pool will remove the related connection from the pool if a Sequel::DatabaseDisconnectError is raised.

In order for me to look into this, you'll need to provide a minimal self-contained example showing the problem.

[fbayaffo2097]

That's PG::ConnectionBad, not Sequel::DatabaseDisconnectError,

For some reason, the output in Sentry is strange.... There are two errors here:

(but if I switch to ‘raw fulltrace’ mode, the error stack is as I wrote above)

[jeremyevans]

That's expected, as Sequel::DatabaseDisconnectError wraps PG::ConnectionBad. Do you have evidence that the related connection is not being removed from the pool after the Sequel::DatabaseDisconnectError is raised? You could use a Database logger and set DB.log_connection_info = true to determine that.