Re: Referencing serial col's sequence for insert

On Tuesday, July 22, 2014, rob stone-2 [via PostgreSQL] <
ml-node+s1045698n5812384h28(at)n5(dot)nabble(dot)com> wrote:

>
>
>
> 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.
>
>
1. lastval does not require a transaction block, it operates with
session-level memory.
2. It's the default expression on the table the will automatically use the
sequence if allowed. But if you can always provide your own value to that
column and then the sequence will go unused. The insert triggers the
default but itself doesn't care about sequences. lastval doesn't care how
or why nextval was called (manually or via a default).

David J.

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