Hello, This is a normal behaviour of the sequence. In case of rollback, a sequence is never decremented. Read the note at the end of this page for more info : http://www.postgresql.org/docs/8.1/static/functions-sequence.html
Regards, Brice 2012/1/25 Francisco Calderón <fjcalde...@gmail.com> > Hello, > > I am having a situation with postgresql 8.3, i have two tables, ta and tb, > with a relation "one tb has many ta" and... well, i will let the SQL talk > for me ;) > > -----------SQL----------- > CREATE TABLE tb > ( > id serial NOT NULL, > descripcion character varying(200) NOT NULL, > CONSTRAINT tb_pkey PRIMARY KEY (id) > ) > WITH (OIDS=FALSE); > INSERT INTO tb (descripcion) values ('desc 1'); > INSERT INTO tb (descripcion) values ('desc 2'); > CREATE TABLE ta > ( > id serial NOT NULL, > descripcion character varying(200), > tb_id integer default null, > CONSTRAINT ta_pkey PRIMARY KEY (id), > CONSTRAINT ta_tb_id FOREIGN KEY (tb_id) > REFERENCES tb (id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITH (OIDS=FALSE); > > When i make an insert like this: > > INSERT INTO ta (descripcion, tb_id) values ('prueba', 0); > > we can expect this error: > > ERROR: insert or update on table "ta" violates foreign key constraint > "ta_tb_id" > DETAIL: Key (tb_id)=(0) is not present in table "tb". > > and that is what i am getting but the unusual situation is the sequence > "ta_id_seq" is incrementing every time i get the "violates foreign key > constraint" error and i think this is not a good behavior, what do you > think? > > Thanks in advance. > // > // Francisco J. Calderón S. > // >