Support Postgres schemas

Question

Support Postgres schemas

sobrinho opened this issue 8 months ago · 3 comments

Here's how to reproduce:

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'

  gem 'activerecord'
  gem 'pg'
  gem 'sidekiq'
  gem 'sidekiq-iteration'
end

require 'active_record'
require 'sidekiq'
require 'sidekiq-iteration'

ActiveRecord::Base.logger = Logger.new($stdout)
ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'example')

class Example < ActiveRecord::Base
  if ENV['USE_SCHEMA']
    self.table_name = 'my_schema.examples'
  else
    self.table_name = 'examples'
  end
end

class MyWorker
  include Sidekiq::Job
  include SidekiqIteration::Iteration

  def build_enumerator(cursor:)
    # This will fail with the following error:
    #
    active_record_relations_enumerator(
      Example.all,
      cursor: cursor,
      batch_size: 100,
    )
  end

  def each_iteration(relation)
    # Fake, never gets here
    relation.update_all('name = name')
  end
end

if $PROGRAM_NAME == __FILE__
  puts 'Creating schema and populating data'

  ActiveRecord::Schema.define do
    if ENV['USE_SCHEMA']
      create_schema 'my_schema'

      create_table 'my_schema.examples' do |t|
        t.string :name
      end
    else
      create_table 'examples' do |t|
        t.string :name
      end
    end
  end

  examples = []

  1_000.times do |i|
    examples << { name: "Example #{i}" }
  end

  Example.upsert_all(examples)

  MyWorker.perform_async
end

dropdb example && createdb example && ruby issue.rb
sidekiq -r ./issue.rb

It works as expected, but if you use the custom schema:

dropdb example && createdb example && USE_SCHEMA=1 ruby issue.rb
USE_SCHEMA=1 sidekiq -r ./issue.rb

2024-05-06T18:48:49.536Z pid=75141 tid=1nw1 class=MyWorker jid=ed19535e33c863fec8759a50 INFO: start
2024-05-06T18:48:49.616Z pid=75141 tid=1nw1 class=MyWorker jid=ed19535e33c863fec8759a50 elapsed=0.08 INFO: fail
2024-05-06T18:48:49.616Z pid=75141 tid=1nw1 WARN: {"context":"Job raised exception","job":{"retry":true,"queue":"default","args":[{"sidekiq_iteration":{"executions":3,"cur
sor_position":null,"times_interrupted":0,"total_time":0}}],"class":"MyWorker","jid":"ed19535e33c863fec8759a50","created_at":1715021247.0816529,"enqueued_at":1715021329.534
359,"error_message":"wrong number of arguments (given 0, expected 3; required keyword: cursor)","error_class":"ArgumentError","failed_at":1715021262.135322,"retry_count":1
,"retried_at":1715021313.603843,"trace_propagation_headers":{"sentry-trace":"3f2de66ea9a94d699cbb60c5f32ac401-959b0d9a6a9d4787","baggage":"sentry-trace_id=3f2de66ea9a94d69
9cbb60c5f32ac401,sentry-environment=development"}}}
2024-05-06T18:48:49.616Z pid=75141 tid=1nw1 WARN: ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "my_schema"
LINE 1: ....examples.id FROM "my_schema"."examples" ORDER BY "my_schema...
                                                             ^

2024-05-06T18:48:49.616Z pid=75141 tid=1nw1 WARN: /Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:894
:in `exec_params'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:894:in `block (2 levels) in exec_no_cache'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:1028:in `block in with_raw_connection'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activesupport-7.1.3.2/lib/active_support/concurrency/null_lock.rb:9:in `synchronize'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:1000:in `with_raw_connection'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:893:in `block in exec_no_cache'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activesupport-7.1.3.2/lib/active_support/notifications/instrumenter.rb:58:in `instrument'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:1143:in `log'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:892:in `exec_no_cache'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:872:in `execute_and_clear'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:64:in `internal_exec_query'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/abstract/database_statements.rb:630:in `select'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/abstract/database_statements.rb:71:in `select_all'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/abstract/query_cache.rb:115:in `select_all'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/relation/calculations.rb:313:in `block in pluck'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/relation.rb:1018:in `skip_query_cache_if_necessary'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/relation/calculations.rb:309:in `pluck'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/active_record_enumerator.rb:122:in `pluck_columns'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/active_record_enumerator.rb:94:in `block in next_batch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/connection_adapters/abstract/query_cache.rb:83:in `uncached'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/query_cache.rb:21:in `uncached'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/relation/delegation.rb:122:in `public_send'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/relation/delegation.rb:122:in `block in method_missing'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/relation.rb:929:in `_scoping'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/relation.rb:467:in `scoping'
/Users/sobrinho/.gem/ruby/3.3.0/gems/activerecord-7.1.3.2/lib/active_record/relation/delegation.rb:122:in `method_missing'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/active_record_enumerator.rb:89:in `next_batch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/active_record_enumerator.rb:66:in `block in relations'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/iteration.rb:179:in `each'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/iteration.rb:179:in `each'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/iteration.rb:179:in `iterate_with_enumerator'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/iteration.rb:161:in `block in interruptible_perform'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/iteration.rb:160:in `catch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/iteration.rb:160:in `interruptible_perform'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-iteration-0.3.0/lib/sidekiq_iteration/iteration.rb:83:in `perform'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:210:in `execute_job'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:180:in `block (4 levels) in process'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/middleware/chain.rb:180:in `traverse'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/middleware/chain.rb:183:in `block in traverse'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/metrics/tracking.rb:26:in `track'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/metrics/tracking.rb:126:in `call'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/middleware/chain.rb:182:in `traverse'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/middleware/chain.rb:173:in `invoke'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:179:in `block (3 levels) in process'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:140:in `block (6 levels) in dispatch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/job_retry.rb:113:in `local'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:139:in `block (5 levels) in dispatch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/config.rb:33:in `block in <class:Config>'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:135:in `block (4 levels) in dispatch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:271:in `stats'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:130:in `block (3 levels) in dispatch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/job_logger.rb:13:in `call'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:129:in `block (2 levels) in dispatch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/job_retry.rb:80:in `global'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:128:in `block in dispatch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/job_logger.rb:39:in `prepare'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:127:in `dispatch'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:178:in `block (2 levels) in process'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:177:in `handle_interrupt'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:177:in `block in process'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:176:in `handle_interrupt'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:176:in `process'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:82:in `process_one'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/processor.rb:72:in `run'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/component.rb:10:in `watchdog'
/Users/sobrinho/.gem/ruby/3.3.0/gems/sidekiq-7.2.2/lib/sidekiq/component.rb:19:in `block in safe_thread'

Answer 1 · 2024-05-06T19:04:01.000Z

I was able to workaround by explicitly adding the columns option:

    active_record_relations_enumerator(
      Example.all,
      columns: [:id],
      cursor: cursor,
      batch_size: 100,
      order: :desc
    )

That way it works with/without the custom schema.

Answer 2 · 2024-05-06T19:19:19.000Z

Thanks for reporting! This gem is about to be merged into sidekiq itself soon. I am going to do some updates to it before that, also fixing all the opened issues.

Answer 3 · 2024-05-10T17:32:59.000Z

Fixed on master, thanks!