CODEWITHSUNDEEP
ruby-on-railspostgresql
Lorenz
Lorenz

Reputation: 775

PG::FeatureNotSupported: ERROR: FOR UPDATE is not allowed with aggregate functions

I'm getting a PG error and I can't figure out how to rewrite this statement is a way that works. When I run it in the rails console, everything seems to be fine. But when I try to process it as a background job, it borks with the following error:

    /Users/lorenzsell/DEV/Heartbeat-pods/app/mailers/notifications_mailer.rb:44:in `community_update'
/Users/lorenzsell/DEV/Heartbeat-pods/app/jobs/send_community_digest_job.rb:17:in `block (2 levels) in perform'
/Users/lorenzsell/DEV/Heartbeat-pods/app/jobs/send_community_digest_job.rb:16:in `block in perform'
/Users/lorenzsell/DEV/Heartbeat-pods/app/jobs/send_community_digest_job.rb:8:in `perform'
/Users/lorenzsell/DEV/Heartbeat-pods/app/jobs/schedule_send_community_digest_job.rb:9:in `block in perform'
/Users/lorenzsell/DEV/Heartbeat-pods/app/jobs/schedule_send_community_digest_job.rb:8:in `each'
/Users/lorenzsell/DEV/Heartbeat-pods/app/jobs/schedule_send_community_digest_job.rb:8:in `perform'
/Users/lorenzsell/DEV/Heartbeat-pods/lib/tasks/email_tasks.rake:10:in `block (2 levels) in <top (required)>'
-e:1:in `<main>'
ActiveRecord::StatementInvalid: PG::FeatureNotSupported: ERROR:  FOR UPDATE is not allowed with aggregate functions
: SELECT COUNT(*) FROM "activities" WHERE "activities"."receiver_id" = $1 AND "activities"."is_read" = 'f' FOR UPDATE
/Users/lorenzsell/DEV/Heartbeat-pods/app/mailers/notifications_mailer.rb:44:in `community_update'

Here is the code snippet:

community_ids = Activity.where(receiver_type: "community", is_read: false).uniq.pluck(:receiver_id)

Upvotes: 1

Views: 2103

Answers (1)

ahmacleod
ahmacleod

Reputation: 4310

According to the documentation, lock causes ActiveRecord to generate a SELECT ... FOR UPDATE. Postgres won't let you perform a count on such queries (though other databases might--this is probably why it works in the console), so you need to call count on your Activity relation before you call lock.

# base query
notifications = Activity.where(receiver_id: options['id'], is_read: false)

# save count
notifications_count = notifications.count

# apply lock
notifications = notifications.lock(true)

Upvotes: 1

Related Questions