Chris <dmagick@xxxxxxxxx> 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@xxxxxxxxx>
