I need to generate a unique serial number in numerical order, but only unique for each user. For example, each user can create a new credit card and each new credit card must start at a predetermined number and increment by 1 for each new credit card for that user. Thus, credit card numbers are unique for a user but not unique for the CreditCard table. And yes, it is absolutely critical that credit card numbers are always unique for each user. http://apidock.com/rails/ActiveRecord/Locking/Pessimistic I''m using PostgreSQL. Looking through the docs it doesn''t appear that I can use either pessimistic locking or optimistic locking. Maybe I''m wrong? Is my only option to lock the table, find the highest credit card number, add 1, update the new credit card number, save, and release the table lock? Karl -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jan 31, 2011, at 9:38 PM, Karl Smith wrote:> I need to generate a unique serial number in numerical order, but only > unique for each user.if worst comes to worst, you can always use one sequence per user: http://www.postgresql.org/docs/8.1/static/sql-createsequence.html -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Karl Smith wrote in post #978730:> I need to generate a unique serial number in numerical order, but only > unique for each user. > > For example, each user can create a new credit card and each new credit > card > must start at a predetermined number and increment by 1 for each new > credit > card for that user. Thus, credit card numbers are unique for a user but > not > unique for the CreditCard table. And yes, it is absolutely critical that > credit card numbers are always unique for each user.I hope this "credit card" scenario is a contrived example. It seems to me that it would be critical for the credit card number to be unique across ALL users.> http://apidock.com/rails/ActiveRecord/Locking/Pessimistic > > I''m using PostgreSQL. > > Looking through the docs it doesn''t appear that I can use either > pessimistic > locking or optimistic locking. Maybe I''m wrong?You are correct. Row level locking is useless to prevent the duplication of values between rows due to the race condition during a SQL INSERT. A unique index (possibly across multiple fields) would prevent such duplication.> Is my only option to lock the table, find the highest credit card > number, > add 1, update the new credit card number, save, and release the table > lock?It might be possible for you to use a sequence. But, in your case you would need a separate sequence for each user if I understand you correctly. Although this might work it may not be appropriate in your case. http://www.postgresql.org/docs/8.1/static/sql-createsequence.html My concern about locking the entire table would be that if something went wrong then you might end up in a state where your entire table is stuck in a locked state. I don''t know for sure if that''s an issue with PostgreSQL, but something to consider. There may also be other considerations if you ever have a need to use more that one database backend for the purposes of scaling. I would also try to avoid having to use a "max value" query every time you needed to find the next number in sequence. You could instead create a table that contains a foreign key to your users table with the next sequence value stored there. You would then increment that value for each insert similar to how database sequences work. This way you should only have to be concerned about concurrent access to the table used for sequencing. Good luck, I hope this helps at least a little. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Monday, January 31, 2011 2:16:02 PM UTC-7, Ruby-Forum.com User wrote:> > I hope this "credit card" scenario is a contrived example. It seems to > me that it would be critical for the credit card number to be unique > across ALL users. >Yes, it''s made up illustrative purposes.> You are correct. Row level locking is useless to prevent the duplication > > of values between rows due to the race condition during a SQL INSERT. > > A unique index (possibly across multiple fields) would prevent such > duplication. >Hmm, so you are saying I could use a multi-field index (:user_id, :credit_card_number). Then, during inserts if a duplicate :credit_card_number is attempted a ActiveRecord::StatementInvalid would be generated, thus giving me an indication to try another credit_card_number. Sounds workable, but is it realistic?> > Is my only option to lock the table, find the highest credit card > > number, > > add 1, update the new credit card number, save, and release the table > > lock? > > It might be possible for you to use a sequence. But, in your case you > would need a separate sequence for each user if I understand you > correctly. Although this might work it may not be appropriate in your > case. > > http://www.postgresql.org/docs/8.1/static/sql-createsequence.html >I don''t like that, but nice to know.> My concern about locking the entire table would be that if something > went wrong then you might end up in a state where your entire table is > stuck in a locked state. I don''t know for sure if that''s an issue with > PostgreSQL, but something to consider. >Which concerns me as well.> There may also be other considerations if you ever have a need to use > more that one database backend for the purposes of scaling. > > I would also try to avoid having to use a "max value" query every time > you needed to find the next number in sequence. You could instead create > a table that contains a foreign key to your users table with the next > sequence value stored there. You would then increment that value for > each insert similar to how database sequences work. This way you should > only have to be concerned about concurrent access to the table used for > sequencing. >I like that idea. That way I''m only locking that sequence table. This way I could use row level (pessimistic) locking. So, have you are anyone else tried this? -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Karl Smith wrote in post #978748:> Hmm, so you are saying I could use a multi-field index (:user_id, > :credit_card_number). Then, during inserts if a duplicate > :credit_card_number is attempted a ActiveRecord::StatementInvalid would > be > generated, thus giving me an indication to try another > credit_card_number. > > Sounds workable, but is it realistic?Yes, this is really common in database design. There was a time when composite primary keys were common and this is how those were enforced to be unique. However, the use of the technique is not limited to composite keys.> >> http://www.postgresql.org/docs/8.1/static/sql-createsequence.html >> > I don''t like that, but nice to know.Some databases use sequences exclusively for generating primary keys. As opposed to providing auto-incrmenting column types... Just FYI.>> My concern about locking the entire table would be that if something >> went wrong then you might end up in a state where your entire table is >> stuck in a locked state. I don''t know for sure if that''s an issue with >> PostgreSQL, but something to consider. >> > Which concerns me as well. > > >> > I like that idea. That way I''m only locking that sequence table. This > way I > could use row level (pessimistic) locking. > > So, have you are anyone else tried this?Yes, I have used this technique for generating serial sequences on a number of projects. And you are correct that row level locking is used to manage concurrent access. http://www.postgresql.org/docs/8.1/static/sql-select.html#SQL-FOR-UPDATE-SHARE -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.