Обсуждение: [GENERAL] sequence used on null value or get the max value for a column whithconcurrency
In some table, I have a bigint column which at the app level can be null. Call it "DocumentNumber", and of course is not the PK. In most cases, the applications give some value to the column. But sometimes, the value remains null, expecting the backend or someone assign it a unique value. Could I use a sequence only when the field arrives to the backend as null? How? Using a triger? Alternatively: How could I get the max value for the column and increment it by one, but with concurrency warranty? Something as a table lock? TIA
Re: [GENERAL] sequence used on null value or get the max value for acolumn whith concurrency
От
"David G. Johnston"
Дата:
Thinking aloud...
In some table, I have a bigint column which at the app level can be null. Call it "DocumentNumber", and of course is not the PK.
In most cases, the applications give some value to the column.
But sometimes, the value remains null, expecting the backend or someone assign it a unique value.
Could I use a sequence only when the field arrives to the backend as null? How? Using a triger?
Can you reserve a portion of the value range for auto-generated numbers that application-assigned values will take on? If so it would reasonably simple to invoke nextval() in a trigger.
How could I get the max value for the column and increment it by one, but with concurrency warranty? Something as a table lock?
One option would be to maintain the value in a separate table that you update on insert using "UPDATE tbl SET col = col + 1 RETURNING col INTO new_doc_num"
You could probably make it an unlogged table as well and you'd return from the trigger function with new_doc_num if its non-null otherwise you'd branch and re-create the record before returning the just queried maximum + 1.
David J.
On Thu, 2017-08-10 at 19:05 -0300, marcelo wrote:
> In some table, I have a bigint column which at the app level can be
> null. Call it "DocumentNumber", and of course is not the PK.
> In most cases, the applications give some value to the column.
>
> But sometimes, the value remains null, expecting the backend or
> someone
> assign it a unique value.
>
> Could I use a sequence only when the field arrives to the backend as
> null? How? Using a triger?
>
> Alternatively:
>
> How could I get the max value for the column and increment it by
> one,
> but with concurrency warranty? Something as a table lock?
>
> TIA
>
>
>
>
>
Hello Marcelo,
I haven't tested this but if you define the column thus:-
document_number bigint default
nextval('my_document_number_sequence'::regclass)
then on insert, if that column is not in the values list, then the next
available number from the sequence will be used.
Remember, that if a rollback occurs, the sequence number is lost
forever.
HTH,
Robert