Re: Sequential sequence numbers

If truly sequential numbers is what you want, that is, sequential both by
number and by time (i.e. a smaller number cannot be inserted after a larger
number has) you can only do it with a performance hit. The idea would be:

CREATE TABLE seq ( index INT NOT NULL );
-- we assume that seq has been initialized with a single row, index value 1

-- how to use it:
BEGIN
SELECT index FROM seq FOR UPDATE;
UPDATE seq SET index = index + 1;
-- do your stuff here
-- if the transaction is committed, seq.index is incremented, and the lock
is released
-- if the transaction is rolled back, seq.index is untouched, the lock is
released, and the next call will attempt to use the current number.

As you can see, locking the sequence generator in this way blocks
simultaneous access, but wouldn't be a big deal if there aren't may users
and the actions taken during the transaction are quick. The drawback is that
seq will be locked for the duration of the transaction.

If it is not important that the sequence be sequential in time, you could
employ a sequence and a queue table, where the queue table is checked first
before the sequence is called. This would probably have to be done in the
application, as I can't think of how it can be accomplished using triggers
alone since you have to check for a rollback condition where you would need
to push the unused number onto the queue.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

> From: Marc SCHAEFER <schaefer(at)alphanet(dot)ch>
> Date: Sat, 27 Oct 2001 12:08:52 +0200 (MEST)
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Sequential sequence numbers
>
> Hi,
>
> for an application involving accounting (a free software project),
> I need to implement a sequential number generator: one that doesn't have
> holes.
>
> As far as I have understood/experimented it, SEQUENCEs in PostgreSQL have
> the advantage they are backend-private: this solves many concurrency
> issues without any performance loss. Unfortunately it also means that
> numbering holes will be created when transactions are rolled back, for
> example.
>
> Thus it appears SEQUENCEs (or the SERIAL type) cannot be used in this
> context.
>
> Numbers could be emulated through sorting by OID, and the number of the
> tuple (row) could be implicit. Design constraints forbids this: a deletion
> should be seeable at the application level by a numbering hole.
>
> I have thought of the following:
>
> CREATE TABLE serial_number (name TEXT NOT NULL,
> current_value INT4 NOT NULL DEFAULT 0,
> UNIQUE(name), PRIMARY KEY(name));
>
> Initialization phase (must be done before the application is installed):
>
> INSERT INTO serial_number(name) VALUES ('ecriture');
>
> Use of the counter to create a new instance, possibly in a RULE or TRIGGER
> of another table, say the `ecriture' table:
>
> -- This is pseudo-code
> BEGIN WORK;
> counter :=
> SELECT current_value
> FROM serial_number
> WHERE name = 'ecriture'
> FOR UPDATE; -- This should lock/serialize access to this and
> -- prevent races, AFAIK.
>
> counter++;
>
> INSERT INTO ecriture(name, number) VALUES (name, counter);
>
> UPDATE serial_number SET current_value = counter WHERE name = 'ecriture';
>
> -- AFAIK the lock is now over
>
> COMMIT WORK;
>
> Would you have a suggestion or comment on the subject ? Should I take
> care of something special (SERIALIZATION) ? Is there a simpler method ?
>
> Is it possible to genericize the procedure so that the table name where
> the trigger applies is the parameter of the function ?
>
> When a function defines a new transaction, will the commit commit this
> new transaction or the possibly enclosing transaction (ie: is the
> concept of sub-transaction possible/implemented) ?
>
> Thank you for any idea, pointers, or suggestions.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>