Обсуждение: 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.

marc,

I too am using postgresql as the backend for job tracking system. I
also needed 'no holes'. What I found is that working with sequences
is a little tricky. If there is a failure during your 'INSERT' you could get
a skipped number.

Technically, (very technically) this is not an issue. I am being very
careful with my words. Technically, you do not need to guarentee that
there are no holes. You only need to guarentee that the same
number is not reused. Let's face it if you have 100 clients in your
accounting package, the invoice numbers will be peppered
thoughout the clients. These numbers are only there to assist in
applying payments to jobs. They only need to be unique.

No client is likely to get many consecutive numbers. So what is the
big deal..... I will tell you that as a business owner, when I get my job
list and I see missing numbers I have to go and resolve why
numbers are missing. Did someone screw up? Is someone
deleting jobs from the system to give a 'friend' a break? My first line
of defence is to check for all job numbers.

My method is to create a random number in my client app. Insert the
number and get the 'sequence number' assigned to that row. Now
the row is mine. The number is accounted for. I can update that row
with client information. If the update fails, I still have the row. I know
that this is not perfect. The only thing that can go wrong is the initial
insert. Except that the number is machine generated, there is no
human involved. I think I have a pretty good probability that the insert
will succeed. In over a year with multiple users, I have never skipped
a number.

generate random
insert random -- which generates the sequence number
select seq_num where random_column = random -- now i have the
invoice number and row to do with as I please

Keep  in mind that there are many things that are technically correct,
but bad business. (Accountants that insist that a statement with
'balance forwarded' will be the death of any good business)

JMHO

Ted

-----Original Message-----
From: Marc SCHAEFER <schaefer@alphanet.ch>
To: pgsql-general@postgresql.org
Date: Sat, 27 Oct 2001 12:08:52 +0200 (MEST)
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@postgresql.org)

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@alphanet.ch>
> Date: Sat, 27 Oct 2001 12:08:52 +0200 (MEST)
> To: pgsql-general@postgresql.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@postgresql.org)
>