Postgres Pro
Downloads
Home   >   mailing lists

Re: Referencing serial col's sequence for insert - Mailing list pgsql-general

From Anil Menon
Subject Re: Referencing serial col's sequence for insert
Date
Msg-id CAHzbRKd-itmwYvyh-s+s4KCdDuQcFeSYWEVX9ONYUX94tMMo=g@mail.gmail.com
Whole thread Raw
In response to Re: Referencing serial col's sequence for insert  (Albe Laurenz <laurenz.albe@wien.gv.at>)
Responses Re: Referencing serial col's sequence for insert  (Francisco Olarte <folarte@peoplecall.com>)
List pgsql-general
Tree view
Am a bit confused -which one comes first?

1) the 'data'||currval('id01_col1_seq') is parsed first : which means it takes the current session's currval
2) then the insert is attempted which causes a sequence.nextval to be performed which means that 'data'||currval('id01_col1_seq')will be different from the sequence's value

or

1) an insert is attempted which causes a sequence.nextval to be performed and then
2) the cols are parsed for the insert so the 'data'||currval('id01_col1_seq') has the correct value

I observe the latter on my single session notebook instance of postgres.


Regards,
AK


On Tue, Jul 22, 2014 at 10:53 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Ramesh T
Date:
Subject: Re: Need r_constraint_name
Next
From: klo uo
Date:
Subject: question about memory usage