Squeel request and Heroku, Postgres: Error using GROUP BY and ORDER

Question

I am developing my application with mysql but I am using Heroku to deploy it and am forced to use PG.

I have an issue with the following statement:

<% current_user_savings = Saving.where{user_id == my{current_user}} %>      

<% @latest_savings =  Saving.where{product_id.not_in(current_user_savings.select{product_id})}.group{product_id} %>

So it work on my computer but when deploying to heroku I have the following issue :

ActionView::Template::Error (PGError: ERROR:  column "savings.id" must appear in the GROUP      BY clause or be used in an aggregate function
LINE 1: SELECT "savings".* FROM "savings"  WHERE "savings"."product_...
SELECT "savings".* FROM "savings"  WHERE "savings"."product_id" NOT IN (SELECT "savings"."product_id" FROM "savings"  WHERE "savings"."user_id" = 1) GROUP BY "savings"."product_id"):

I really have no idea on how to fix that and make it work on heroku.

Gemfile as requested : source 'https://rubygems.org'

ruby '1.9.3'
gem 'rails', '3.2.7'
gem 'compass_twitter_bootstrap', '2.0.3'
gem 'bcrypt-ruby', '3.0.1'
gem 'faker', '1.0.1'
gem 'will_paginate', '3.0.3'
gem 'bootstrap-will_paginate', '0.0.6'
gem 'omniauth-facebook', '1.4.0'
gem 'railroady'
gem 'devise', '2.1.2'
gem 'devise_invitable'
gem 'simple_form'
gem "mongoid", "~> 3.0.0"
gem 'thin'
gem 'best_in_place'
gem "jquery-fileupload-rails"
gem 'paperclip'
gem "squeel"
gem 'client_side_validations'
gem 'wicked'
gem 'koala'
gem 'aws-sdk'
gem 'bson_ext'


group :development, :test do
    gem 'sqlite3'
end
group :development, :test do
    gem 'annotate', '2.5.0'
end

group :test, :development do
    gem 'rspec-rails'
end

group :assets do
  gem 'sass-rails',   '~> 3.2.3'
  gem 'compass-rails', '1.0.3'
  gem 'coffee-rails', '~> 3.2.1'
  gem 'uglifier', '>= 1.0.3'
end

gem 'jquery-rails'
gem 'jquery-ui-rails'

group :test do
    gem 'capybara'
    gem 'factory_girl_rails'
end

group :production do
    gem 'pg', '0.12.2'
end

I think some of this code belongs in a scope or at least in your controller. But that's besides the point, can you post your Gemfile? In my opinion, the best course of action is to use the same database and ORM in development. Have you tried to run PG locally? — JP Silvashy, Oct 30 '12 at 18:14
My Gemfile is now posted and I have not tried to run PG locally yet I will do it, thanks — Jeremy B, Oct 30 '12 at 18:37

score 1 · Answer 1 · answered Oct 30 '12 at 19:35

I think some of this code belongs in a scope or at least in your controller. But that's besides the point, can you post your Gemfile?

In my opinion, the best course of action is to use the same database and ORM in development. Have you tried to run PG locally?

It looks like a few things might be going on here that are giving you a tough time, firstly get PG up and running locally on your development machine, and update your databases.yml to coincide.

Secondly, are you using MongoBD as well? Maybe remove the gem from the bundle if you're no longer using it.

I cleaned up your Gemfile a little to make it a bit easier to read, but I'd suggest grouping the gems in sections that better represent the gem's role in the project, I like to document each gem so that it's clear what it does, oftentimes rails apps have a tendency to end out with a huge Gemfile, this makes it really hard to pickup someone else's project:

source 'https://rubygems.org'
ruby '1.9.3'

gem 'rails', '3.2.7'

gem 'compass_twitter_bootstrap', '2.0.3'
gem 'bcrypt-ruby', '3.0.1'
gem 'faker', '1.0.1'
gem 'will_paginate', '3.0.3'
gem 'bootstrap-will_paginate', '0.0.6'
gem 'omniauth-facebook', '1.4.0'
gem 'railroady'
gem 'devise', '2.1.2'
gem 'devise_invitable'
gem 'simple_form'
gem 'best_in_place'
gem "jquery-fileupload-rails"
gem 'paperclip'
gem "squeel"
gem 'client_side_validations'
gem 'wicked'
gem 'koala'
gem 'aws-sdk'

gem 'pg', '0.12.2'

gem 'jquery-rails'
gem 'jquery-ui-rails'

group :development, :test do
    gem 'annotate', '2.5.0'
    gem 'rspec-rails'
end

group :test do
    gem 'capybara'
    gem 'factory_girl_rails'
end

group :assets do
  gem 'sass-rails',   '~> 3.2.3'
  gem 'compass-rails', '1.0.3'
  gem 'coffee-rails', '~> 3.2.1'
  gem 'uglifier', '>= 1.0.3'
end

Lastly, look in in your application.rb, and make sure ActiveRecord is available, it seems like it must, but take a look, you probably have require "rails/all" somewhere near the top, there.

Well I tested on my computer with PG but it's still not working I have the same issue ... it seems that the request is not a valid PG request, I prefer to keep mysql for now for testing ... Thanks — Jeremy B, Oct 30 '12 at 20:03
@jaybbb That's a *terrible* idea if you're going to be deploying to PostgreSQL. You'll just accumulate more problems. You need to be developing and testing on PostgreSQL if you plan to deploy to PostgreSQL. — Craig Ringer, Oct 31 '12 at 00:17
Well I know that, but even with PostGreSQL in dev and testing it's not working ... I am planing to move everything to mongo asap but need that query working in the meantime ... — Jeremy B, Oct 31 '12 at 00:38
@jaybbb If you think MongoDB will magically solve your data management challenges, you're in for an interesting surprise. — Craig Ringer, Oct 31 '12 at 12:02
Actually no, I have a part of my application that use mongoDB (for a feed) I was thinking of migrating everything to mongoDB but if postgreSQL is better I will stick with it — Jeremy B, Oct 31 '12 at 13:11

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

