BUG(fixed) in CREATE TABLE ADD CONSTRAINT...

From: jozzano <jozzano(at)exa(dot)unicen(dot)edu(dot)ar>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG(fixed) in CREATE TABLE ADD CONSTRAINT...
Date: 2001-07-18 09:23:41
Message-ID: 17765721.995448221111.JavaMail.webmail@mail.exa.unicen.edu.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

--
jozzano(at)exa(dot)unicen(dot)edu(dot)ar

Hi !
My system is i686/Linux Mandrake 7.0/Postgresql v-7.0.2.
I found a bug in the sql command ALTER TABLE ADD CONSTRAINT..., when I tried to add a composite foreign key constraint
(a FK with more than one attribute). The problem is in the file identified by
$Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/command.c,v 1.71 2000/04/12 17:14:57 momjian Exp $
in the code lines #1139 to #1150, when the function AlterTableAddConstraint() tries to construct the vector of the triggers tgargs.
>From the position 4 and forward, it must collect the pairs of fk_attrs and pk_attrs (interleaved), but the current code put first all
fk_attrs and then all the pk_attrs, leading to an error.
I fixed the bug and tested the update and now it works well. I send you a "diff -c command.c command.fixed.c" (with the diff :
GNU diffutils version 2.7) and the output is:

*** command.c Sun May 6 21:13:06 2001
--- command.fixed.c Mon Jul 9 19:58:21 2001
***************
*** 19,24 ****
--- 19,25 ----
* manipulating code in the commands/ directory, should go
* someplace closer to the lib/catalog code.
*
+ *
*-------------------------------------------------------------------------
*/
#include "postgres.h"
***************
*** 1138,1152 ****
{
Ident *fk_at = lfirst(list);

! trig.tgargs[count++] = fk_at->name;
}
foreach(list, fkconstraint->pk_attrs)
{
Ident *pk_at = lfirst(list);

! trig.tgargs[count++] = pk_at->name;
}
! trig.tgnargs = count;

scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL);
AssertState(scan != NULL);
--- 1139,1156 ----
{
Ident *fk_at = lfirst(list);

! trig.tgargs[count] = fk_at->name;
! count+=2;
}
+ count = 5;
foreach(list, fkconstraint->pk_attrs)
{
Ident *pk_at = lfirst(list);

! trig.tgargs[count] = pk_at->name;
! count+=2;
}
! trig.tgnargs = (count-1);

scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL);
AssertState(scan != NULL);
***************
*** 1220,1223 ****
LockRelation(rel, lockstmt->mode);

heap_close(rel, NoLock); /* close rel, keep lock */
! }
--- 1224,1227 ----
LockRelation(rel, lockstmt->mode);

heap_close(rel, NoLock); /* close rel, keep lock */
! }

I wish it would help you. If its necessary, drop me a line. Regards
Jose Luis Ozzano.
>From pgsql-bugs-owner(at)postgresql(dot)org Fri Jul 20 13:36:34 2001
Received: from candle.pha.pa.us (candle.navpoint.com [162.33.245.46])
by postgresql.org (8.11.3/8.11.1) with ESMTP id f6KFjna64169
for <pgsql-bugs(at)postgresql(dot)org>; Fri, 20 Jul 2001 11:45:49 -0400 (EDT)
(envelope-from pgman(at)candle(dot)pha(dot)pa(dot)us)
Received: (from pgman(at)localhost)
by candle.pha.pa.us (8.10.1/8.10.1) id f6KFjeE15500;
Fri, 20 Jul 2001 11:45:40 -0400 (EDT)
From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Message-Id: <200107201545(dot)f6KFjeE15500(at)candle(dot)pha(dot)pa(dot)us>
Subject: Re: check contraint allows illegal value?
In-Reply-To: <slrn9lebha(dot)o1(dot)lee(at)troll(dot)east(dot)rochester(dot)k12(dot)ny(dot)us>
"from Lee Harr at Jul 19, 2001 07:00:59 pm"
To: missive(at)hotmail(dot)com
Date: Fri, 20 Jul 2001 11:45:40 -0400 (EDT)
CC: pgsql-bugs(at)postgresql(dot)org
X-Mailer: ELM [version 2.4ME+ PL90 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Archive-Number: 200107/104
X-Sequence-Number: 1886

>
> Is this a bug?
>
> create table foo( c char(2)
> check (c in ('09', '10', '11', '12', 'n/a'))
> );
> CREATE
>
> insert into foo values('09');
> INSERT
>
> insert into foo values('10');
> INSERT
>
> insert into foo values('08');
> ExecAppend: rejected due to CHECK constraint foo_c
>
> insert into foo values('n/a');
> ExecAppend: rejected due to CHECK constraint foo_c
>
>
>
> clearly this was a 'whups' on my part, but it would have
> been nice to know that my constraint was bogus at the
> time the table was created.

That is interesting. You want the check constraint to be a valid value
for the column. I can see char() having this issue. Is this something
we should check for all types folks? Not sure.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Vadim I. Passynkov 2001-07-18 14:08:16 Curious result from text ( netmask ( inet ) ) functions.
Previous Message pgsql-bugs 2001-07-18 08:18:23 libpgtcl doesn't use UTF encoding of TCL