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.
