Skip site navigation (1) Skip section navigation (2)

Re: Not incrementing the 'serial' counter on failed inserts.

From: Louis Bertrand <louis(at)bertrandtech(dot)on(dot)ca>
To: Arcady Genkin <antipode(at)thpoon(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Not incrementing the 'serial' counter on failed inserts.
Date: 2001-03-13 13:40:02
Message-ID: Pine.BSO.4.20.0103130818470.792-100000@grendel.bts (view raw or flat)
Thread:
Lists: pgsql-novice
Arcady,

I'd make the id field just an integer and use a separate sequence:
 CREATE SEQUENCE idnumber;
 SELECT setval( 'idnumber', nnnn); /* nnnn is your starting number */

Perform the insert, if successful then update the row:
 UPDATE a 
   SET serial = nextval('idnumber') 
   WHERE foo=$newfoo AND bar=$newbar; /* variables hold new values */

Look into bracketing both operations within a BEGIN-COMMIT block. Not sure
how that works with sequences, but it might keep that number from
incrementing if you do it the way you wrote.

Ciao
 --Louis  <louis(at)bertrandtech(dot)on(dot)ca> 


On 10 Mar 2001, Arcady Genkin wrote:

> I have a (probably generic) problem: a table like
> 
> create table a (
>        id serial,
>        foo text,
>        bar text,
>        unique( foo, bar ) );
> 
> >From a PHP script, I do an INSERT and then check by the return value
> of pg_exec() function whether the insert failed because the entry
> already exists.  The problem is that the sequence on the 'id' field
> gets incremented even if the insert fails.
> 
> What's the typical way of addressing this problem?
> 
> I thought about doing a SELECT, and then INSERT only if the SELECT
> returns 0 rows.  But then there is a possibility that in between the
> SELECT and INSERT queries some other client will do an INSERT on the
> same values, and then my INSERT will fail (again, incrementing the
> 'id').
> 
> Many thanks for any input,
> -- 
> Arcady Genkin
> Nostalgia isn't what it used to be.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> 


In response to

pgsql-novice by date

Next:From: Brett W. McCoyDate: 2001-03-13 14:26:22
Subject: Re: Fwd: Silly question about numbering of rows?
Previous:From: Scott MuirDate: 2001-03-13 11:02:47
Subject: FW: look-up cache failures

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group