How do I troubleshoot this Heroku database error?

Question

My app did not successfully deploy to Heroku and I am getting a database error that says this:

PG::UndefinedTable: ERROR:  relation "topics" does not exist
: ALTER TABLE "blogs" ADD CONSTRAINT "fk_rails_7f5637ea0d"
FOREIGN KEY ("topic_id")
  REFERENCES "topics" ("id")

It seems to be saying that I do not have the reference of topic_id, but it is in my schema.rb file:

ActiveRecord::Schema.define(version: 20170930175841) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "blogs", force: :cascade do |t|
    t.string "title"
    t.text "body"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "slug"
    t.integer "status", default: 0
    t.bigint "topic_id"
    t.index ["slug"], name: "index_blogs_on_slug", unique: true
    t.index ["topic_id"], name: "index_blogs_on_topic_id"
  end

  create_table "comments", force: :cascade do |t|
    t.text "content"
    t.bigint "user_id"
    t.bigint "blog_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["blog_id"], name: "index_comments_on_blog_id"
    t.index ["user_id"], name: "index_comments_on_user_id"
  end

  create_table "friendly_id_slugs", force: :cascade do |t|
    t.string "slug", null: false
    t.integer "sluggable_id", null: false
    t.string "sluggable_type", limit: 50
    t.string "scope"
    t.datetime "created_at"
    t.index ["slug", "sluggable_type", "scope"], name: "index_friendly_id_slugs_on_slug_and_sluggable_type_and_scope", unique: true
    t.index ["slug", "sluggable_type"], name: "index_friendly_id_slugs_on_slug_and_sluggable_type"
    t.index ["sluggable_id"], name: "index_friendly_id_slugs_on_sluggable_id"
    t.index ["sluggable_type"], name: "index_friendly_id_slugs_on_sluggable_type"
  end

  create_table "portfolios", force: :cascade do |t|
    t.string "title"
    t.string "subtitle"
    t.text "body"
    t.text "main_image"
    t.text "thumb_image"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer "position"
  end

  create_table "skills", force: :cascade do |t|
    t.string "title"
    t.integer "percent_utilized"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.text "badge"
  end

  create_table "technologies", force: :cascade do |t|
    t.string "name"
    t.bigint "portfolio_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["portfolio_id"], name: "index_technologies_on_portfolio_id"
  end

  create_table "topics", force: :cascade do |t|
    t.string "title"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "users", force: :cascade do |t|
    t.string "name"
    t.string "email", default: "", null: false
    t.string "encrypted_password", default: "", null: false
    t.string "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer "sign_in_count", default: 0, null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.inet "current_sign_in_ip"
    t.inet "last_sign_in_ip"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "roles"
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
  end

  add_foreign_key "blogs", "topics"
  add_foreign_key "comments", "blogs"
  add_foreign_key "comments", "users"
  add_foreign_key "technologies", "portfolios"
end

How do I go about troubleshooting an error that conflicts with what I see in my schema.rb file? Is the error telling me to go into postgres and add that constraint item? If so, how do I go about doing that?

I have looked through this documentation:

https://devcenter.heroku.com/articles/postgres-logs-errors

but could not find anything remotely helpful to this case. I also reviewed this documentation:

PG::UndefinedTable: ERROR: relation "..." does not exist

but I am not sure if this is the answer. If I understand the above SO doc, my topics table should be created before the blogs table, but it is not because the timestamp show that the blog was created before the topics table. When I looked into it, indeed the blogs table was created before the topics table in my migrations.

So what is the cleanest and most simple way to correct those dates so that the table is created first? Assuming this is the issue.

I have verified that the table of topics does exist locally via:

ActiveRecord::Base.connection.tables
 => ["schema_migrations", "ar_internal_metadata", "friendly_id_slugs", "skills", "portfolios", "users", "topics", "blogs", "comments", "technologies"]
2.3.3 :002 >

However when I do this:

$ psql postgres
psql (9.6.1)
Type "help" for help.

postgres=# SELECT * FROM pg_tables WHERE tablename = 'topics';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
(0 rows)

postgres=#

I realized via the help I am receiving from an SO community member that I needed to switch into the correct database and once I did that I confirmed that I have the "topics" table:

postgres=# \connect <db_name>_development
You are now connected to database "<db_name>_development" as user "danale".
<db_name>_development=# SELECT * FROM pg_tables WHERE tablename = 'topics';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | topics    | danale     |            | t          | f        | t           | f
(1 row)

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

Table topics just has to be there before you can create a FK reference to it.

General SQL tools to help diagnose the situation

First, make sure you are connected to the right database in your various tests. And in the right DB cluster (host, port)!

SELECT current_database();

Connecting with psql postgres connects to the database named "postgres". (Standard maintenance db, don't create objects there, leave it alone.)

You are certain, the table exists? Check on that - in the right database:

SELECT *
FROM   pg_tables
WHERE  tablename = 'topics';

There can be multiple tables of the same name in multiple schemas.

More:

How to check if a table exists in a given schema

And does the schema (schemaname) appear in your current search_path?

SHOW search_path;

More:

How does the search_path influence identifier resolution and the "current schema"

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 03 '17 at 00:32

Erwin Brandstetter

605,456
145
1,078
1,228

Erwin, I verified that it does exist locally via ActiveRecord::Base.connection.tables. – Oct 03 '17 at 00:37
Erwin, it looks like I do not have that table. I will post it above, please comment. The moving of the topics table before blogs did not work. – Oct 03 '17 at 00:48
@Ale: you may be connected to the wrong db ...? I added a bit more above. – Erwin Brandstetter Oct 03 '17 at 01:01
oh, thank you, you are bringing back my postgresql skillset. So I switched to the correct database and the table does exist. So what then is the issue? – Oct 03 '17 at 01:06
@Ale: I am a Postgres expert. But an amateur with Ruby. I thought I could help with tools to diagnose the situation in the DB since you first asked: `How do I go about troubleshooting an error`. Not sure how to fix your migration code. – Erwin Brandstetter Oct 03 '17 at 01:19
No worries Erwin, that's my mistake, you answered the question I initially asked. – Oct 03 '17 at 01:22

score 0 · Answer 2 · answered Oct 03 '17 at 00:33

0

The REFERENCES "topics" ("id") seems to reference to "topics" table and field "id" in it. I don't see it created on create table? Admittedly it's been years, but shouldn't you be referring to an existing column?

answered Oct 03 '17 at 00:33

Stacking For Heap

299
2
9

How do I troubleshoot this Heroku database error?

2 Answers2

General SQL tools to help diagnose the situation