Re: Get last generated serial sequence and set it up when explicit value is used
От | Sebastien FLAESCH |
---|---|
Тема | Re: Get last generated serial sequence and set it up when explicit value is used |
Дата | |
Msg-id | 10622684-b74b-39d2-83d8-4ed2d8d52915@4js.com обсуждение исходный текст |
Ответ на | Re: Get last generated serial sequence and set it up when explicit value is used ("David G. Johnston" <david.g.johnston@gmail.com>) |
Ответы |
Re: Get last generated serial sequence and set it up when explicit value is used
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | pgsql-sql |
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. >
В списке pgsql-sql по дате отправления:
Предыдущее
От: "David G. Johnston"Дата:
Сообщение: Re: Get last generated serial sequence and set it up when explicit value is used
Следующее
От: "David G. Johnston"Дата:
Сообщение: Re: Get last generated serial sequence and set it up when explicit value is used