Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id C14C412E1D38
	for <pgsql-sql@postgresql.org>; Wed, 25 Jan 2012 10:32:04 -0400 (AST)
Received: from mh1.mail.rice.edu ([128.42.201.20])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ktm@aart.rice.edu>) id 1Rq3tM-0003KC-0j
	for pgsql-sql@postgresql.org; Wed, 25 Jan 2012 14:32:04 +0000
Received: from mh1.mail.rice.edu (localhost.localdomain [127.0.0.1])
	by mh1.mail.rice.edu (Postfix) with ESMTP id A2CF4291CF6;
	Wed, 25 Jan 2012 08:31:51 -0600 (CST)
Received: from mh1.mail.rice.edu (localhost.localdomain [127.0.0.1])
	by mh1.mail.rice.edu (Postfix) with ESMTP id 95FDB297613;
	Wed, 25 Jan 2012 08:31:51 -0600 (CST)
X-Virus-Scanned: by amavis-2.6.4 at mh1.mail.rice.edu, auth channel
Received: from mh1.mail.rice.edu ([127.0.0.1])
	by mh1.mail.rice.edu (mh1.mail.rice.edu [127.0.0.1]) (amavis,
	port 10026)
	with ESMTP id rf5vnCDpVDez; Wed, 25 Jan 2012 08:31:51 -0600 (CST)
X-SMTP-Auth: no
X-SMTP-Auth: no
Received: from aart.rice.edu (aart.rice.edu [168.7.56.48])
	by mh1.mail.rice.edu (Postfix) with ESMTP id 5B7D4291CF6;
	Wed, 25 Jan 2012 08:31:51 -0600 (CST)
Received: by aart.rice.edu (Postfix, from userid 18612)
	id D782B100240; Wed, 25 Jan 2012 08:31:49 -0600 (CST)
Date: Wed, 25 Jan 2012 08:31:49 -0600
From: "ktm@rice.edu" <ktm@rice.edu>
To: Francisco =?iso-8859-1?Q?Calder=F3n?= <fjcalderon@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with sequence increment
Message-ID: <20120125143149.GF10228@aart.rice.edu>
References: 
 <CAFVakMosc3T5WmhQqwDLQEkPRhChA9KoOUVQKGUR-3xwXBVOSg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
In-Reply-To: 
 <CAFVakMosc3T5WmhQqwDLQEkPRhChA9KoOUVQKGUR-3xwXBVOSg@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-12-10)
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -4.2 (----)
X-Archive-Number: 201201/103
X-Sequence-Number: 36274

On Wed, Jan 25, 2012 at 09:42:05AM -0430, Francisco Calder=F3n wrote:
> Hello,
>=20
> 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 ta=
lk
> for me ;)
>=20
> -----------SQL-----------
> CREATE TABLE tb
> (
>   id serial NOT NULL,
>   descripcion character varying(200) NOT NULL,
>   CONSTRAINT tb_pkey PRIMARY KEY (id)
> )
> WITH (OIDS=3DFALSE);
> 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=3DFALSE);
>=20
> When i make an insert like this:
>=20
> INSERT INTO ta (descripcion, tb_id) values ('prueba', 0);
>=20
> we can expect this error:
>=20
> ERROR:  insert or update on table "ta" violates foreign key constraint
> "ta_tb_id"
> DETAIL:  Key (tb_id)=3D(0) is not present in table "tb".
>=20
> 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?
>=20

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