ProtocolViolation: ERROR: bind message supplies 0

Question

I am trying to create a list of unique patients who have left comments, in order of patient who left the most recent comment first.

This is my Ruby .erb code to create the list:

@comment_list.order("created_at desc").each_with_index do |comment, index|

@comment_list is defined in the controller as:

  @comments = current_clinician.comments.select('ON (patient_id) *').uniq
  @comments = @comments.order("patient_id, created_at DESC")
  @comment_list = Comment.select('*').from("(#{@comments.to_sql}) sub")

I get an ActiveRecord::StatementInvalid message:

PG::ProtocolViolation: ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1 : SELECT * FROM (SELECT DISTINCT ON (patient_id) * FROM "comments" WHERE "comments"."clinician_id" = $1 ORDER BY patient_id, created_at DESC) sub ORDER BY created_at desc

I tried to follow the answer on 24619117 and my output is a combination of this and the answer top 29660396.

$ rails -v
Rails 4.1.8
$ ruby -v
ruby 2.2.1p85 (2015-02-26 revision 49769) [x86_64-darwin14]
$ psql --version
psql (PostgreSQL) 9.4.1

I am inexperienced with PostgresSQL and part of the problem is that I am using Ruby on Rails to get SQL and the methods are not straightforward. I have been using http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-from

Suggestions please

Answer 1

In your case it seems that because you are using the @comments.to_sql you are pulling that prepared statement into your subselect without bringing in the parameter for it. You can try just including the comment data like this:

  @comments = current_clinician.comments.select('ON (patient_id) *').uniq.order("patient_id, created_at DESC").include(:comment)
  @comment_list = @comments.include(:comment)

This issue also seems to come from the way that the prepared statements are built in Rails and could be caused by either issues within Rails itself (Rails issue #15920, which has been fixed in Rails 4.2) or by issues with various gems that help to generate queries (example: Rails issue #20236) or even by the way you define your model associations (Rails issues #12852).

It is possible to just outright disable prepared statements by adding a directive to your database.yml file:

production:
  adapter: postgresql
  database: prod_dbname
  username: prod_user
  password: prod_pass
  prepared_statements: false

But first, you might want to check and make sure you aren't using unnecessary parameters in your model associations like this:

class DashboardTab < ActiveRecord::Base
  has_many :dashboard_tab_feeds, foreign_key: :dashboard_tab_id, dependent: :destroy
  has_many :social_feeds, through: :dashboard_tab_feeds
end

class DashboardTabFeed < ActiveRecord::Base
  belongs_to :social_feed
  belongs_to :dashboard_tab
end

class SocialFeed < ActiveRecord::Base
  has_many :dashboard_tab_feeds, foreign_key: :social_feed_id, dependent: :destroy
end

...which should just leave out foreign_key, like this:

class DashboardTab < ActiveRecord::Base
  has_many :dashboard_tab_feeds, dependent: :destroy
  has_many :social_feeds, through: :dashboard_tab_feeds
end

class DashboardTabFeed < ActiveRecord::Base
  belongs_to :social_feed
  belongs_to :dashboard_tab
end

class SocialFeed < ActiveRecord::Base
  has_many :dashboard_tab_feeds, dependent: :destroy
end