On Mon, 2006-11-06 at 14:04, Richard Broersma Jr wrote:
> > 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.
Well, in the original, he had an id field to go with the pin, so I
assumed that he was generating them ahead of time in that format. so,
given an id number that increments to accompany the pins, you can be
sure that by using a sequence you'll never accidentally grab the same
pin twice.
By wrapping the logic in a pl/pgsql function and using sec definer, you
can be sure that some bug in your app logic can give you the same pin
twice by working around your well thought out rules of how to get a new
one.
Note that I was using the date_used field as a kind of "taken" marker.
If it was set, then there was an error, and you should NOT do the select
pin from table where id=$var but in fact get a new sequence number and
try again. Or error out.