Обсуждение: fetching unique pins in a high-transaction environment...
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...
I think we've figured out a way to implement the equivalent of a
READPAST hint in a function.
The basic idea is to loop until we find the next available unlocked
row, using the lock_not_available exception to determine if the record
is locked or not. Our early testing seems to indicate that this
solution will work, but we would love to hear about simpler and more
efficient ways to accomplish this.
Here's a simplified version of the function which illustrates the
principle:
CREATE OR REPLACE FUNCTION "getpin"() RETURNS varchar
as $$
DECLARE v_id integer := 0; v_pin varchar;
BEGIN LOOP BEGIN -- Find the first available PIN. -- Note: we cannot lock down the row here since we need to
be -- able to store the ID of the pin to implement the READPAST. select id into v_id from pins where id > v_id
andstatus = 0 order by id limit 1;
-- Exit if there are no PINs available. IF NOT FOUND THEN RAISE EXCEPTION 'no pins available'; END
IF;
-- Lock down the PIN. If another transaction beat us to it, we -- trap the error (see below) and loop looking
forthe next -- available pin. If another transaction already updated the -- status to 1 in between this select
andthe previous, then we -- loop (see ELSE statement). select pin into v_pin from pins where id = v_id and
status= 0 for update nowait;
IF FOUND THEN -- Update the PIN. The status = 0 check is unnecessary, -- but better safe than sorry.
update pins set status = 1 where id = v_id and status = 0;
-- I don't think this should ever happen. IF NOT FOUND THEN RAISE EXCEPTION 'this should never
happen'; END IF;
RETURN v_pin; ELSE -- Somebody snuck in and updated/grabbed the pin. Loop. END IF;
EXCEPTION WHEN lock_not_available THEN -- Loop looking for the next available unlocked pin. END; END LOOP;
END;
$$
language plpgsql;
Thanks...
Hi All. My setup is as follows: OS: Sun Solaris 5.8. Postgres: 7.2.4 I have very large database, which contain 15 tables and each table is contain more than 10,00,000 records. My application is parsing text data files and inserting records into database. When this process was running last night, machine was got down because of power failure. Today when I come back to office and I have compaired record count in data files and in database and find that some records are missing in database. Then I have checked postgres log and found log similar to as follows: ================================= DEBUG: The DataBase system was not properly shut downAutomatic recovery is in progress... DEBUG: Redo starts at (0, 1064) ================================= I have read some WAL related stuff on postgres site but not found any solution to recover those uncommited records. Can any one tell me how to recover those missing records? Thanks, Santosh
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.
> 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.
On Mon, 2006-10-30 at 04:25, Santosh wrote: > Hi All. > > My setup is as follows: > OS: Sun Solaris 5.8. > Postgres: 7.2.4 Just so you know, 7.2 is ancient. You should, at a minimum be running the latest 7.2 release, 7.2.8. You should really look into upgrading to a later version as soon as possible. > I have very large database, which contain 15 tables and each table is > contain more than 10,00,000 records. > > My application is parsing text data files and inserting records into > database. > > When this process was running last night, machine was got down because > of power failure. > > Today when I come back to office and I have compaired record count in > data files and in database and find that some records are missing in > database. You may have lost data. Hard to say from what you've told us. How did you determine that records are missing? > Then I have checked postgres log and found log similar to as follows: > ================================= > DEBUG: The DataBase system was not properly shut down > Automatic recovery is in progress... > DEBUG: Redo starts at (0, 1064) > ================================= This is normal. It's the messages after this you need to worry about. Did the logs say anything else after this? > I have read some WAL related stuff on postgres site but not found any > solution to recover those uncommited records. If the records were committed, then they'd get put into the db by the wal recovery process. If the hardware (i.e. the hard drive and its controller) wasn't lying about fsync. If the hardware was lying, you need to look into that (hint, IDE and many SATA drives lie about fsync) > Can any one tell me how to recover those missing records? there are some older tools laying about, like pgfsck that might help. I'd ask on admin or another list. SQL isn't really the list for admin / system problems...
On Mon, 2006-11-06 at 14:04, Richard Broersma Jr wrote:
> > 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.
Well, in the original, he had an id field to go with the pin, so I
assumed that he was generating them ahead of time in that format. so,
given an id number that increments to accompany the pins, you can be
sure that by using a sequence you'll never accidentally grab the same
pin twice.
By wrapping the logic in a pl/pgsql function and using sec definer, you
can be sure that some bug in your app logic can give you the same pin
twice by working around your well thought out rules of how to get a new
one.
Note that I was using the date_used field as a kind of "taken" marker.
If it was set, then there was an error, and you should NOT do the select
pin from table where id=$var but in fact get a new sequence number and
try again. Or error out.