Re: Get last generated serial sequence and set it up when explicit value is used

On 11/20/20 9:49 AM, David G. Johnston wrote:
> On Friday, November 20, 2020, Sebastien FLAESCH <sf@4js.com <mailto:sf@4js.com>> wrote:
> 
>     Is there any way to avoid the error produced by currval()?
> 
> 
> No
> 
>     Ideally, currval() should return zero when no serial was produced yet.
> 
> 
> I’d accept null, zero is a valid value.
> 
> 
>     Is it possible to write that in a simple SQL expression so it can be used in
>     the RETURNING clause of my INSERTs ?
> 
> 
> Not that I can think of.  Maybe as the docs suggest, just do an unconditional setval()?  You might be able to combine
thatwith a non-default 
 
> isolation level (guessing here) to get close-enough behavior.  You are fighting the existing design of the feature,
lookingfor an in-between position 
 
> of fast-and-concurrent (existing) and fully serialized (where this would be mostly trivial to implement).  I don’t
knowof such a method.
 
> 
> David J.
> 

Thanks David for your comments.

I will give a chance to:

insert into table1 (name) values ('aaaa')
    returning pkey, (select last_value from table1_pkey_seq);

Followed by a setval('seq',pkey,true), if pkey > last_value ...

In fact I wonder how PostgreSQL actually executes such statement.

To me, it should be an atomic operation so I guess the

   (SELECT last_value FROM seq-name)

Should either return the new serial produced by this current INSERT,
or a new serial produced previously by the INSERT in another session,
when the current INSERT do not produce a new serial value.

But it should not return a new serial value that was produced
by another session between the actual local INSERT and the SELECT
last_val sub-query in the RETURNING clause...

Anyway, doing the setval(...pkey...) when pkey value is greater than
the last_value, should also be ok if a new last_value was produced
by another session in-between...

Does that make sense?

Seb