Re: Referencing serial col's sequence for insert

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