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.
>