SERIAL Field

От:

"Gaetano Mendola" <mendola@bigfoot.com>

Дата:

6 мая 2002 г. в 06:46:24

Hi all,

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




Ciao
Gaetano

-- 
#exclude 
#include 
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");

Re: SERIAL Field

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

6 мая 2002 г. в 16:28:57

Joel Stevenson  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

От:

"Dan Langille" <dan@langille.org>

Дата:

6 мая 2002 г. в 13:20:17

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

От:

"Dan Langille" <dan@langille.org>

Дата:

6 мая 2002 г. в 14:49:06

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

От:

Brian McCane <bmccane@mccons.net>

Дата:

6 мая 2002 г. в 15:18:33

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

От:

"Rajesh Kumar Mallah." <mallah@trade-india.com>

Дата:

6 мая 2002 г. в 10:10:41

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

От:

Joel Stevenson <jstevenson@bearriver.com>

Дата:

6 мая 2002 г. в 15:49:31

>  > "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

От:

Joel Stevenson <jstevenson@bearriver.com>

Дата:

6 мая 2002 г. в 17:15:15

>
>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.

Re: SERIAL Field

От:

"Joel Burton" <joel@joelburton.com>

Дата:

6 мая 2002 г. в 10:07:29

> 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

От:

"Joel Burton" <joel@joelburton.com>

Дата:

6 мая 2002 г. в 11:30:38

> -----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

От:

"Joel Burton" <joel@joelburton.com>

Дата:

6 мая 2002 г. в 11:29:57

> -----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

От:

"Joel Burton" <joel@joelburton.com>

Дата:

6 мая 2002 г. в 13:20:31

> -----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