[SQL] Problem with sequence increment

[Francisco Calderón]

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.
//

Re: [SQL] Problem with sequence increment

[Brice André]

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 

> 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.
> //
>

Re: [SQL] Problem with sequence increment

[k...@rice.edu]

On Wed, Jan 25, 2012 at 09:42:05AM -0430, Francisco Calderón wrote:
> 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?
> 

That is how sequences work. If you want different behavior, use another
process or method to produce a sequential count. Hint, it will involve
a lot of locking and be much slower than a sequence. It would be better
to have your application handle gaps in the sequence.

Regards,
Ken

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql