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

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general