CODEWITHSUNDEEP
ruby-on-railspostgresqlmigrationauto-incrementdatabase-migration
Cfrim
Cfrim

Reputation: 980

Add auto increment with scope to existing column in migration-file rails

I have posts and organisations in my database. Posts belongs_to organisation and organisation has_many posts.

I have an existing post_id column in my post table which I by now increment manually when I create a new post. How can I add auto increment to that column scoped to the organisation_id?

Currently I use mysql as my database, but I plan to switch to PostgreSQL, so the solution should work for both if possible :)

Thanks a lot!

Upvotes: 4

Views: 2918

Answers (4)

hakunin
hakunin

Reputation: 4231

Its good to know how to implement it. I would prefer to use a gem myself.

Upvotes: 1

Homan
Homan

Reputation: 26748

@richard-huxton has the correct answer and is thread safe.

Use a transaction block and use SELECT FOR UPDATE inside that transaction block. Here is my rails implementation. Use 'transaction' on a ruby class to start a transaction block. Use 'lock' on the row you want to lock, essentially blocking all other concurrent access to that row, which is what you want for ensuring unique sequence number.

class OrderFactory
  def self.create_with_seq(order_attributes)
    order_attributes.symbolize_keys!
    raise "merchant_id required" unless order_attributes.has_key?(:merchant_id)
    merchant_id = order_attributes[:merchant_id]

    SequentialNumber.transaction do
      seq = SequentialNumber.lock.where(merchant_id: merchant_id, type: 'SequentialNumberOrder').first
      seq.number += 1
      seq.save!
      order_attributes[:sb_order_seq] = seq.number
      Order.create(order_attributes)
    end
  end
end

We run sidekiq for background jobs, so I tested this method by creating 1000 background jobs to create orders using 8 workers with 8 threads each. Without the lock or the transaction block, duplicate sequence number occur as expected. With the lock and the transaction block, all sequence numbers appear to be unique.

Upvotes: 5

Richard Huxton
Richard Huxton

Reputation: 22943

OK - I'll be blunt. I can't see the value in this. If you really want it though, this is what you'll have to do.

Firstly, create a table org_max_post (org_id, post_id). Populate it when you add a new organisation (I'd use a database trigger).

Then, when adding a new post you will need to:

  1. BEGIN a transaction
  2. SELECT FOR UPDATE that organisation's row to lock it
  3. Increment the post_id by one, update the row.
  4. Use that value to create your post.
  5. COMMIT the transaction to complete your updates and release locks.

You want all of this to happen within a single transaction of course, and with a lock on the relevant row in org_max_post. You want to make sure that a new post_id gets allocated to one and only one post and also that if the post fails to commit that you don't waste post_id's.

If you want to get clever and reduce the SQL in your application code you can do one of:

  1. Wrap the hole lot above in a custom insert_post() function.
  2. Insert via a view that lacks the post_id and provides it via a rule/trigger.
  3. Add a trigger that overwrites whatever is provided in the post_id column with a correctly updated value.

Deleting a post obviously doesn't affect your org_max_post table, so won't break your numbering.

Prevent any updates to the posts at the database level with a trigger. Check for any changes in the OLD vs NEW post_id and throw an exception if there is one.

Then delete your existing redundant id column in your posts table and use (org_id,post_id) as your primary key. If you're going to this trouble you might as well use it as your pkey.

Oh - and post_num or post_index is probably better than post_id since it's not an identifier.

I've no idea how much of this will play nicely with rails I'm afraid - the last time I looked at it, the database handling was ridiculously primitive.

Upvotes: 4

Vinicius
Vinicius

Reputation: 123

First, I must say this is not a good practice, but I will only focus on a solution for your problem: You can always get the organisation's posts count by doing on your PostsController:

def create
  post = Post.new(...)
  ...
  post.post_id = Organization.find(organization_id).posts.count + 1
  post.save
  ...
end

You should not alter the database yourself. Let ActiveRecord take care of it.

Upvotes: 0

Related Questions