Hello, I a small database (about 80 new records daily), I'm running postgresql
7.1.2 running in a RedHat Box 7.1, and the primary key is updated by an
automatic secuence, my application is in C languaje and I use the following
instruction to insert each row res = PQexec(conn, data) and the value of data
is "INSERT INTO captura (registro) VALUES ( 'expected data......') ", each
record has 4 columns: (the primary key is consecutivo)
Attribute | Type
| Modifier
---------+-------------------+--------------------------------------------
consecutivo | integer | not null default
nextval('"captura_consecutivo_seq"'::text)
fechahora | timestamp with time zone | not null default now()
registro | character varying(150) |
procesado | character(1) | not null default 'N'
everything worked perfectly by about 6 weeks but one day my application didn't
work because 'duplicate key in unique index´ I checked the problem and it was
because the value of consecutivo in the last record was 3 numbres higher than
the actual value of captura_consecutivo_seq.
maybe the problem was becase somebody reset the computer without doing a
shutdown.
I wan't some help in the following questions:
1) How was this possible do I need to make a commit after each INSERT?? or a
transaction is needed to update at the same time the record and the
secuence???
2) I want to make sure this simple integrity of data is kept always, is there
some way to guarantee this???? (I'm thinking in other applications with
Postgresql)
3) Maybe the simple solution in this case is to get rid of the primary key and
keeping the consecutivo column as a not unique field, (I need the field for
sorting purposes) is these OK??? (I mean normally in databases a primary key
is some kind of recommended, I was thinking that the secuence was the perfect
primary key but now I'm not sure).
Thanks in advace to your helping.
Jesus Sandoval