On Sun, 2006-10-29 at 10:36, Bobus wrote: > Hi, > > I posted this question to the "general" forum, but then discovered this > one which I think is more appropriate. Apologies for the cross-post. > > We are in the process of porting an application from SQL Server to > PostgresQL. > > We have a table which contains a bunch of prepaid PINs. What is the > best way to fetch the next available unique pin from the table in a > high-traffic environment with lots of concurrent requests? > > For example, our PINs table might look like this and contain thousands > of records. (FYI, the PIN numbers are generated by a third party and > loaded into the table): > > ID PIN USED_BY DATE_USED > .... > 100 1864678198 > 101 7862517189 > 102 6356178381 > .... > > 10 users request a pin at the same time. What is the easiest/best way > to ensure that the 10 users will get 10 unique pins, while eliminating > any waiting?
Best practice, to me, is to do a couple things. One, create a sequence and set it to the first available pin number. Let's say you have pins available from the number 1 to 9999. Create a default sequence, it'll start on 1. Then, select nextval('yourseqhere') and use that to fetch the pin like so: begin; select nextval('yourseqhere'); -- store in a var update pin set date_used=now() where id=$var and date_used IS NULL If date_used is not null, then someone grabbed it from you. Given that we're grabbing them using a sequence, this is unlikely, but you never know when things might go south. Otherwise you just reserved it. Then grab it: select pin from table where id=$var; commit; if a transaction fails, you might not use a pin, no big loss. Better than accidentally giving it out twice. I'd wrap what I just wrote in a simple pl/pgsql script using security definer and set the perms so ONLY the user defined function can get you a new pin. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org