Re: [GENERAL] sequence used on null value or get the max value for acolumn whith concurrency

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [GENERAL] sequence used on null value or get the max value for acolumn whith concurrency
Дата
Msg-id CAKFQuwZEmZaUfZxh6jtkxe9US8Bcg6P7JRQkx234aBneLh2cDA@mail.gmail.com
обсуждение исходный текст
Ответ на [GENERAL] sequence used on null value or get the max value for a column whithconcurrency  (marcelo <marcelo.nicolet@gmail.com>)
Список pgsql-general
Дерево обсуждения
Thinking aloud...

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

В списке pgsql-general по дате отправления:

Предыдущее
От: marcelo
Дата:
Сообщение: [GENERAL] sequence used on null value or get the max value for a column whithconcurrency
Следующее
От: Fabiana Zioti
Дата:
Сообщение: [GENERAL] Receive a string in Composite-type Arguments