Re: How to create "auto-increment" field WITHOUT a sequence object?

Hey Dmitry,

2011/6/30 Dmitry Koterov <dmitry(dot)koterov(at)gmail(dot)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.