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

Question

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!

1. What do you mean exactly by "auto increment ... scoped to the organisation_id"? 2. Why do you want it (caring about the precise value of artificial keys is usually not helpful)? — Richard Huxton, Jun 10 '13 at 13:37
I want the post_id to be dependent on the organisation. Let's say we have two organisations. Organisation one creates the first post: id: 1 post_id: 1 title: "Hello world" organisation_id: 1 And then organisation two creates a post: id: 2 post_id: 1 title: "Hello another world" organisation_id: 2 So the post_id will not increment when a new post is made from another organisation. That is basically the idea. And when you delete post 1 from organisation 2, the next post_id should be 2 of course. So basically the post_id will not increase depending on other organisations' posts. — Cfrim, Jun 10 '13 at 13:55
You've not addressed the "Why?" in my comment. Also, I'm not clear on whether you find holes in your numbering acceptable or not. Or what should happen if you try to update a post_id. Are you aware of the various concurrency/organisational issues with trying to do this sort of thing? The short answer to this question is "don't do that", but that isn't very helpful by itself. So - you need to explain why you want to do this so someone can suggest a better way. — Richard Huxton, Jun 10 '13 at 14:53
Thanks for your respond. First of all, yes holes in our post_ids are very acceptable. It's mandatory. Secondly, the reason why we need to do this in my company, is that we want the post_id to be absolute unique and we don't want to show the primary key (id) itself in the view as a post-indicator, because we want the organisations to be scoped. I'm not saying that we use the post_id referring to the post. There of course we use the id itself. So basically the post_id is only for the user's benefit and needs to be displayed in the view as a post indicator. — Cfrim, Jun 10 '13 at 17:06

score 5 · Answer 1 · answered Jul 15 '14 at 18:24

@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.

Richard Huxton · Answer 2 · 2013-06-10T22:07:44.527

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:

BEGIN a transaction
SELECT FOR UPDATE that organisation's row to lock it
Increment the post_id by one, update the row.
Use that value to create your post.
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:

Wrap the hole lot above in a custom insert_post() function.
Insert via a view that lacks the post_id and provides it via a rule/trigger.
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.

Thanks a lot. I think I'll go with that solution. I know it seems bad practice to do what we want, but it is a thing that we find mandatory in our system. Yes database handling can seem pretty primitive, but I really appreciate Active Records and the record associations built in ROR among other things. I don't think that this will be hard to implement. Can you tell me about the idea with a transaction in this example? I'm just curious :) — Cfrim, Jun 10 '13 at 21:46
Added some notes re: the transaction/locking and also how to wrap the process. — Richard Huxton, Jun 10 '13 at 22:08

hakunin · Answer 3 · 2017-02-20T12:30:19.323

1

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

https://github.com/austinylin/sequential (based on sequenced)
https://github.com/djreimer/sequenced
https://github.com/felipediesel/auto_increment

edited Feb 20 '17 at 12:30

answered Nov 04 '15 at 07:33

hakunin

4,041
6
40
57

Also another gem: https://github.com/djreimer/sequenced (which that one is based on). And yet another one here: https://github.com/felipediesel/auto_increment – rmcsharry Feb 20 '17 at 09:08

score 0 · Answer 4 · answered Jun 10 '13 at 14:54

0

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.

answered Jun 10 '13 at 14:54

Vinicius

123
9

Thanks for your answer :) I have come across this approach. The problem with this solution is, that we want the post_id to be absolute unique "forever", just like an auto incremented primary key. So if I have 6 posts and I delete the last one, and I create a new post, it should have post_id: 7 and not 6 like your example. So it's going to be like this: 1,2,3,4,5,7 – Cfrim Jun 10 '13 at 17:17

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

4 Answers4