ActiveRecord raising duplicate key error on simple create

Question

I am getting a uniqueness violation for a reason I do not understand.

I have a table for tracking Prices of various Equity objects. I create or update a price for a given equity on a given date like this:

p = Price.where(equity_id: eq.id, date: date).first_or_create

When I run this, ActiveRecord is throwing this exception:

ActiveRecord::RecordNotUnique Exception: PG::UniqueViolation: ERROR:
duplicate key value violates unique constraint "prices_pkey" DETAIL: Key
(id)=(4273819) already exists.
: INSERT INTO "prices" ("created_at", "date", "equity_id", "updated_at")
VALUES ($1, $2, $3, $4) RETURNING "id"

Even though my code does nothing to specify what the primary key for the Price object should be, ActiveRecord seems to insist on re-using an existing id---in this case, 4273819.

If it helps, here is the rather lengthy traceback:

from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:780:in `exec_cache'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:139:in `block in exec_query'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:425:in `block in log'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activesupport-4.0.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:420:in `log'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:183:in `exec_insert'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:96:in `insert'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `insert'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/relation.rb:76:in `insert'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/persistence.rb:498:in `create_record'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/attribute_methods/dirty.rb:78:in `create_record'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/callbacks.rb:303:in `block in create_record'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activesupport-4.0.0/lib/active_support/callbacks.rb:373:in `_run__2672223936792095331__create__callbacks'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activesupport-4.0.0/lib/active_support/callbacks.rb:80:in `run_callbacks'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/callbacks.rb:303:in `create_record'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/timestamp.rb:57:in `create_record'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/persistence.rb:466:in `create_or_update'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/callbacks.rb:299:in `block in create_or_update'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activesupport-4.0.0/lib/active_support/callbacks.rb:383:in `_run__2672223936792095331__save__callbacks'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activesupport-4.0.0/lib/active_support/callbacks.rb:80:in `run_callbacks'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/callbacks.rb:299:in `create_or_update'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/persistence.rb:106:in `save'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/validations.rb:51:in `save'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/attribute_methods/dirty.rb:32:in `save'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/transactions.rb:270:in `block (2 levels) in save'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/transactions.rb:326:in `block in with_transaction_returning_status'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:202:in `block in transaction'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:210:in `within_new_transaction'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:202:in `transaction'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/transactions.rb:209:in `transaction'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/transactions.rb:323:in `with_transaction_returning_status'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/transactions.rb:270:in `block in save'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/transactions.rb:281:in `rollback_active_record_state!'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/transactions.rb:269:in `save'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/protected_attributes-1.0.3/lib/active_record/mass_assignment_security/persistence.rb:46:in `create'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/relation.rb:121:in `block in create'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/relation.rb:270:in `scoping'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/relation.rb:121:in `create'
from /home/ded/.rvm/gems/ruby-2.0.0-p353@s16/gems/activerecord-4.0.0/lib/active_record/relation.rb:133:in `first_or_create'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:130:in `block (3 levels) in digest_xch'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:96:in `each'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:96:in `block (2 levels) in digest_xch'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:95:in `open'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:95:in `block in digest_xch'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:87:in `chdir'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:87:in `digest_xch'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:69:in `block in digest'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:68:in `each'
from /home/ded/src/s16-o-r/app/models/price_retriever.rb:68:in `digest'
from /home/ded/src/s16-o-r/app/models/runner.rb:164:in `digest'
from /home/ded/src/s16-o-r/app/models/runner.rb:85:in `run'
from /home/ded/src/s16-o-r/app/models/runner.rb:54:in `block in catch_up'
from /home/ded/src/s16-o-r/app/models/runner.rb:39:in `each'
from /home/ded/src/s16-o-r/app/models/runner.rb:39:in `catch_up'
from script/run.rb:15:in `<main>'

Answer 1

For Postgres and Rails 5, re-sync the database by putting the following in a rake task:

ActiveRecord::Base.connection.tables.each do |t|
  ActiveRecord::Base.connection.reset_pk_sequence!(t)
end

Answer 2

Sounds like your Database is out of sync, it handles the ID assignments unless being set explicitly. Refer to the following answer on how to re-sync it.

How to reset postgres' primary key sequence when it falls out of sync?