On Jan 17, 2008 9:19 AM, James B. Byrne <byrnejb@xxxxxxxxxxxxx> wrote:
>
> On Thu, January 17, 2008 10:15, Scott Marlowe wrote:
> >
> > If race conditions are a possible issue, you use a sequence and
> > increment that until you get a number that isn't used. That way two
> > clients connecting at the same time can get different, available
> > numbers.
> >
>
> That is close to the idea that I originally had. I was simply wondering
> if the built-in sequencer could handle this case or whether I need to roll
> my own.
Got bored, hacked this aggregious pl/pgsql routine up. It looks
horrible, but I wanted it to be able to use indexes. Seems to work.
Test has ~750k rows and returns in it and returns a new id in < 1ms
on my little server.
File attached.
drop table a;
drop sequence aseq;
create table a (i integer primary key);
create sequence aseq;
insert into a(i) select * from generate_series(1,1000000) where random() > 0.25;
create or replace function getnext() returns int as $$
DECLARE
niq int;
tf bool;
BEGIN
loop
select nextval('aseq') into niq;
select case
when (select true from (select niq as i) as x join a on (a.i=x.i))
then TRUE
else FALSE end into tf;
exit when not tf ;
end loop;
return niq;
END;
$$ language plpgsql;
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
