From: Richard Broersma Jr <rabroersma@yahoo.com>
To: 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, 6 Nov 2006 12:04:06 -0800 (PST)
Message-ID: <328332.90829.qm@web31805.mail.mud.yahoo.com> (raw)
In-Reply-To: <1162840903.5195.5.camel@state.g2switchworks.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.
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: rabroersma@yahoo.com, smarlowe@g2switchworks.com, roblocke@gmail.com
Subject: Re: fetching unique pins in a high-transaction environment...
In-Reply-To: <328332.90829.qm@web31805.mail.mud.yahoo.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