DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=TNMVkO6xupWQWQP7nwttbFdoO90eBDek1ZG6gqgzRldn9vl9azjbzWtueyQ3+dN3DQ7ftM2ymWc0yuMDHzqoekYBpvqy7TgANAiwqPNx8nNZEjVFe5SqOBZeqUhX6c71vGH9YzC4JAaIvdSseFQECIJa+o9H13V21OnVK/j77Tc=
	;
Date: Mon, 6 Nov 2006 12:04:06 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: fetching unique pins in a high-transaction environment...
To: Scott Marlowe <smarlowe@g2switchworks.com>, Bobus <roblocke@gmail.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1162840903.5195.5.camel@state.g2switchworks.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <328332.90829.qm@web31805.mail.mud.yahoo.com>

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

Regards,

Richard Broersma Jr.