Secuence not updated
От | Jesus Sandoval |
---|---|
Тема | Secuence not updated |
Дата | |
Msg-id | 3BF80401.59829735@mzt.megared.net.mx обсуждение исходный текст |
Ответ на | Are WALs affected by 'duplicate key' errors? ("Jeff Boes" <jboes@nexcerpt.com>) |
Ответы |
Re: Secuence not updated
|
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: