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

Chris <dmagick(at)gmail(dot)com> writes:

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

This is part of the solution, yes. But I would still need locking this table
so that no other concurrent transaction gets another "id". I don't want to
lock the main table -- as I believe you're suggesting -- because I want it to
be searchable and updatable while I'm inserting new data. I just can't have
gaps in the sequence but I don't want to restrict everything else here.

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

It doesn't. You'd have to do what you did: "order by <x> desc limit 1" to
have it using indexes...

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

I also have to test it... But I still keep using the "order by desc" syntax
:-)

Thanks for your answer,
--
Jorge Godoy <jgodoy(at)gmail(dot)com>