Обсуждение: Not incrementing the 'serial' counter on failed inserts.

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.

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