Thread: SERIAL Field

SERIAL Field

From
"Gaetano Mendola"
Date:
Hi all,

having a table with a serial field what is the corrected method
to retrieve the value after an insert ?




Ciao
Gaetano

--
#exclude <windows>
#include <CSRSS>
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");


Re: SERIAL Field

From
"Joel Burton"
Date:
> Hi all,
>
> having a table with a serial field what is the corrected method
> to retrieve the value after an insert ?

Use currval(_name_of_sequence_):

joel=# create table test ( id serial );
NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL
column 'test.id'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'test_id_key' for
table 'test'
joel=# insert into test values (default);
INSERT 16617 1
joel=# select currval('test_id_seq');
 currval
---------
       1
(1 row)

See also nextval() and setval() for other functions for sequences.


Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


Re: SERIAL Field

From
"Rajesh Kumar Mallah."
Date:
Hi Gaetano,

Postgresql implements serial field using sequences.

detailed description of sequences are avaliable at
http://www.postgresql.org/idocs/index.php?functions-sequence.html


a NICE flash tutorial is also available at:
http://techdocs.postgresql.org/college/001_sequences/

the specific answer to your question is the function "currval(sequence_name)"
can be used.
you can get the name of sequenced being used for the serial field by
describing the table (\d tablename).

But the very idea of inserting the value and then retriving the sequence
number sounds fishy to me. Why not get the sequence number first and than
insert using that seqnce number (more on the flash tutorial ).


regds
mallah.


On Monday 06 May 2002 04:16 pm, Gaetano Mendola wrote:
> Hi all,
>
> having a table with a serial field what is the corrected method
> to retrieve the value after an insert ?
>
>
>
>
> Ciao
> Gaetano

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.



Re: SERIAL Field

From
"Joel Burton"
Date:
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Rajesh Kumar
> Mallah.
> Sent: Monday, May 06, 2002 9:26 AM
> To: Gaetano Mendola; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] SERIAL Field

> But the very idea of inserting the value and then retriving the sequence
> number sounds fishy to me. Why not get the sequence number first and than
> insert using that seqnce number (more on the flash tutorial ).

Hmmm... does nextval() 'hold' a sequence number for this backend? Otherwise,
the risk would be that you request a value, and someone else has beat you to
it before you insert it.

In any event, inserting then using currval() is the standard practice around
here, and it works great. Nothing fishy at all here, nothing to see, move
on.

J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


Re: SERIAL Field

From
"Joel Burton"
Date:
> -----Original Message-----
> From: Joel Burton [mailto:joel@joelburton.com]
> Sent: Monday, May 06, 2002 10:51 AM
> To: Rajesh Kumar Mallah.; Gaetano Mendola; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] SERIAL Field
>
>
> > -----Original Message-----
> > From: pgsql-admin-owner@postgresql.org
> > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Rajesh Kumar
> > Mallah.
> > Sent: Monday, May 06, 2002 9:26 AM
> > To: Gaetano Mendola; pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] SERIAL Field
>
> > But the very idea of inserting the value and then retriving the
> sequence
> > number sounds fishy to me. Why not get the sequence number
> first and than
> > insert using that seqnce number (more on the flash tutorial ).
>
> Hmmm... does nextval() 'hold' a sequence number for this backend?
> Otherwise, the risk would be that you request a value, and
> someone else has beat you to it before you insert it.

Duh. Of course it does. Forgive me: it's still a bit early here in EST for
my brain to be on.

> In any event, inserting then using currval() is the standard
> practice around here, and it works great. Nothing fishy at all
> here, nothing to see, move on.
>
> J.
>
> Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> Knowledge Management & Technology Consultant


Re: SERIAL Field

From
"Dan Langille"
Date:
On 6 May 2002 at 10:51, Joel Burton wrote:

> > But the very idea of inserting the value and then retriving the sequence
> > number sounds fishy to me. Why not get the sequence number first and than
> > insert using that seqnce number (more on the flash tutorial ).

That's pretty standard in a number of database applications (i.e. not just
PostgreSQL situations).

> Hmmm... does nextval() 'hold' a sequence number for this backend?
> Otherwise, the risk would be that you request a value, and someone else has
> beat you to it before you insert it.

I use nextval all the time.  In fact:

fp2migration=# \d users
 Column | Type       |   Modifiers
------------+--------------------------+--------------------------
 id         | integer     | not null default nextval('users_id_seq'::text)

What's wrong this this?  Nobody is going to "beat me to it".

