> 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.
It is my understanding that nexval and even currentval are safe across transactions or even user sessions. I was curious of the datatype for pin, in the previous example I think that it was defined as a varchar. Perhaps casting the sequence to a varchar would be the finial step before updating/inserting the records. Regards, Richard Broersma Jr. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend