Thread: UNIQUE constraint no longer works under 6.5.1
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Mark Dalphin
Your email address : mdalphin@amgen.com
System Configuration
---------------------
Architecture (example: Intel Pentium) : SGI Octane
Operating System (example: Linux 2.0.26 ELF) : Irix 6.5
PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-6.5.1
Compiler used (example: gcc 2.8.0) : native cc
Please enter a FULL description of your problem:
------------------------------------------------
Using the UNIQUE constraint in a TABLE definition no longer does anything.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
The following is the output from my using '\i' to read in my SQL. Notice
that there is no longer any comment about creating an index to perform
the UNIQUE constraint on the field, 'Login'.
Testing with INSERT confirms that I can add duplicates.
CREATE SEQUENCE mkPersonID;
CREATE
CREATE TABLE Person (
PersonID int PRIMARY KEY DEFAULT nextval('mkPersonID'),
DateAdded timestamp -- Date user added to system
NOT NULL DEFAULT getTimeStamp(),
Login char(40) UNIQUE NOT NULL, -- User's NT Login name
CONSTRAINT validLogin CHECK (RTrim(Login) <> '')
);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'person_pkey' for
table 'person'
CREATE
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
--
Mark Dalphin email: mdalphin@amgen.com
Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
One Amgen Center Drive +1-805-375-0680 (home)
Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
Mark Dalphin <mdalphin@amgen.com> writes:
> Using the UNIQUE constraint in a TABLE definition no longer does anything.
Interesting. Playing with some variants of your example shows that
UNIQUE works fine *unless* there is another column marked PRIMARY KEY.
Then the UNIQUE constraint is ignored. Looks like a simple logic bug in
the table-definition expander.
A look at the CVS logs reveals this apparently related entry for
parser/analyze.c:
revision 1.102
date: 1999/05/12 07:17:18; author: thomas; state: Exp; lines: +68 -24
Fix problem with multiple indices defined if using column- and table-
constraints. Reported by Tom Lane.
Now, check for duplicate indices and retain the one which is a primary-key.
Thomas, do you recall what that was all about? I don't offhand...
regards, tom lane
> Interesting. Playing with some variants of your example shows that
> UNIQUE works fine *unless* there is another column marked PRIMARY KEY.
> Then the UNIQUE constraint is ignored. Looks like a simple logic bug in
> the table-definition expander.
> A look at the CVS logs reveals this apparently related entry for
> parser/analyze.c:
> revision 1.102
> date: 1999/05/12 07:17:18; author: thomas; state: Exp; lines: +68 -24
> Fix problem with multiple indices defined if using column- and table-
> constraints. Reported by Tom Lane.
> Now, check for duplicate indices and retain the one which is a primary-key.
Yow! The problem reported earlier (by you, so you share some blame! ;)
was that if one specified a primary key *and* a unique constraint, and
they both pointed to the same column, then you got two indices
created. So I tried to go through the list of indices and drop any
which seemed to be the same as the primary key index.
I apparently hadn't tested for this reported case (obviously :() but
it should be easy to fix. I'll look at it soon, unless someone already
has.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> > Interesting. Playing with some variants of your example shows that
> > UNIQUE works fine *unless* there is another column marked PRIMARY KEY.
> > Then the UNIQUE constraint is ignored. Looks like a simple logic bug in
> > the table-definition expander.
> > A look at the CVS logs reveals this apparently related entry for
> > parser/analyze.c:
> > revision 1.102
> > date: 1999/05/12 07:17:18; author: thomas; state: Exp; lines: +68 -24
> > Fix problem with multiple indices defined if using column- and table-
> > constraints. Reported by Tom Lane.
> > Now, check for duplicate indices and retain the one which is a primary-key.
> Yow! The problem reported earlier (by you, so you share some blame! ;)
> was that if one specified a primary key *and* a unique constraint, and
> they both pointed to the same column, then you got two indices
> created. So I tried to go through the list of indices and drop any
> which seemed to be the same as the primary key index.
OK, the immediate problem was due to a cut and paste typo (I was
comparing column names to decide if indices were identical, and the
pointer to the name was set to be the same for both index elements).
But, the code which was in there was always a bit wimpy; it only
checked for duplicate indices if they both had only one column. I've
modified it to (I think) check for any number of columns, so
constraints like
create table t1 (i int, j int, unique(i,j), primary key(i,j))
should also work correctly by swallowing the "unique" index.
Here is a patch, to be applied in src/backend/parser/. Let me know if
it fixes your problem and any other cases you can think of, and I'll
apply it to the tree(s).
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California*** analyze.c.orig Sat Aug 14 16:40:36 1999
--- analyze.c Sat Aug 14 23:38:14 1999
***************
*** 579,584 ****
--- 579,587 ----
columns = NIL;
dlist = NIL;
+ /*
+ * Run through each primary element in the table creation clause
+ */
while (elements != NIL)
{
element = lfirst(elements);
***************
*** 588,593 ****
--- 591,597 ----
column = (ColumnDef *) element;
columns = lappend(columns, column);
+ /* Special case SERIAL type? */
if (column->is_sequence)
{
char *sname;
***************
*** 625,630 ****
--- 629,635 ----
blist = lcons(sequence, NIL);
}
+ /* Check for column constraints, if any... */
if (column->constraints != NIL)
{
clist = column->constraints;
***************
*** 815,842 ****
* or if a SERIAL column was defined along with a table PRIMARY KEY constraint.
* - thomas 1999-05-11
*/
! if ((pkey != NULL) && (length(lfirst(pkey->indexParams)) == 1))
{
dlist = ilist;
ilist = NIL;
while (dlist != NIL)
{
! int keep = TRUE;
index = lfirst(dlist);
! /*
! * has a single column argument, so might be a conflicting
! * index...
! */
! if ((index != pkey)
! && (length(index->indexParams) == 1))
{
! char *pname = ((IndexElem *) lfirst(index->indexParams))->name;
! char *iname = ((IndexElem *) lfirst(index->indexParams))->name;
! /* same names? then don't keep... */
! keep = (strcmp(iname, pname) != 0);
}
if (keep)
--- 820,862 ----
* or if a SERIAL column was defined along with a table PRIMARY KEY constraint.
* - thomas 1999-05-11
*/
! if (pkey != NULL)
{
dlist = ilist;
ilist = NIL;
while (dlist != NIL)
{
! List *pcols, *icols;
! int plen, ilen;
! int keep = TRUE;
index = lfirst(dlist);
+ pcols = pkey->indexParams;
+ icols = index->indexParams;
! plen = length(pcols);
! ilen = length(icols);
!
! /* Not the same as the primary key? Then we should look... */
! if ((index != pkey) && (ilen == plen))
{
! keep = FALSE;
! while ((pcols != NIL) && (icols != NIL))
! {
! IndexElem *pcol = lfirst(pcols);
! IndexElem *icol = lfirst(icols);
! char *pname = pcol->name;
! char *iname = icol->name;
! /* different names? then no match... */
! if (strcmp(iname, pname) != 0)
! {
! keep = TRUE;
! break;
! }
! pcols = lnext(pcols);
! icols = lnext(icols);
! }
}
if (keep)