Thread: SERIAL type does not generate new ID ?

Hi all,

I don't really understand what happens, so I put context, then problem:

1/ Context
----------
I have this table:

CREATE TABLE film (
film_id SERIAL PRIMARY KEY,
film_country_id CHAR(2),
film_country_id2 CHAR(2),
film_country_id3 CHAR(2),
film_country_id4 CHAR(2),
film_ec_certif BOOL DEFAULT 'false',
film_ce_certif BOOL DEFAULT 'false',
film_prod_year INTEGER,
film_eur_support BOOL DEFAULT 'false',
film_media_support BOOL DEFAULT 'false',
film_budgetnat DECIMAL,
film_budgetnat_rate_id CHAR(3),
film_budget DECIMAL,
film_provisoire BOOL DEFAULT 'false',
film_production_id INTEGER,
film_production_id2 INTEGER,
film_production_id3 INTEGER,
film_production_id4 INTEGER
);

CREATE UNIQUE INDEX film_uidx ON film ( film_id );
CREATE INDEX film_production_1 ON film (film_production_id);
CREATE INDEX film_production_2 ON film (film_production_id2);
CREATE INDEX film_production_3 ON film (film_production_id3);
CREATE INDEX film_production_4 ON film (film_production_id4);

Now if I look at it with postgresadmin, I see:
-- postgresAdmin PostgreSQL-Dump
--
-- Serveur: localhost:5432 Base de données: admissions12
---------------------------------------------------------

--
-- Structure de la table 'film'
--

DROP SEQUENCE film_film_id_seq;
CREATE TABLE film (  film_id serial,  film_country_id bpchar,  film_country_id2 bpchar,  film_country_id3 bpchar,
film_country_id4bpchar,  film_ec_certif bool,  film_ce_certif bool,  film_prod_year int4,  film_eur_support bool,
film_media_supportbool,  film_budgetnat numeric,  film_budgetnat_rate_id bpchar,  film_budget numeric,  film_provisoire
bool, film_production_id int4,  film_production_id2 int4,  film_production_id3 int4,  film_production_id4 int4
 
)
;
SELECT setval ('film_film_id_seq', 6);

2/ Problem:

I have this query in PHP:
insert into film

(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
values('FR','','','','2000','f','f','f','f','f')
 

And it gives the following error:
ERROR: Cannot insert a duplicate key into a unique index

3/ Question:

Shouldn't it automagically create an appropriate film_id ?

Right now, I can do it with  $sql_query_film="insert into film
(film_id,film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
 values
(MAX(film_id)+1,'$film_country_id','$film_country_id2','$film_country_id3','$film_country_id4','$film_prod_year','$film_ec_certif','$film_ce_certif','$film_eur_support','$film_media_support','$film_provisoire')";

and it works fine as long as there are no concurrent accesses, but
there must be a better way :-(

This is very puzzling because it seems that this is the only table which 
gives such errors.

Pointers on relevant online documentation accepted, too :-)
Jean-Marc Libs

-- 
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURG    http://www.actimage.net
Professionnel : jeanmarc@actimage.fr 

Jean-Marc Libs wrote:
> 
> I have this table:
> 
> CREATE TABLE film (
>    film_id serial,
> ...
>
> SELECT setval ('film_film_id_seq', 6);
> 
> I have this query in PHP:
> insert into film
>
(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
values('FR','','','','2000','f','f','f','f','f')
 
> 
> And it gives the following error:
> ERROR: Cannot insert a duplicate key into a unique index

You should not have to do anything special with the serial or the
sequence (including setting it to 6).  Possible sources for for your
error:  1) you are resetting the sequence value to 6 when you already
have a row with that value for film_id in the table, or 2) could be the
message is coming from a triggered insert "downstream" from your initial
insert (see your server log).  BTW, 7.0+ tells you *which* index caused
the problem.

Regards,
Ed Loher

On Sat, 10 Jun 2000, Jean-Marc Libs wrote:

Hi all,

> I don't really understand what happens, so I put context, then problem:
> 
> 1/ Context
> ----------
> I have this table:
> 
> CREATE TABLE film (
> film_id SERIAL PRIMARY KEY,
snip
> )
> ;
> SELECT setval ('film_film_id_seq', 6);
> 
> 2/ Problem:
> 
> I have this query in PHP:
> insert into film
>
(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
values('FR','','','','2000','f','f','f','f','f')
 
> 
> And it gives the following error:
> ERROR: Cannot insert a duplicate key into a unique index
> 
> 3/ Question:
> 
> Shouldn't it automagically create an appropriate film_id ?
> 
OK, I guess I figured out what happens: this serial word just creates
some sequence which acts as a counter of sort, which increments whenever
I insert stuff whithout specifying a value for film_id. Only, when
I insert with a specified film_id (as I did when I imported my 
legacy values), the sequence doesn't budge, so it is now out of sync
with the actual count of items in the database.

I'll just SELECT setval to max(film_id) and everything should be OK.

Thanks to those who answered me,

Jean-Marc Libs

-- 
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURG    http://www.actimage.net
Professionnel : jeanmarc@actimage.fr 
Lieu de travail : jml@obs.coe.int