From: "Bobus" <roblocke@gmail.com>
Subject: fetching unique pins in a high-transaction environment...
Date: 29 Oct 2006 08:36:48 -0800
Organization: http://groups.google.com
Lines: 36
Message-ID: <1162139808.031657.23340@f16g2000cwb.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
User-Agent: G2/1.0
Complaints-To: groups-abuse@google.com
Injection-Info: f16g2000cwb.googlegroups.com; posting-host=124.106.126.48;
	posting-account=OzfgeQ0AAAA5-_8F6HU0QheK-BteRp5f
To: pgsql-sql@postgresql.org

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?

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