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
