Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

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

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