Hey Dmitry, 2011/6/30 Dmitry Koterov <dmitry.kote...@gmail.com>
> Hello. > > I need to create an auto-increment field on a table WITHOUT using > sequences: > > CREATE TABLE tbl( > name TEXT, > uniq_id INTEGER > ); > > Each INSERT to this table must generate a new uniq_id which is distinct > from all others. > > The problem is that these INSERTs are rolled back oftenly (i.e. they are > executed within a transaction block which is rolled back time to time), this > is an existing design of the current architecture and unfortunately we have > to live with it. And I need as compact uniq_id generation (with minimum > "holes") as it possible - this is a VERY important requirement (to export > these values into external systems which accepts only IDs limited from 1 to > 100000). > > So I cannot use sequences: sequence value is obviously not rolled back, so > if I insert nextval(...) as uniq_id, I will have large holes (because of > often transaction rollbacks) and exhaust 100000 uniq_ids very fast. How to > deal with all this without sequences? > > I tried > > BEGIN; > LOCK TABLE tbl; > INSERT INTO tbl(uniq_id) VALUES((SELECT max(uniq_id) FROM tbl) + 1); > COMMIT; > Consider to create table with column of type integer and write a function which will perform SELECT FOR UPDATE ... and returns the next value, i.e. BEGIN; INSERT INTO tbl(uniq_id) SELECT uniq_id_generator(); -- SELECT FOR UPDATE inside COMMIT; -- or ROLLBACK -- // Dmitriy.
