0

I have a database table in postgres which is used to generate receipt numbers for the company.The table has a column named receipt_number which is inserted when a row is recorded after post, all i do is check the last receipt_number and increment it by 1 to get the next receipt number,the problem is that when let say two clients pay at the same time,there are two rows recorded but they have the same receipt number,otherwise everything is fine.

Any idea of what is the cause of this...As it happens only when multiple clients make payments at the same time..

Thanks in advance

user6781731
  • 13
  • 1
  • 5
  • you do not have primary key in table – Vikram Singh Mar 06 '17 at 07:07
  • How do you check the "last receipt_number"? If that is a simple `max()` then is never going to work. Can you live with gaps in the number? Then use a sequence. –  Mar 06 '17 at 07:11
  • I have the PK which i used before,but suddenly it started to jump numbers not in a serial order and thus i decided to introduce a separate column.And you are right i used max() and it gave me the same problem. I have a separate table i call current_receipt_number of which before inserting a new record in the receipts table i increment it by one and then update it to the current number ready for the next entry – user6781731 Mar 06 '17 at 07:38

1 Answers1

0

It is due to concurrent access. When the event happens twice at the same time, both instances are getting the maximum value before the other is saved, thus the maximum value is the same. A possible solution is to make the column serial. If that is not an option, then you can have an event queue at server-side and whenever you intend to add a new record, instead of inserting it, you can add them to the queue. On the other hand a cron job or heartbeat job can periodically check the queue and if it is not empty, then execute the commands sequentially.

Community
  • 1
  • 1
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • From your comment,is it possible to alter my column to be serial because i have tried to check on that and seems i cannot alter the existing column to SERIAL... – user6781731 Mar 06 '17 at 07:49
  • @user6781731 that is because you have duplicate data. About the altering, see here http://stackoverflow.com/questions/27307835/how-to-convert-primary-key-from-integer-to-serial – Lajos Arpad Mar 06 '17 at 07:58
  • @user6781731 also you might have gaps. I believe you should stop the online application, make a temporary table where you have a serial version of the column and copy all the records there, drop your main table, recreate it with the correct structure, migrate the data back from the temporary table, remove the temporary table and restart your application. – Lajos Arpad Mar 06 '17 at 08:01