> 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.