> In any event, inserting then using currval() is the standard practice
> around here, and it works great. Nothing fishy at all here, nothing to see,
> move on.

Why is that "less risk"?
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: SERIAL Field

From
"Joel Burton"
Date:
> -----Original Message-----
> From: Dan Langille [mailto:dan@langille.org]
> Sent: Monday, May 06, 2002 12:41 PM
> To: Joel Burton
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] SERIAL Field
>
> > In any event, inserting then using currval() is the standard practice
> > around here, and it works great. Nothing fishy at all here,
> nothing to see,
> > move on.
>
> Why is that "less risk"?

It's not; nextval() is just fine. I was half asleep and thinking, I think,
of another database when I wrote that.

"You say nextval() + write, I say write + currval()..."

J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


Re: SERIAL Field

From
"Dan Langille"
Date:
On 6 May 2002 at 12:43, Joel Burton wrote:

> > -----Original Message-----
> > From: Dan Langille [mailto:dan@langille.org]
> > Sent: Monday, May 06, 2002 12:41 PM
> > To: Joel Burton
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] SERIAL Field
> >
> > > In any event, inserting then using currval() is the standard practice
> > > around here, and it works great. Nothing fishy at all here,
> > nothing to see,
> > > move on.
> >
> > Why is that "less risk"?
>
> It's not; nextval() is just fine. I was half asleep and thinking, I think,
> of another database when I wrote that.
>
> "You say nextval() + write, I say write + currval()..."

So long as a given application does not mix the two approaches, everything
should be fine.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Re: SERIAL Field

From
Brian McCane
Date:
My personal experience is that you MUST operate like:

SELECT nextval...
INSERT ...

I have several web sites that have many simultaneous updates occurring,
based on user interaction with the website and its underlying data
structures.  I used to INSERT, then SELECT currval, and depending on
timing, I might get a different sequence number than the one I actually
used.  Apparently I had the following occurring:

INSERT ...
INSERT ...
SELECT currval...
SELECT currval...

In this situation, both of the SELECTs return the same sequence number.
So basically, user1 would update user2's "myarea" data.  This problem
became much more critical when I went to multiple Apache servers on the
front end and had a higher likelihood of concurrency.  However, even on a
single server, you can't guarantee where the task switch will occur and
mess you up.

- brian


On Mon, 6 May 2002, Joel Burton wrote:

>
> > -----Original Message-----
> > From: Dan Langille [mailto:dan@langille.org]
> > Sent: Monday, May 06, 2002 12:41 PM
> > To: Joel Burton
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] SERIAL Field
> >
> > > In any event, inserting then using currval() is the standard practice
> > > around here, and it works great. Nothing fishy at all here,
> > nothing to see,
> > > move on.
> >
> > Why is that "less risk"?
>
> It's not; nextval() is just fine. I was half asleep and thinking, I think,
> of another database when I wrote that.
>
> "You say nextval() + write, I say write + currval()..."
>
> J.
>
> Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> Knowledge Management & Technology Consultant
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"



Re: SERIAL Field

From
Joel Stevenson
Date:
>  > "You say nextval() + write, I say write + currval()..."
>
>So long as a given application does not mix the two approaches, everything
>should be fine.

Though, if you have multiple clients inserting into the table, there
is a chance that between client A inserting the record and then
calling currval(), client B could have performed an insert, in which
case client A gets the value of client B's record.  'nextval() +
write' avoids this possible overlap.
--
Joel Stevenson
Bear River Associates, Inc.

Re: SERIAL Field

From
Tom Lane
Date:
Joel Stevenson <jstevenson@bearriver.com> writes:
> Though, if you have multiple clients inserting into the table, there
> is a chance that between client A inserting the record and then
> calling currval(), client B could have performed an insert, in which
> case client A gets the value of client B's record.  'nextval() +
> write' avoids this possible overlap.

People who have not studied the documentation frequently make that
claim.  *There is no such risk* (unless A and B are interleaving their
commands on a single database connection, in which case they have lots
worse risks of conflicts than this one).  See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-sequence.html

            regards, tom lane

Re: SERIAL Field

From
Joel Stevenson
Date:
>
>People who have not studied the documentation frequently make that
>claim.  *There is no such risk* (unless A and B are interleaving their
>commands on a single database connection, in which case they have lots
>worse risks of conflicts than this one).  See
>http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-sequence.html
>
>            regards, tom lane

Point taken.  I've worked on web-apps which use a single JDBC
connection per user session where the overlap could occur, but as you
say there are far greater issues involved for such session-based
connections.
--
Joel Stevenson
Bear River Associates, Inc.