serial columns & loads misfeature?

From: Kevin Brannen <kevinb(at)nurseamerica(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: serial columns & loads misfeature?
Date: 2002-06-28 18:28:20
Message-ID: 3D1CAAC4.2060202@nurseamerica.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm new to Postgres, so sorry if this is easy, but I did search the docs
and could find nothing to answer this...

I've got a Mysql DB that I've dumped out and am trying to insert into a
Pg DB, as we transition. There were a few changes I had to do to the
data, but they were easy--except for the auto_increment columns. :-/

After I created the DB, I inserted the data (thousands of inserts) via
psql. All went well. Then I started testing the changed code (Perl)
and when I went to insert, I got a "dup key" error.

It took me awhile to figure out what was going on, but I can recreate
the problem with:

create table test (s serial, i int);
insert into test values (1,1);
insert into test values (2,2);
insert into test values (3,3);
insert into test (i) values (4);
ERROR: Cannot insert a duplicate key into unique index test_s_key

I was expecting the system to realize new "keys" had been inserted, and
so when the "nextval" that implicitly happens on a serial field is run,
it would "know" that it was too small and return "max(s)+1". [FWIW, my
expectations in this area were set by my experience with Informix and
mysql, both do this; not sure if other RDBMs do.]

I realize I can fix this by writting a Perl/DBI script to read the list
of sequences, and do [conceptually]:

get the list of sequences
from the sequence name, retrieve the table name
n=select max(id)+1 from table;
select setval('seq_name',n) from seq_name;

and things will be fine from here after, but surely this is a common
enough problem after a bulk load that there is something already built
in to handle this and I just don't have it configured correctly (or is
this a bug?).

Oh, this on a RH 7.2 system with Pg 7.1.3.

TIA for any help in understanding this better!
Kevin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2002-06-28 18:45:34 Re: Shared Memory Sizing
Previous Message Thomas Beutin 2002-06-28 17:43:31 Re: One source of constant annoyance identified