Skip site navigation (1) Skip section navigation (2)

Sequential sequence numbers

From: Marc SCHAEFER <schaefer(at)alphanet(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Subject: Sequential sequence numbers
Date: 2001-10-27 10:08:52
Message-ID: (view raw or whole thread)
Lists: pgsql-general

for an application involving accounting (a free software project),
I need to implement a sequential number generator: one that doesn't have

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

Thus it appears SEQUENCEs (or the SERIAL type) cannot be used in this

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
      counter := 
         SELECT current_value
         FROM serial_number
         WHERE name = 'ecriture'
         FOR UPDATE; -- This should lock/serialize access to this and
                     -- prevent races, AFAIK.


      INSERT INTO ecriture(name, number) VALUES (name, counter);

      UPDATE serial_number SET current_value = counter WHERE name = 'ecriture';

      -- AFAIK the lock is now over


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.


pgsql-general by date

Next:From: Lincoln YeohDate: 2001-10-27 11:01:33
Subject: Re: Disable Transaction - plans ?
Previous:From: Stephan SzaboDate: 2001-10-27 06:57:17
Subject: Re: concurrency performance degradation

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group