Setting depth field to NOT NULL results in PG::NotNullViolation error

[mhuggins]

Hi there,

I'm trying to use this gem for the first time in my Rails project. I'm using Postgres, since that might be relevant.

The migration for my nested_set-enabled model is defined as:

class CreateComments < ActiveRecord::Migration
  def change
    create_table :comments do |t|
      t.integer :question_id, null: false
      t.integer :user_id, null: false
      t.integer :parent_id
      t.integer :lft, null: false
      t.integer :rgt, null: false
      t.integer :depth, null: false
      t.integer :children_count, null: false
      t.text :body, null: false
      t.timestamps null: false
    end

    add_index :comments, :question_id
    add_index :comments, :parent_id
    add_index :comments, :lft
    add_index :comments, :rgt
  end
end

My model is defined as:

class Comment < ActiveRecord::Base
  acts_as_nested_set dependent: :destroy, counter_cache: :children_count

  belongs_to :question, inverse_of: :comments
  belongs_to :user, inverse_of: :comments

  validates :question, presence: true
  validates :user, presence: true
  validates :body, presence: true

  scope :authored_by, ->(user) { where(user_id: user.id) }
end

And I'm trying to run this code:

user = User.first
question = Question.first
question.comments.create(user: user, body: 'testing')

Which results in this error/backtrace:

  SQL (1.6ms)  INSERT INTO "comments" ("user_id", "body", "question_id", "created_at", "updated_at", "lft", "rgt") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "id"  [["user_id", 1], ["body", "testing"], ["question_id", 2], ["created_at", "2015-02-08 23:40:33.966268"], ["updated_at", "2015-02-08 23:40:33.966268"], ["lft", 1], ["rgt", 2]]
PG::NotNullViolation: ERROR:  null value in column "depth" violates not-null constraint
DETAIL:  Failing row contains (2, 2, 1, null, 1, 2, null, null, testing, f, 0, 0, 2015-02-08 23:40:33.966268, 2015-02-08 23:40:33.966268).
: INSERT INTO "comments" ("user_id", "body", "question_id", "created_at", "updated_at", "lft", "rgt") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "id"
   (0.2ms)  ROLLBACK
ActiveRecord::StatementInvalid: PG::NotNullViolation: ERROR:  null value in column "depth" violates not-null constraint
DETAIL:  Failing row contains (2, 2, 1, null, 1, 2, null, null, testing, f, 0, 0, 2015-02-08 23:40:33.966268, 2015-02-08 23:40:33.966268).
: INSERT INTO "comments" ("user_id", "body", "question_id", "created_at", "updated_at", "lft", "rgt") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "id"
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:602:in `exec_prepared'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:602:in `block in exec_cache'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:466:in `block in log'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:460:in `log'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:601:in `exec_cache'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql_adapter.rb:585:in `execute_and_clear'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:160:in `exec_query'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:192:in `exec_insert'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/database_statements.rb:108:in `insert'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `insert'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/relation.rb:64:in `insert'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/persistence.rb:521:in `_create_record'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/counter_cache.rb:139:in `_create_record'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/attribute_methods/dirty.rb:127:in `_create_record'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activerecord-4.2.0/lib/active_record/callbacks.rb:306:in `block in _create_record'
... 63 levels...
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/railties-4.2.0/lib/rails/commands/console.rb:9:in `start'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/railties-4.2.0/lib/rails/commands/commands_tasks.rb:68:in `console'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/railties-4.2.0/lib/rails/commands/commands_tasks.rb:39:in `run_command!'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/railties-4.2.0/lib/rails/commands.rb:17:in `<top (required)>'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:274:in `require'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:274:in `block in require'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:240:in `load_dependency'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:274:in `require'
    from /Users/mhuggins/Development/interview_questions/bin/rails:8:in `<top (required)>'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:268:in `load'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:268:in `block in load'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:240:in `load_dependency'
    from /Users/mhuggins/.rvm/gems/ruby-2.2.0@interview_questions/gems/activesupport-4.2.0/lib/active_support/dependencies.rb:268:in `load'
    from /Users/mhuggins/.rvm/rubies/ruby-2.2.0/lib/ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
    from /Users/mhuggins/.rvm/rubies/ruby-2.2.0/lib/ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
    from -e:1:in `<main>'

As you can see, it's trying to insert a row without depth assigned, but the column is defined as NOT NULL, per the readme. It looks like the set_depth! callback is running until after save, which is too late to assign a value for a column that doesn't permit nulls. Is this a bug in the gem, or in the documentation?

[parndt]

Ah, yeah, that'll be a documentation problem unless you could see a way that we could figure out the depth before we save it?

[mhuggins]

I had a similar issue on the children_count field as well. I ended up working around both by assigning a default value on both columns:

t.integer :depth, null: false, default: 0
t.integer :children_count, null: false, default: 0

For performance reasons, I think it would be nice to calculate the depth prior to the insert, or more ideally, as part of the insert by adding one to the parent_id if parent_id is set. (Or at least I assume this is essentially what the Ruby code is doing without digging into it.)

I will try to take a look at this when I can grab some free time. I'll also open a pull request to address the README for the default value thing prior to closing this if that sounds reasonable.

[parndt]

Thanks, Matt!