Re: SERIAL Field

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"