GROUP BY is a known pain point when users are migrating from MySQL to PostgreSQL.

As @rs notes, your SQL is somewhat problematic.

If you're grouping by the primary key

Assuming that savings.product_id is the primary key since that's the only way this query could be correct, it relies on the database knowing that grouping by the primary key implicitly groups all fields because the primary key is unique.

MySQL doesn't know that, it just goes ahead and picks the first result it sees for each field. In this case that'll be correct because you're grouping by the primary key, but it can easily produce unpredictable results in other circumstances.

PostgreSQL 9.0 and below do not recognise that grouping by the primary key means you've grouped all fields, despite the SQL-99 standard requiring it to do so. Those versions require you to list all the fields you want to SELECT in the GROUP BY. This is clumsy, so the best thing you can do is upgrade to PostgreSQL 9.1 or 9.2, which are no longer subject to this limitation.

Dealing with this is made more complicated by the fact that you're using a query generator. You can't just modify your SQL. If you can't upgrade PostgreSQL you need to work out how to convince your query generator to GROUP BY all the columns in the table.

If you're grouping by some other field

If savings.product_id is not the primary key, your query is just plain wrong. You must list every field you want in the SELECT list in the GROUP BY to guarantee a unique and unambiguous result, or only refer to the field within an aggregate function.

MySQL will execute this query anyway, but it relies on you knowing that the results will be unambiguous. If there are multiple possible values for a field it'll just pick whatever one it gets to first.

It's hard to work out what your query is actually supposed to do and what the results you expect from it are in this case.

edited May 23 '17 at 11:55

Community

1
1

answered Oct 31 '12 at 00:11

Craig Ringer

307,061
76
688
778

Is there a chance to fix the query so that it works on heroku with PostGreSQL ? – Jeremy B Oct 31 '12 at 00:15
@jaybbb What PostgreSQL version are you on at Heroku? I thought they ran 9.1, which shouldn't be subject to this issue. Anyway, to fix the query you must `GROUP BY` all the columns you wish to `SELECT`. How to do that with your query generator ... no idea. – Craig Ringer Oct 31 '12 at 00:17
@jaybbb Also, is `savings.product_id` the primary key? If it isn't, your query is just plain wrong. – Craig Ringer Oct 31 '12 at 00:19
the primary key is the id of the saving. but I get the correct result when I do it with mysql. thanks – Jeremy B Oct 31 '12 at 00:37
@chris ringer do you know how to do it with the regular rails sql expression by anychance ? – Jeremy B Oct 31 '12 at 00:53
I just checked on the PG version on heroku is 9.1.6 so I don't really know where is the issue ... – Jeremy B Oct 31 '12 at 09:36
@jaybbb by "The id of the saving" do you mean `savings.product_id`? I'm guessing you mean that a column like `savings.id` or `savings.savings_id` is the primary key, so `savings.product_id` is *NOT* the primary key. In that case the SQL is wrong and won't work on anything except MySQL. I'm struggling to work out what your query is actually supposed to do so I can't really help you further. Try posting a new question that explains *in detail* what the query you're trying to write is *supposed* to do, with your models, sample data, and expected results. Then link to it here. *be specific*. – Craig Ringer Oct 31 '12 at 09:42

score 1 · Accepted Answer · answered Nov 01 '12 at 10:55

Thank you all for your help I ended up doing that and it's working on PG in local !

Saving.select("DISTINCT ON (savings.product_id) * ").where{product_id.not_in(current_user_savings.select{product_id})}.group("savings.user_id, savings.updated_at, savings.id, savings.product_id, savings.price,savings.wishlist_id, savings.saved, savings.created_at")

Having that issue made me switch my db from mysql to pg in dev so no more surprise in heroku !

score 0 · Answer 4 · answered Oct 30 '12 at 18:16

0

Your SQL will not work in PG, MySQL will allow you to do that but not any other DBMS

This query:

SELECT "savings".* FROM "savings"  
WHERE "savings"."product_id" 
NOT IN (SELECT "savings"."product_id" FROM "savings"  
         WHERE "savings"."user_id" = 1) 
GROUP BY "savings"."product_id"

Should be

SELECT "savings"."product_id", count(*) FROM "savings"  
--change this to your AGGR func
WHERE "savings"."product_id" 
NOT IN (SELECT "savings"."product_id" FROM "savings"  
         WHERE "savings"."user_id" = 1) 
GROUP BY "savings"."product_id"

answered Oct 30 '12 at 18:16

rs.

26,707
12
68
90

How should I do that with my squeel request ? I am not sure how to change it, Thanks – Jeremy B Oct 30 '12 at 18:34
Actually, some RDBMSs recognise the functional dependency on the primary key like PostgreSQL 9.1 and above do. They are *required to by SQL-99*. This has been a long standing PostgreSQL limitation fixed in 9.1. However, if `savings.product_id` is *not* the primary key, then yes the query is just plain wrong. – Craig Ringer Oct 31 '12 at 00:19
You're assuming the user wants a count; is that the case? – Craig Ringer Oct 31 '12 at 00:25
No actually I want every savings where savings.product.id is not in current_user_savings and group the result by savings.product.id – Jeremy B Oct 31 '12 at 00:37

Squeel request and Heroku, Postgres: Error using GROUP BY and ORDER

4 Answers4

If you're grouping by the primary key

If you're grouping by some other field

Linked