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

Not incrementing the 'serial' counter on failed inserts.

From: Arcady Genkin <antipode(at)thpoon(dot)com>
To: pgsql-novice(at)postgreSQL(dot)org
Subject: Not incrementing the 'serial' counter on failed inserts.
Date: 2001-03-10 11:44:37
Message-ID: 87snkl6e6y.fsf@tea.thpoon.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

Responses

pgsql-novice by date

Next:From: Arcady GenkinDate: 2001-03-10 12:27:28
Subject: Not incrementing the 'serial' counter on failed inserts.
Previous:From: wsmuirDate: 2001-03-10 02:45:00
Subject: look-up cache failures

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