Chris <dmagick@gmail.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@gmail.com>
