Re: Best approach for a "gap-less" sequence

Jorge Godoy wrote:
> Jorge Godoy <jgodoy@gmail.com> writes:
>
>> Is there a better way to guarantee that there will be no gaps in my sequence
>> if something goes wrong with my transaction?
>
>From the overwhelming feedback I assume there isn't a better way yet...
> Thanks.  I'll see how I can improve the model then to separate these sequences
> into different tables.
>

I'm not sure what type of lock you'd need to make sure no other
transactions updated the table (see
http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in
theory" something like this should work:

begin;
select id from table order by id desc limit 1;
insert into table (id, blah) values (id+1, 'blah');
commit;


P.S. I'm sure in older versions this query wouldn't use an index:
select max(id) from table;

I'm not sure about 8.0+.. hence doing an order by the id desc limit 1.

--
Postgresql & php tutorials
http://www.designmagick.com/