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

--
jozzano@exa.unicen.edu.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 trigger�s 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 it�s necessary, drop me a line. Regards
Jose Luis Ozzano.

From pgsql-bugs-owner@postgresql.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@postgresql.org>; Fri, 20 Jul 2001 11:45:49 -0400 (EDT)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@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@candle.pha.pa.us>
Message-Id: <200107201545.f6KFjeE15500@candle.pha.pa.us>
Subject: Re: check contraint allows illegal value?
In-Reply-To: <slrn9lebha.o1.lee@troll.east.rochester.k12.ny.us>
"from Lee Harr at Jul 19, 2001 07:00:59 pm"
To: missive@hotmail.com
Date: Fri, 20 Jul 2001 11:45:40 -0400 (EDT)
CC: pgsql-bugs@postgresql.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026