Sequential sequence numbers

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

Show quoted text

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"

Show quoted text

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)