How to use SQL group clause with FOR UPDATE SKIP LOCKED in Rails? I'd prefer to use ActiveRecord as opposed to raw queries. I have a generic model called Job, which has an attribute called client_class, which I'd like to group by. I also need to remove clients who exceed the limits set in the class variable CLIENT_LIMITS. My setup is as follows:
CLIENT_LIMITS = { CLIENT_1: 2, CLIENT_2: 3, ... }.freeze
unavailable_clients = Job.running
.group(:client_class)
.count
.select { |k, v| v >= CLIENT_LIMITS[k] }
.keys
job = Job.lock("FOR UPDATE SKIP LOCKED")
.queued
.where.not(client_class: unavailable_clients)
.sample
I get the following error:
.rbenv/versions/3.0.2/lib/ruby/gems/3.0.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `exec_params': PG::FeatureNotSupported: ERROR: FOR UPDATE is not allowed with GROUP BY clause (ActiveRecord::StatementInvalid)
.rbenv/versions/3.0.2/lib/ruby/gems/3.0.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `exec_params': ERROR: FOR UPDATE is not allowed with GROUP BY clause (PG::FeatureNotSupported)
Copyright Notice：Content Author:「kskickislk」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/74084714/how-to-use-sql-group-clause-with-for-update-skip-locked-in-rails