Re: fetching unique pins in a high-transaction environment...

> 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?
>
> SQL Server supports the notion of a SELECT FOR UPDATE with a READPAST
> hint which tells SQL Server to skip over locked rows instead of waiting
> until the lock is lifted. This guarantees a unique pin will be
> acquired every time without hampering performance.
>
> Is there any equivalent in Postgres?
>
> Any help would be greatly appreciated...

if your pin is a kind of auto-incremented number, then postgresql equivalent functionality is
sequences or the pseudo type serial (they are really the same thing).

http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL

Regards,

Richard Broersma Jr.