PG:Error relation does not exist

Question

I am using Rails 3.2.x and also using RailsAdmin.

I have run many migrations and changed my DB structure many times. As a result of that, I am getting a PG:Error saying that a relation doesn't exist for a table that really doesn't exist.

This is the error:

ActiveRecord::StatementInvalid at /
PG::Error: ERROR:  relation "addresses" does not exist
LINE 5:              WHERE a.attrelid = '"addresses"'::regclass
                                        ^
:             SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod

So in short, when I go to /admin the error shown in my development.log is this:

Started GET "/admin" for 127.0.0.1 at 2013-07-04 02:24:07 -0500
Processing by RailsAdmin::MainController#dashboard as HTML
  Cart Load (0.5ms)  SELECT "carts".* FROM "carts" WHERE "carts"."id" = $1 LIMIT 1  [["id", 5]]
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
   (0.6ms)  SELECT COUNT(*) FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."user_id" = 1 AND (((roles.name = 'admin') AND (roles.resource_type IS NULL) AND (roles.resource_id IS NULL)))
   (0.6ms)  SELECT COUNT(*) FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."user_id" = 1 AND (((roles.name = 'seller') AND (roles.resource_type IS NULL) AND (roles.resource_id IS NULL)))
PG::Error: ERROR:  relation "addresses" does not exist
LINE 5:              WHERE a.attrelid = '"addresses"'::regclass
                                        ^
:             SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
              FROM pg_attribute a LEFT JOIN pg_attrdef d
                ON a.attrelid = d.adrelid AND a.attnum = d.adnum
             WHERE a.attrelid = '"addresses"'::regclass
               AND a.attnum > 0 AND NOT a.attisdropped
             ORDER BY a.attnum

Completed 500 Internal Server Error in 1269ms

ActiveRecord::StatementInvalid - PG::Error: ERROR:  relation "addresses" does not exist
LINE 5:              WHERE a.attrelid = '"addresses"'::regclass
                                        ^
:             SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
              FROM pg_attribute a LEFT JOIN pg_attrdef d
                ON a.attrelid = d.adrelid AND a.attnum = d.adnum
             WHERE a.attrelid = '"addresses"'::regclass
               AND a.attnum > 0 AND NOT a.attisdropped
             ORDER BY a.attnum

How do I fix this?

Apprehensive

This is in production env? – Enlightenment

This is in development, but it may happen in production too. – Apprehensive

The problem is that addresses table is missing but I can't tell whats the reason. As you said - migrations are messed up. – Enlightenment

Answer 1

What I would suggest is to drop and recrete the whole db. You probably will have some problems in migrations while doing this - fix them all so a clean install of an app is possible.

Enlightenment

That's too much risk because I used some gems, a rails engine, that doesn't have the proper migrations to get rid of it - and it is just a mess. So I know this solution is a short-term solution, but at some point in the future, I will re-write it all from scratch. But for now, how do I get around that and fix this issue? – Apprehensive

Don't mess with db? No - really. You have gems that don't provide migrations? – Enlightenment

So I installed piggybak - and while it has a generator for the up-migrations, uninstalling that gem after you have been using it for a while, and have run/generated other migrations after installing that, is a pain in the ass. Soo....I manually dropped the piggybak related tables and everything is all fubarred. – Apprehensive

Oh...and to make things more complicated, I had it installed in dev, and on Heroku. So, I can't just remove the gem from my Gemfile, I have to do something that makes sense. The issue with that is that for every new app install, I still had the old create migrations for piggybak and all other gems. So it is one big friggin mess. – Apprehensive

You can store seeded data, before dropping the database, and then when you will recreate the database structure, you can reseed the stored data. – Broderickbrodeur

Answer 2

Although the Table doesn't exist, does the model exist. Rails admin will expect a table for each model that inherits from active record. If you have a model that is not backed up by a database table, then don't do < ActiveRecord::Base, simply declare the class and Rails_admin will be happy.

Phago