As a remark, in Spain bill numbers need to be gapless increasing. I
have done it with a sequence ( with cache 1, so I doubt it is much
more performant than a single row table, as it has a backing element
which needs to be saved ), and just used another process which swept
the values and filled the gap a posteriori ( there is a way to fill a
gap, has it legal uses, you can use it for that as long as you do not
have too many, basically insert cancelled bills in the gaps ). I
probably would do it with the one-row table now for simplicity, I
doubt I gained enough performance to justify the 20-something perl
lines plus the crontab entry to use sequences. As beard turns grey I
apreciate more the simpler ways to do things.
One answer to this problem has been around for a while, and my version is shown below. No extra table is needed, just a windowed look at the table where the reference is used. My usage is at human speed so performance is not an issue but it should be pretty good if there are minimal holes. What is not addressed is that a reference can be reissued upto the time the calling process commits an entry in the table and takes that reference out of circulation. There are different approaches to handling/preventing such collisions.
CREATE OR REPLACE FUNCTION accounts.next_active_reference()
RETURNS integer
LANGUAGE 'sql'
VOLATILE LEAKPROOF STRICT
PARALLEL UNSAFE
COST 3000 -- pure guesstimate
AS $BODY$ SELECT L.transaction_ref + 1 AS start FROM accounts.transaction_refs AS L LEFT OUTER JOIN accounts.transaction_refs AS R ON L.transaction_ref + 1 = R.transaction_ref WHERE R.transaction_ref IS NULL AND L.transaction_ref >700 -- historic reasons only, added to existing system ORDER BY L.transaction_ref LIMIT 1;
$BODY$;
COMMENT ON FUNCTION accounts.next_active_reference() IS
$COMMENT$
Determines the next available reference number, making sure to fill any holes.
The holes in the sequence prior to 700 are ignored (pure history), and this code
will make sure any out of sequence data blocks will not cause conflicts.
Credits:
Ref: <http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/>
2022 update: this link is now dead, only reporting "There is nothing here".
$COMMENT$;
Regards
Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920
