pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with sequence increment
3+ messages / 3 participants
[nested] [flat]
* Problem with sequence increment
@ 2012-01-25 14:12 Francisco Calderón <fjcalderon@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Francisco Calderón @ 2012-01-25 14:12 UTC (permalink / raw)
To: pgsql-sql
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.
//
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with sequence increment
@ 2012-01-25 14:31 Brice André <brice@famille-andre.be>
parent: Francisco Calderón <fjcalderon@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Brice André @ 2012-01-25 14:31 UTC (permalink / raw)
To: Francisco Calderón <fjcalderon@gmail.com>; +Cc: pgsql-sql
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 <fjcalderon@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.
> //
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with sequence increment
@ 2012-01-25 14:31 ktm@rice.edu <ktm@rice.edu>
parent: Francisco Calderón <fjcalderon@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: ktm@rice.edu @ 2012-01-25 14:31 UTC (permalink / raw)
To: Francisco Calderón <fjcalderon@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-01-25 14:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-25 14:12 Problem with sequence increment Francisco Calderón <fjcalderon@gmail.com>
2012-01-25 14:31 ` Brice André <brice@famille-andre.be>
2012-01-25 14:31 ` ktm@rice.edu <ktm@rice.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox