From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | marcelo <marcelo(dot)nicolet(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: sequence used on null value or get the max value for a column whith concurrency |
Date: | 2017-08-10 23:18:09 |
Message-ID: | CAKFQuwZEmZaUfZxh6jtkxe9US8Bcg6P7JRQkx234aBneLh2cDA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thinking aloud...
On Thu, Aug 10, 2017 at 3:05 PM, marcelo <marcelo(dot)nicolet(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Fabiana Zioti | 2017-08-11 13:09:13 | Receive a string in Composite-type Arguments |
Previous Message | marcelo | 2017-08-10 22:05:35 | sequence used on null value or get the max value for a column whith concurrency |