Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

On 20 Jul 2022, at 4:08, Francisco Olarte wrote:

> 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