Обсуждение: Get last generated serial sequence and set it up when explicit value is used

Hi all!

Using SERIAL or BIGSERIAL column, I try to find a smart solution to
do the following when an INSERT is done:

1) Retrieve the last generated sequence, so the program can use it.

2) Setup the underlying sequence, if an explicit value was used by
the INSERT statement.

So far I figured out the following by using the RETURNING clause...

Is this ok / legal / without risk? (when multiple users insert rows at the same time?)


test1=# create table table1 ( pkey serial not null primary key, name varchar(50) );
CREATE TABLE


test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
  pkey | last_value
------+------------
     1 |          1
(1 row)

INSERT 0 1

test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
  pkey | last_value
------+------------
     2 |          2
(1 row)

INSERT 0 1

test1=# insert into table1 (pkey,name) values (100,'aaaa') returning pkey, (select last_value from table1_pkey_seq);
  pkey | last_value
------+------------
   100 |          2
(1 row)

INSERT 0 1



I see 100 is > than 2, so reset the sequence:

test1=# select setval('table1_pkey_seq',101,false);
  setval
--------
     101
(1 row)

test1=# insert into table1 (name) values ('aaaa') returning pkey, (select last_value from table1_pkey_seq);
  pkey | last_value
------+------------
   101 |        101
(1 row)

INSERT 0 1




Any better way to do that in a single SQL statement?


Is it legal to use a subquery in a RETURNING clause?


Thanks!
Seb

I should have been more clear...

On 11/19/20 9:04 PM, David G. Johnston wrote:
> On Thu, Nov 19, 2020 at 12:21 PM Sebastien FLAESCH <sf@4js.com <mailto:sf@4js.com>> wrote:
> 
>     Is this ok
> 
> 
> subjective; I find it confusing.
> 
>     / legal
 >
 > expect that the system will tell you if what you are doing is illegal.


I was wondering if [SELECT last_value FROM sequence-name] was documented and if
it's legal to use such query in the RETURNING clause of an INSERT.

1) It is documented: https://www.postgresql.org/docs/13/sql-createsequence.html

2) Since PostgreSQL does not produce any error, I will consider that it's legal.


> 
>     / without risk? (when multiple users insert rows at the same time?)
> 
> add a unique index and it will be risk free - and you will see whether the rate of errors is acceptable for you.

Sure, the serial column should have a unique / primary key constraint.

My question was more related to concurrent clients doing the same query, and
make sure that the SELECT last_value FROM sequence-name query will return
the last serial value produced for the current session (and not from others)

Reading the doc (I should have done this before asking here sorry):

"
  SELECT * FROM name;

to examine the parameters and current state of a sequence. In particular,
the last_value field of the sequence shows the last value allocated by any
session.
"

So that solution DOES NOT work for us, since it returns the last_value from
ANY session...

Using SELECT currval(seq) is not possible, since that would produce an SQL error
and cancel the INSERT and the whole transaction, if the first INSERT of the session
is using an explicit value for the serial.

test1=# insert into table1 (pkey,name) values (3335,'aaaa') returning pkey, currval('table1_pkey_seq');
ERROR:  currval of sequence "table1_pkey_seq" is not yet defined in this session

test1=# insert into table1 (name) values ('aaaa') returning pkey, currval('table1_pkey_seq');
  pkey | currval
------+---------
   103 |     103
(1 row)

INSERT 0 1


> Seems if you are going to allow direct inserts though you should test multiple inserted rows at a time, not just
single-row.

It will be one row insert at a time.

> I'd suggest using the newer GENERATED ALWAYS feature and prohibiting direct specification of values for the
sequence-backedcolumn.
 
Good point.

But we have to use the SERIAL/BIGSERIAL types because we want to have an equivalent
behavior as the Informix SERIAL/BIGSERIAL columns, where you can provide a value
for the serial column, and the DB will automatically reset the serial counter for
a next INSERT with zero as value or without serial column usage in the INSERT.

I suspect BTW that these types exist in PostgreSQL to mimic Informix serials.
Unfortunately, the behavior is not 100% Informix compatible.


Is there any way to avoid the error produced by currval()?

Ideally, currval() should return zero when no serial was produced yet.

Is it possible to write that in a simple SQL expression so it can be used in
the RETURNING clause of my INSERTs ?




Seb

> David J.
> 

Is there any way to avoid the error produced by currval()?

Ideally, currval() should return zero when no serial was produced yet.

Is it possible to write that in a simple SQL expression so it can be used in
the RETURNING clause of my INSERTs ?

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

Hello everyone!

I believe I have a solution using a single SQL command.

Check this out... do you see any potential issues?

Any simpler way or more efficient code?

Note the insert with value 50, lower than previously inserted values, otherwise,
the returning clause would just need to be

         returning pkey, setval('mytab1_pkey_seq',pkey,true)

Note also that I want to return the pkey to use the generated serial in the
program code...


=====

create table mytab1 ( pkey serial not null primary key, name varchar(50) );

insert into mytab1 (name)      values ('aaa')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

insert into mytab1 (pkey,name) values (100,'bbb')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

insert into mytab1 (name)      values ('ccc')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

insert into mytab1 (pkey,name) values (50,'ddd')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

insert into mytab1 (name)      values ('eee')
     returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
                                  then setval('mytab1_pkey_seq',pkey,true)
                                  else 0
                             end );

select * from mytab1 order by name;

=====

SELECT output:

  pkey | name
------+------
     1 | aaa
   100 | bbb
   101 | ccc
    50 | ddd
   102 | eee
(5 rows)




PostgreSQL rocks!

Seb

Better use >= in pkey >= (select last_value ... ) :

  insert into mytab1 (name) values ('aaa')
       returning pkey, (select case when pkey >= (select last_value from mytab1_pkey_seq)
                                    then setval('mytab1_pkey_seq',pkey,true)
                                    else 0
                               end );

Seb

On 11/21/20 10:26 AM, Sebastien FLAESCH wrote:
> Hello everyone!
> 
> I believe I have a solution using a single SQL command.
> 
> Check this out... do you see any potential issues?
> 
> Any simpler way or more efficient code?
> 
> Note the insert with value 50, lower than previously inserted values, otherwise,
> the returning clause would just need to be
> 
>          returning pkey, setval('mytab1_pkey_seq',pkey,true)
> 
> Note also that I want to return the pkey to use the generated serial in the
> program code...
> 
> 
> =====
> 
> create table mytab1 ( pkey serial not null primary key, name varchar(50) );
> 
> insert into mytab1 (name)      values ('aaa')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> insert into mytab1 (pkey,name) values (100,'bbb')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> insert into mytab1 (name)      values ('ccc')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> insert into mytab1 (pkey,name) values (50,'ddd')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> insert into mytab1 (name)      values ('eee')
>      returning pkey, (select case when pkey > (select last_value from mytab1_pkey_seq)
>                                   then setval('mytab1_pkey_seq',pkey,true)
>                                   else 0
>                              end );
> 
> select * from mytab1 order by name;
> 
> =====
> 
> SELECT output:
> 
>   pkey | name
> ------+------
>      1 | aaa
>    100 | bbb
>    101 | ccc
>     50 | ddd
>    102 | eee
> (5 rows)
> 
> 
> 
> 
> PostgreSQL rocks!
> 
> Seb
>