Search Postgresql Archives

Re: sequence used on null value or get the max value for a column whith concurrency

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thinking aloud...

On Thu, Aug 10, 2017 at 3:05 PM, marcelo <marcelo.nicolet@xxxxxxxxx> 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?

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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux