Обсуждение: Referencing serial col's sequence for insert

On Mon, 2014-07-21 at 10:00 +0800, Anil Menon wrote:
> Hi,
>
>
> I have a question on the right/correct practice on using the serial
> col's sequence for insert.
>
>
> Best way of explanation is by an example:
>
>
> create table id01 (col1 serial, col2 varchar(10));
>
> insert into id01(col2) values ( 'data'||
> currval('id01_col1_seq')::varchar);
>
>
> while I do get what I want:
>
> select  * from id01;
>  col1 | col2
> ------+-------
>     1 | data1
>
>
> Is this guaranteed to work : I am assuming that an insert triggers the
> id01_col1_seq's nextval first hence using
> id01_col1_seq's currval subsequently will have the "correct" /
> expected value (and not the previous value before the insert).
>
>
> Is my assumption correct?
>
>
> Thanks in advance,
>
> AK
>
>
>
>

I would do the following:-

create table id01 (col1 serial NOT NULL PRIMARY KEY, col2 varchar(10));

In a try . . catch block:-

BEGIN;
INSERT INTO id01 (col2) VALUES ('data');
SELECT lastval() AS last_row_id;
COMMIT; or ROLLBACK; if you have errors.

There is also "insert . . returning" syntax which can make the value
assigned to the serial column available to your application. I prefer
using the "select lastval()" method.

HTH.

Robert

rob stone wrote:
>> I have a question on the right/correct practice on using the serial
>> col's sequence for insert.
>>
>> Best way of explanation is by an example:
>>
>> create table id01 (col1 serial, col2 varchar(10));
>>
>> insert into id01(col2) values ( 'data'||
>> currval('id01_col1_seq')::varchar);
>>
>> while I do get what I want:
>>
>> select  * from id01;
>>  col1 | col2
>> ------+-------
>>     1 | data1
>>
>> Is this guaranteed to work : I am assuming that an insert triggers the
>> id01_col1_seq's nextval first hence using
>> id01_col1_seq's currval subsequently will have the "correct" /
>> expected value (and not the previous value before the insert).
>>
>> Is my assumption correct?

> I would do the following:-
> 
> create table id01 (col1 serial NOT NULL PRIMARY KEY, col2 varchar(10));
> 
> In a try . . catch block:-
> 
> BEGIN;
> INSERT INTO id01 (col2) VALUES ('data');
> SELECT lastval() AS last_row_id;
> COMMIT; or ROLLBACK; if you have errors.
> 
> There is also "insert . . returning" syntax which can make the value
> assigned to the serial column available to your application. I prefer
> using the "select lastval()" method.

Your example seems incomplete.

Also, I think that your method is vulnerable to race conditions:
If somebody else increments the sequence between the INSERT and
"SELECT lastval()" you'd get a wrong value.

The same might hold for the original example.

I would suggest something like that:

WITH seq(i) AS (SELECT nextval('id01_col1_seq'))
   INSERT INTO id01 (col1, col2) (SELECT i, 'data' || i FROM seq);

Yours,
Laurenz Albe

Albe Laurenz *EXTERN* wrote
> Also, I think that your method is vulnerable to race conditions:
> If somebody else increments the sequence between the INSERT and
> "SELECT lastval()" you'd get a wrong value.

Uh, no.  It returns that last value issued in the same session - which is
race-proof.

http://www.postgresql.org/docs/9.3/static/functions-sequence.html

Both of them are useful and in the case of inserting multiple rows you have
to use RETURNING.  Beyond though it is largely personal preference and
ease-of-use (dealing with a set when you know a single value is all that is
necessary can be annoying).

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Referencing-serial-col-s-sequence-for-insert-tp5812225p5812382.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tue, 2014-07-22 at 13:32 +0000, Albe Laurenz wrote:
> rob stone wrote:
> >> I have a question on the right/correct practice on using the serial
> >> col's sequence for insert.
> >>
> >> Best way of explanation is by an example:
> >>
> >> create table id01 (col1 serial, col2 varchar(10));
> >>
> >> insert into id01(col2) values ( 'data'||
> >> currval('id01_col1_seq')::varchar);
> >>
> >> while I do get what I want:
> >>
> >> select  * from id01;
> >>  col1 | col2
> >> ------+-------
> >>     1 | data1
> >>
> >> Is this guaranteed to work : I am assuming that an insert triggers the
> >> id01_col1_seq's nextval first hence using
> >> id01_col1_seq's currval subsequently will have the "correct" /
> >> expected value (and not the previous value before the insert).
> >>
> >> Is my assumption correct?
>
> > I would do the following:-
> >
> > create table id01 (col1 serial NOT NULL PRIMARY KEY, col2 varchar(10));
> >
> > In a try . . catch block:-
> >
> > BEGIN;
> > INSERT INTO id01 (col2) VALUES ('data');
> > SELECT lastval() AS last_row_id;
> > COMMIT; or ROLLBACK; if you have errors.
> >
> > There is also "insert . . returning" syntax which can make the value
> > assigned to the serial column available to your application. I prefer
> > using the "select lastval()" method.
>
> Your example seems incomplete.
>
> Also, I think that your method is vulnerable to race conditions:
> If somebody else increments the sequence between the INSERT and
> "SELECT lastval()" you'd get a wrong value.
>
> The same might hold for the original example.
>
> I would suggest something like that:
>
> WITH seq(i) AS (SELECT nextval('id01_col1_seq'))
>    INSERT INTO id01 (col1, col2) (SELECT i, 'data' || i FROM seq);
>
> Yours,
> Laurenz Albe

Hi Laurenz,

The documentation in chapter 9.16 says otherwise.
SELECT lastval() may only be called after a nextval which is issued
implicitly by the INSERT statement as a column is defined as SERIAL.
As you are in transaction state (via BEGIN;) I don't believe that
another process accessing the same sequence can upset the SELECT
lastval(). I could be wrong but I don't have the means to test this out.

Regards,
Rob

On Tue, 2014-07-22 at 13:32 +0000, Albe Laurenz wrote:

> rob stone wrote:
> >> I have a question on the right/correct practice on using the serial
> >> col's sequence for insert.
> >>
> >> Best way of explanation is by an example:
> >>
> >> create table id01 (col1 serial, col2 varchar(10));
> >>
> >> insert into id01(col2) values ( 'data'||
> >> currval('id01_col1_seq')::varchar);
> >>
> >> while I do get what I want:
> >>
> >> select  * from id01;
> >>  col1 | col2
> >> ------+-------
> >>     1 | data1
> >>
> >> Is this guaranteed to work : I am assuming that an insert triggers the
> >> id01_col1_seq's nextval first hence using
> >> id01_col1_seq's currval subsequently will have the "correct" /
> >> expected value (and not the previous value before the insert).
> >>
> >> Is my assumption correct?
>
> > I would do the following:-
> >
> > create table id01 (col1 serial NOT NULL PRIMARY KEY, col2 varchar(10));
> >
> > In a try . . catch block:-
> >
> > BEGIN;
> > INSERT INTO id01 (col2) VALUES ('data');
> > SELECT lastval() AS last_row_id;
> > COMMIT; or ROLLBACK; if you have errors.
> >
> > There is also "insert . . returning" syntax which can make the value
> > assigned to the serial column available to your application. I prefer
> > using the "select lastval()" method.
>
> Your example seems incomplete.
>
> Also, I think that your method is vulnerable to race conditions:
> If somebody else increments the sequence between the INSERT and
> "SELECT lastval()" you'd get a wrong value.
>
> The same might hold for the original example.
>
> I would suggest something like that:
>
> WITH seq(i) AS (SELECT nextval('id01_col1_seq'))
>    INSERT INTO id01 (col1, col2) (SELECT i, 'data' || i FROM seq);
>
> Yours,
> Laurenz Albe

Hi Laurenz,

The documentation in chapter 9.16 says otherwise.
SELECT lastval() may only be called after a nextval which is issued
implicitly by the INSERT statement as a column is defined as SERIAL.
As you are in transaction state (via BEGIN;) I don't believe that
another process accessing the same sequence can upset the SELECT
lastval(). I could be wrong but I don't have the means to test this out.

David G Johnston wrote:
>> Also, I think that your method is vulnerable to race conditions:
>> If somebody else increments the sequence between the INSERT and
>> "SELECT lastval()" you'd get a wrong value.
> 
> Uh, no.  It returns that last value issued in the same session - which is
> race-proof.
> 
> http://www.postgresql.org/docs/9.3/static/functions-sequence.html
> 
> Both of them are useful and in the case of inserting multiple rows you have
> to use RETURNING.  Beyond though it is largely personal preference and
> ease-of-use (dealing with a set when you know a single value is all that is
> necessary can be annoying).

You are right, I mixed it up with "currval".

Yours,
Laurenz Albe

Hi Anil:

On Tue, Jul 22, 2014 at 6:46 PM, Anil Menon <gakmenon@gmail.com> wrote:
> Am a bit confused -which one comes first?
> 1) the 'data'||currval('id01_col1_seq') is parsed first : which means it
....
>or
> 1) an insert is attempted which causes a sequence.nextval to be performed
...
> I observe the latter on my single session notebook instance of postgres.

Don't be confused, you have experimental evidence which beats theories
hand down, it's either the later or a superset of it ( i.e., second
for single sessions only, or second on notebook sessions, but it seems
unlikely ).

Also note the 1st one cannot be unless you are not using a fresh
session ( i.e., the insert is the first command typed, which if it is
not signals you are testing badly ), since currval is documented as
failing in this case.

Anyway, you aproach is risky. You've been told a lot of alternatives
which have predictable behaviour ( here is another one, start work,
select and ignore nextval, then use currval for BOTH values ), so why
not use one of them? Bear in mind that the second alternative maybe
working due to undefined behaviour which may change in a future
release, or when using multiple rows ( or when using an even number of
sessions, although, as before, I find that one unlikely ). ( Or, if
you really want to know for knowledges sake which is the behaviour,
download the sources, procure yourself a tank of your favorite
caffeinated potion and hack on. )

Regards.

    Francisco Olarte.

Hi Anil:

On Wed, Jul 23, 2014 at 11:34 PM, Anil Menon <gakmenon@gmail.com> wrote:
...
> . It's that itch to drink deep from the fountain of knowledge.

Beware of hyponatremia, and keep in mind it can change in the future.
But you'll surely learn a lot.


> I really do like
> Laurenz Albe's advice of using WITH() AS which seems to be the best practice
> I can ask the developers to follow as it eliminates a lot of uncertainties
> and db specific behavior - and seems like a best practice even for other
> DBs.

Yep, that is the kind of thing I use in production too. These kind of
queries are predictable and easy to build and test incrementally.

Regards.

Francisco Olarte.