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

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Dmitry Koterov
Sent: Thursday, June 30, 2011 2:41 PM
To: Postgres General
Subject: [GENERAL] How to create "auto-increment" field WITHOUT a sequence
object?

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;

but seems it performs too hard locking - time to time this query is timed
out (or sometimes deadlocks with other queries).

Is there any other, less hard, locking which allow me to guarantee that no
INSERTs will be performed into tbl between max() calculation and UPDATE
query itself, but does not lock the whole table?

>>>>>>>>>>>>>>>>>>>>

Why not have an internal and an external id? The internal one would use the
sequence and wouldn't care about being gap-less. The external one would be
assigned post-Insert and thus, ignoring deletes, can be gap-less. Depending
upon how frequently/quickly the external identifier needs to be present you
have various options to actually assign the external identifier value.

CREATE TABLE tbl(

name TEXT,

uniq_id serial,

external_id integer NULL

);

Upon creating a new record you have a record with a NULL external_id. At
some point in the future, prior to export, you can replace all the NULLs
with actual values using a sequence. Depending on whether or not the
transaction can be rolled back when successful you can add the "UPDATE"
statement as the last statement of the transaction so that it will only fire
if the transaction is otherwise going to complete successfully.

Without more detail about the how and why of your restrictions it is
difficult to provide solutions.

David J.