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

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@bertrandtech.on.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@postgresql.org)
>