Re: Sequential sequence numbers - Mailing list pgsql-general

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)