Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Rails 4.2 engine reference column migration fails PG::UndefinedTable: ERROR: in postgresql

Ask Question

In an Engine I have built , I have 3 classes: books, categories and authors. The migrations for each of the classes is below. There is a 1 to many relationship between authors and books and between categories and books. The engine namespaces each of the classes to book_store . When using the same migrations on Postgresql (instead of sqlite) I get an error that one of the classes does not exist PG::UndefinedTable: ERROR: relation "authors" does not exist

I'm not sure why this error is occurring. Should the namespace be in the reference as well? e.g, from this:

  t.references :author, index: true, foreign_key: true
  t.references :category, index: true, foreign_key: true
to this:
  t.references :book_store_author, index: true, foreign_key: true
  t.references :book_store_category, index: true, foreign_key: true
This seems like it would not work because then there would be an attribute in BookStore::Books named book_store_author and book_store_category resulting in BookStore::Books.book_store_author which would be not scoped to the engine properly. 
Is it possible that I need to change my migration code to reflect the namespace of the engine on a separate line?
Original code
Authors
# This migration comes from book_store (originally 20150814153615)
class CreateBookStoreAuthors < ActiveRecord::Migration
  def change
    create_table :book_store_authors do |t|
      t.string :name
      t.text :description
      t.string :slug
      t.timestamps null: false
    add_index :book_store_authors, :slug, unique: true
categories
# This migration comes from book_store (originally 20150814153710)
class CreateBookStoreCategories < ActiveRecord::Migration
  def change
    create_table :book_store_categories do |t|
      t.string :title
      t.text :description
      t.string :slug
      t.timestamps null: false
    add_index :book_store_categories, :slug, unique: true
books
# This migration comes from book_store (originally 20150814153733)
class CreateBookStoreBooks < ActiveRecord::Migration
  def change
    create_table :book_store_books do |t|
      t.string :title
      t.string :lead
      t.text :excerpt
      t.text :description
      t.decimal :price
      t.integer :cover_type
      t.integer :num_pages
      t.string :isbn
      t.integer :year
      t.string :buy_link
      t.string :size
      t.string :cover_image
      t.string :slug
      t.references :author, index: true, foreign_key: true
      t.references :category, index: true, foreign_key: true
      t.timestamps null: false
    add_index :book_store_books, :slug, unique: true
Error:
/home/deploy/apps/saturnalia_books/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/migration.rb:797:in `migrate'
/home/deploy/apps/saturnalia_books/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/tasks/database_tasks.rb:137:in `migrate'
/home/deploy/apps/saturnalia_books/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>'
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "authors" does not exist
: ALTER TABLE "book_store_books" ADD CONSTRAINT "fk_rails_52f80cb3c5"
FOREIGN KEY ("author_id")
  REFERENCES "authors" ("id")
/home/deploy/apps/saturnalia_books/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'
/home/deploy/apps/saturnalia_books/shared/bundle/ruby/2.2.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `block in execute'
  PG::UndefinedTable: ERROR:  relation "authors" does not exist
here is the full stack trace
I resolved this issue by changing model and migration files as below.
I have blog model which has many tags through tagging model.
So I updated tagging model to look like this
module Blog
    class Tagging < ActiveRecord::Base
      belongs_to :tag, :class_name => "Blog::Tag"
      belongs_to :post, :class_name => "Blog::Post"
And I modified post model like this.
module Blog
  class Post < ActiveRecord::Base
    has_many :taggings, :class_name => "Blog::Tagging"
    has_many :tags, through: :taggings
Migration file is modified as below
class CreateBlogTaggings < ActiveRecord::Migration
 def change
   create_table :blog_taggings do |t|
    t.integer :tag_id, index: true, foreign_key: true
    t.integer :post_id, index: true, foreign_key: true
So in your case change t.references :author to t.integer :author _id and t.references :category to t.integer :category_id.
This should resolve your migration issue.
                This worked for me as well. Not sure why t.references/t.belongs_to wouldn't work...  I didn't need the class_name: "Namespace::Class", but I am using isolate_namespace, so maybe that's why?
– soupdog
                Jan 13, 2016 at 23:33
You are getting PG::UndefinedTable, so that table author that you are trying to reference can't be found my psql. And it couldn't be found because table is named book_store_authors here create_table :book_store_authors. 
I'm not sure why it was working on sqlite, but you certainly need to pass a full and valid table name as you were creating model (I mean in singular form if it is User or in plural if you in some reason was creating Users model) to t.belongs_to and t.references
This is Indeed due to PostgreSql not understanding how to make the bridge between tables when not namespaced
You can fix that by just changing your migration files.
In your Book migration file:
change
t.references :author, index: true, foreign_key: true
t.references :category, index: true, foreign_key: true
t.references :author
t.references :category
then after your line
add_index :book_store_books, :slug, unique: true
add this
add_index(:book_store_books, 'author_id', :name => 'index_books_on_author')
add_index(:book_store_books, 'category_id', :name => 'index_books_on_category')
those lines manually create index to the table book_store_books
more informations here : http://apidock.com/rails/v4.2.1/ActiveRecord/ConnectionAdapters/SchemaStatements/add_index
foreign_keys are not required but you can still add them with the same method
With that, you don't have to change anything in your application and postgresql know how to manage references between namespaced tables
The problem is with foreign_key, the rails api for adding foreign key use table name instead of model name. If you write a compounded line like this:
t.references :author, index: true, foreign_key: true
it will generate 2 separate commands: 
add_column :book_store_books, :author_id, index: true
add_foreign_key :book_store_books, :authors
the second command will cause undefined relation "authors"
Can fix by use 2 separate commands like this:
t.references :author, index: true and add_foreign_key :book_store_books, book_store_authors
So your migration file you be:
class CreateBookStoreBooks < ActiveRecord::Migration
  def change
    create_table :book_store_books do |t|
      # more lines go here
      # ...
      # bonus: you can define you slug unique index inline
      t.string :slug, index: true, unique: true
      t.references :author, index: true
      t.references :category, index: true
    add_foreign_key :book_store_books, book_store_authors, column: :author_id
    add_foreign_key :book_store_books, book_store_categories, column: :category_id
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.