rails SQL query reported failing with postgres but succeeds from psql command-line tool

Question

I am trying to use a Rails 2.3.5 app with Postgres9.1 on Ubuntu (deployed on Apache2 with Phusion Passenger). The app throws this error when I try to access it, complaining that table does not exist:

PGError: ERROR:  relation "users" does not exist
LINE 4:              WHERE a.attrelid = '"users"'::regclass
                                    ^
:             SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"users"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

However if I cut and paste the same query at the psql (cmdline-client), it works just fine. The users table exists too. I tried a rake db:reset and 'rake db:migrate' again and again but it doesn't seem to help.
I also verified that the ActiveRecord::Base.connection succeeds from the script/console in the rails-appwhich means the connection to the database works with the credentials configured in database.yml.

I am lost.. any clues to remedy this will be appreciated greatly.

My bet is, that you are connecting to a different DB from within Rails — user330315
– user330315, Commented Nov 3, 2011 at 22:15
Thanks much for your response, Edwin and "a_horse..". T'was connection to wrong DB. I'd configured the app for development mode (via environment.rb). But my setting was being overridden by Phusion Passenger (which I was using with apache); it sets the RailsEnv to 'production' by default. Since I'd earlier used 'rake db:create:all', the prod DB did get created but my 'rake db:migrate' only populated the development DB. I had to change the apache site configuration to set RailsEnv development as shown here: modrails.com/documentation/…. — Don
– Don, Commented Nov 4, 2011 at 21:45
@Don: a_horse_with_no_name had his money on the right horse. Cool that it works. I do wonder about this mysterious Edwin guy, though. ;) — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 4, 2011 at 21:56

Erwin Brandstetter · Accepted Answer · 2024-05-03 19:48:32Z

1

Three possible causes:

The special cast ::regclass takes the current setting for search_path into account. Maybe your table users is in a schema that is not in the search_path when querying from your app. search_path can be set per user or session.
Solution would be to schema-qualify the table-name like this:

'myschema.users'::regclass
Capitalization. Why '"users"'::regclass and not 'users'::regclass? This is redundant. Is the actual name of the Table "Users" or something and you use upper case in psql? (If you actually cut & paste the query, this can't be it.)
Connection to the wrong database. Wrong port?

edited May 3, 2024 at 19:48

answered Nov 3, 2011 at 22:57

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Don Over a year ago

Thanks much for your response, Edwin and "a_horse..". T'was connection to wrong DB. I'd configured the app for development mode (via environment.rb). But my setting was being overridden by Phusion Passenger (which I was using with apache); it sets the RailsEnv to 'production' by default. Since I'd earlier used 'rake db:create:all', the prod DB did get created but my 'rake db:migrate' only populated the development DB. I had to change the apache site configuration to set RailsEnv development as shown here: modrails.com/documentation/….

Collectives™ on Stack Overflow

rails SQL query reported failing with postgres but succeeds from psql command-line tool

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related