Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group