From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Bobus <roblocke@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: fetching unique pins in a high-transaction environment...
Date: Mon, 06 Nov 2006 13:21:43 -0600
Message-ID: <1162840903.5195.5.camel@state.g2switchworks.com> (raw)
In-Reply-To: <1162139808.031657.23340@f16g2000cwb.googlegroups.com>
References: <1162139808.031657.23340@f16g2000cwb.googlegroups.com>
On Sun, 2006-10-29 at 10:36, Bobus wrote:
> 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?
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, roblocke@gmail.com
Subject: Re: fetching unique pins in a high-transaction environment...
In-Reply-To: <1162840903.5195.5.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox