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

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

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

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

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.

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

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

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

On Tuesday, July 22, 2014, rob stone-2 [via PostgreSQL] <
ml-node+s1045698n5812384h28@n5.nabble.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.

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

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:

On Tue, Jul 22, 2014 at 9:46 AM, 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
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

​If this was the case currval would always emit an error for the first
insert of the session...​

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.

​And given that it is the logical conclusion why are you confused?​

​To be honest I totally missed the dual-column nature of the OP. I read it
as simply wishing to use the sequence value in a string instead of, not in
addition to, the "serial" defined column.

I do not know whether the call to nextval in the default will always occur
before any currval expression in the source query...it might make more
sense, for multiple reasons, to simply define a trigger to enforce the
value of "col2". A user-defined trigger will always be evaluated after the
default expression and so you can simply pick off the value assigned to
"col1" and do what you'd like with it. Combined with a constraint you can
remove the entire business rule from user logic and embed it into the
database where it cannot be messed up.

David J.​

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.

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

Thanks Olarte,
Exactly following your advice...this being the beauty of open source -you
can read the source code
​. It's that itch to drink deep from the fountain of knowledge.​

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.
​In fact I am sort of expanding that a bit to say wherever sequences need
to be used ​
​use the WITH() AS construct pattern.​

Thanks everyone for the inputs.

Regards
​,​

A
​nil​

On 24 Jul 2014 02:03, "Francisco Olarte" <folarte@peoplecall.com> wrote:

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.

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