Solaris 2.6/sparc; postgres 6.5.1

dns=> create table test (zone int4, net cidr, unique(zone, net));
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
CREATE
dns=> insert into test (zone, net) values (1, '1.2.3/24');
INSERT 21750 1
dns=> insert into test (zone, net) values (1, '2.3.4/24');
INSERT 21751 1
dns=> insert into test (zone, net) values (1, '1.2.3/24');
INSERT 21752 1
dns=> insert into test (zone, net) values (1, '2.3.4/24');
ERROR:  Cannot insert a duplicate key into a unique index
dns=> select * from test;
zone|net     
- ----+--------
   1|1.2.3/24
   1|2.3.4/24
   1|1.2.3/24
(3 rows)


Once a unique error is reported, uniqueness seems to be maintained.
Also, if you enter 4 values, then try a duplicate, it all works.

The threshold seems to be 3.

A select before the duplicate add also seems to fix it.

~f



Thu, 11 Nov 1999 01:14:03 -0800
Frank Cusack <fcusack@iconnet.net>
uniqueness not always correct
Frank Cusack wrote:
> 
> Solaris 2.6/sparc; postgres 6.5.1
> 
> dns=> create table test (zone int4, net cidr, unique(zone, net));
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> CREATE
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21750 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> INSERT 21751 1
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21752 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> ERROR:  Cannot insert a duplicate key into a unique index

Yes, I reproduced this (Solaris 2.5/sparc). 
Seems like CIDR problem(??!):

ais=> create table test (zone int4, net int4, unique(zone, net));
                                        ^^^^
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
CREATE
ais=> insert into test (zone, net) values (1, 1);
INSERT 7712479 1
ais=> insert into test (zone, net) values (1, 2);
INSERT 7712480 1
ais=> insert into test (zone, net) values (1, 1);
ERROR:  Cannot insert a duplicate key into a unique index

Vadim

Thu, 11 Nov 1999 17:07:25 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [BUGS] uniqueness not always correct
Vadim Mikheev <vadim@krs.ru> writes:
> Yes, I reproduced this (Solaris 2.5/sparc). 
> Seems like CIDR problem(??!):

Yes.  Looks like the low-order bits of a CIDR address are garbage,
but network_cmp() compares them as though all bits are significant.
So, indeed, it may think two different instances of '1.2.3/24'
are not equal.

The regular inet comparison functions at least *try* to mask out
garbage bits, but I think they get it wrong too --- they should be
taking the smaller of ip_bits(a1) and ip_bits(a2) as the number of
bits to compare.  They don't.  Thus, for example,

regression=> select '1.2.5/16'::cidr < '1.2.3/24'::cidr;
?column?
--------
f
(1 row)

which looks wrong to me.

In short, it's a bug in the inet data types, not a generic problem
with unique indexes.

			regards, tom lane

Thu, 11 Nov 1999 11:57:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: [BUGS] uniqueness not always correct 
I'm not sure that a '<' comparison is really meaningful for inet/cidr?
At least not the '<' comparison you are doing. For networks (cf hosts),
the only really meanininful operators are '<<' (contained within), etc.

A nice easy fix might be to make sure that the unmasked portion of the
data is set to all 0's when storing the data.

~f
ps. I'm not subscribed to the lists so this will probably bounce. Please
repost for me.

>>>>> On Thu, 11 Nov 1999, "Tom" == Tom Lane wrote:

  Tom> Vadim Mikheev <vadim@krs.ru> writes:

  +> Yes, I reproduced this (Solaris 2.5/sparc).  Seems like CIDR
  +> problem(??!):

  Tom> Yes.  Looks like the low-order bits of a CIDR address are garbage,
  Tom> but network_cmp() compares them as though all bits are significant.
  Tom> So, indeed, it may think two different instances of '1.2.3/24' are
  Tom> not equal.

  Tom> The regular inet comparison functions at least *try* to mask out
  Tom> garbage bits, but I think they get it wrong too --- they should be
  Tom> taking the smaller of ip_bits(a1) and ip_bits(a2) as the number of
  Tom> bits to compare.  They don't.  Thus, for example,

  Tom> regression=> select '1.2.5/16'::cidr < '1.2.3/24'::cidr;
  Tom> ?column?
  Tom> --------
  Tom> f
  Tom> (1 row)

  Tom> which looks wrong to me.

  Tom> In short, it's a bug in the inet data types, not a generic problem
  Tom> with unique indexes.

  Tom> regards, tom lane
>>>>> On Thu, 11 Nov 1999,
>>>>> "Tom" == Tom Lane wrote:

  Tom> Vadim Mikheev <vadim@krs.ru> writes:

  +> Yes, I reproduced this (Solaris 2.5/sparc).
  +> Seems like CIDR problem(??!):

  Tom> Yes.  Looks like the low-order bits of a CIDR address are garbage,
  Tom> but network_cmp() compares them as though all bits are significant.
  Tom> So, indeed, it may think two different instances of '1.2.3/24'
  Tom> are not equal.

  Tom> The regular inet comparison functions at least *try* to mask out
  Tom> garbage bits, but I think they get it wrong too --- they should be
  Tom> taking the smaller of ip_bits(a1) and ip_bits(a2) as the number of
  Tom> bits to compare.  They don't.  Thus, for example,

  Tom> regression=> select '1.2.5/16'::cidr < '1.2.3/24'::cidr;
  Tom> ?column?
  Tom> --------
  Tom> f
  Tom> (1 row)

  Tom> which looks wrong to me.

  Tom> In short, it's a bug in the inet data types, not a generic problem
  Tom> with unique indexes.

  Tom> regards, tom lane

Thu, 11 Nov 1999 12:50:59 -0800
Frank Cusack <fcusack@iconnet.net>
Re: [HACKERS] Re: [BUGS] uniqueness not always correct 
> Frank Cusack wrote:
> > 
> > Solaris 2.6/sparc; postgres 6.5.1
> > 
> > dns=> create table test (zone int4, net cidr, unique(zone, net));
> > NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> > CREATE
> > dns=> insert into test (zone, net) values (1, '1.2.3/24');
> > INSERT 21750 1
> > dns=> insert into test (zone, net) values (1, '2.3.4/24');
> > INSERT 21751 1
> > dns=> insert into test (zone, net) values (1, '1.2.3/24');
> > INSERT 21752 1
> > dns=> insert into test (zone, net) values (1, '2.3.4/24');
> > ERROR:  Cannot insert a duplicate key into a unique index
> 
> Yes, I reproduced this (Solaris 2.5/sparc). 
> Seems like CIDR problem(??!):

I see a more serious problem in the current source tree:
	
	test=> create table test (zone int4, net cidr, unique(zone, net));
	NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
	CREATE
	test=> insert into test (zone, net) values (1, '1.2.3/24');
	ERROR:  fmgr_info: function 0: cache lookup failed

Seems something is broken with CIDR, but not INET:
	
	test=> create table test2 (x inet unique(x));  
	ERROR:  parser: parse error at or near "("
	test=> create table test2 (x inet, unique(x));
	NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test2_x_key' for table 'test2'
	CREATE
	test=> insert into test2 values ('1.2.3.4/24');
	INSERT 19180 1
	test=> create table test3 (x cidr, unique(x));
	NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test3_x_key' for table 'test3'
	CREATE
	test=> insert into test3 values ('1.2.3.4/24');
	ERROR:  fmgr_info: function 0: cache lookup failed

The problem appears to be in _bt_mkscankey() and index_getprocid().

Any ideas?

Backtrace shows:

---------------------------------------------------------------------------

#0  elog (lev=-1, fmt=0x817848e "fmgr_info: function %u: cache lookup failed")
    at elog.c:94
#1  0x8135a47 in fmgr_info (procedureId=0, finfo=0x830a060) at fmgr.c:225
#2  0x80643f9 in ScanKeyEntryInitialize (entry=0x830a058, flags=0, 
    attributeNumber=2, procedure=0, argument=137404148) at scankey.c:65
#3  0x8083e70 in _bt_mkscankey (rel=0x8312230, itup=0x8309ee8) at nbtutils.c:56
#4  0x8079989 in _bt_doinsert (rel=0x8312230, btitem=0x8309ee8, 
    index_is_unique=1 '\001', heapRel=0x82dfd38) at nbtinsert.c:52
#5  0x807eabe in btinsert (rel=0x8312230, datum=0x8309b28, 
    nulls=0x830a020 "  ", ht_ctid=0x8309e2c, heapRel=0x82dfd38) at nbtree.c:358
#6  0x81358d8 in fmgr_c (finfo=0x80476e8, values=0x80476f8, 
    isNull=0x80476df "") at fmgr.c:146
#7  0x8135c25 in fmgr (procedureId=331) at fmgr.c:336
#8  0x8073c6d in index_insert (relation=0x8312230, datum=0x8309b28, 
    nulls=0x830a020 "  ", heap_t_ctid=0x8309e2c, heapRel=0x82dfd38)
    at indexam.c:211
#9  0x80ae3d9 in ExecInsertIndexTuples (slot=0x8309bf8, tupleid=0x8309e2c, 
    estate=0x8309950, is_update=0) at execUtils.c:1206
#10 0x80aa77e in ExecAppend (slot=0x8309bf8, tupleid=0x0, estate=0x8309950)
    at execMain.c:1178
#11 0x80aa60e in ExecutePlan (estate=0x8309950, plan=0x83098b0, 
    operation=CMD_INSERT, offsetTuples=0, numberTuples=0, 
    direction=ForwardScanDirection, destfunc=0x817cdc4) at execMain.c:1024


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Wed, 8 Dec 1999 05:57:53 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] uniqueness not always correct

Can someone comment on this?  Can someone submit a patch?  I remember
something about not clearing bits somewhere.

I can't reproduce the problem on BSD/OS.


> Frank Cusack wrote:
> > 
> > Solaris 2.6/sparc; postgres 6.5.1
> > 
> > dns=> create table test (zone int4, net cidr, unique(zone, net));
> > NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> > CREATE
> > dns=> insert into test (zone, net) values (1, '1.2.3/24');
> > INSERT 21750 1
> > dns=> insert into test (zone, net) values (1, '2.3.4/24');
> > INSERT 21751 1
> > dns=> insert into test (zone, net) values (1, '1.2.3/24');
> > INSERT 21752 1
> > dns=> insert into test (zone, net) values (1, '2.3.4/24');
> > ERROR:  Cannot insert a duplicate key into a unique index
> 
> Yes, I reproduced this (Solaris 2.5/sparc). 
> Seems like CIDR problem(??!):
> 
> ais=> create table test (zone int4, net int4, unique(zone, net));
>                                         ^^^^
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> CREATE
> ais=> insert into test (zone, net) values (1, 1);
> INSERT 7712479 1
> ais=> insert into test (zone, net) values (1, 2);
> INSERT 7712480 1
> ais=> insert into test (zone, net) values (1, 1);
> ERROR:  Cannot insert a duplicate key into a unique index
> 
> Vadim
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Wed, 8 Dec 1999 06:45:07 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] uniqueness not always correct
Someone submitted this patch.  It should fix your problem.  It will
appear in the next release.

> Solaris 2.6/sparc; postgres 6.5.1
> 
> dns=> create table test (zone int4, net cidr, unique(zone, net));
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> CREATE
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21750 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> INSERT 21751 1
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21752 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> ERROR:  Cannot insert a duplicate key into a unique index
> dns=> select * from test;
> zone|net     
> - ----+--------
>    1|1.2.3/24
>    1|2.3.4/24
>    1|1.2.3/24
> (3 rows)
> 
> 
> Once a unique error is reported, uniqueness seems to be maintained.
> Also, if you enter 4 values, then try a duplicate, it all works.
> 
> The threshold seems to be 3.
> 
> A select before the duplicate add also seems to fix it.
> 
> ~f
> 
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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
Wed, 15 Dec 1999 20:29:10 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] uniqueness not always correct
Fixed by recently submitted patch.


> Vadim Mikheev <vadim@krs.ru> writes:
> > Yes, I reproduced this (Solaris 2.5/sparc). 
> > Seems like CIDR problem(??!):
> 
> Yes.  Looks like the low-order bits of a CIDR address are garbage,
> but network_cmp() compares them as though all bits are significant.
> So, indeed, it may think two different instances of '1.2.3/24'
> are not equal.
> 
> The regular inet comparison functions at least *try* to mask out
> garbage bits, but I think they get it wrong too --- they should be
> taking the smaller of ip_bits(a1) and ip_bits(a2) as the number of
> bits to compare.  They don't.  Thus, for example,
> 
> regression=> select '1.2.5/16'::cidr < '1.2.3/24'::cidr;
> ?column?
> --------
> f
> (1 row)
> 
> which looks wrong to me.
> 
> In short, it's a bug in the inet data types, not a generic problem
> with unique indexes.
> 
> 			regards, tom lane
> 
> ************
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Wed, 15 Dec 1999 20:29:31 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [BUGS] uniqueness not always correct
Never mind.  Patch is fir mac, not inet.

> Vadim Mikheev <vadim@krs.ru> writes:
> > Yes, I reproduced this (Solaris 2.5/sparc). 
> > Seems like CIDR problem(??!):
> 
> Yes.  Looks like the low-order bits of a CIDR address are garbage,
> but network_cmp() compares them as though all bits are significant.
> So, indeed, it may think two different instances of '1.2.3/24'
> are not equal.
> 
> The regular inet comparison functions at least *try* to mask out
> garbage bits, but I think they get it wrong too --- they should be
> taking the smaller of ip_bits(a1) and ip_bits(a2) as the number of
> bits to compare.  They don't.  Thus, for example,
> 
> regression=> select '1.2.5/16'::cidr < '1.2.3/24'::cidr;
> ?column?
> --------
> f
> (1 row)
> 
> which looks wrong to me.
> 
> In short, it's a bug in the inet data types, not a generic problem
> with unique indexes.
> 
> 			regards, tom lane
> 
> ************
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Wed, 15 Dec 1999 20:42:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [BUGS] uniqueness not always correct
Never mind.  Patch is fir mac, not inet.  Sorry.

Problem still exists.

> Solaris 2.6/sparc; postgres 6.5.1
> 
> dns=> create table test (zone int4, net cidr, unique(zone, net));
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> CREATE
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21750 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> INSERT 21751 1
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21752 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> ERROR:  Cannot insert a duplicate key into a unique index
> dns=> select * from test;
> zone|net     
> - ----+--------
>    1|1.2.3/24
>    1|2.3.4/24
>    1|1.2.3/24
> (3 rows)
> 
> 
> Once a unique error is reported, uniqueness seems to be maintained.
> Also, if you enter 4 values, then try a duplicate, it all works.
> 
> The threshold seems to be 3.
> 
> A select before the duplicate add also seems to fix it.
> 
> ~f
> 
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Wed, 15 Dec 1999 20:43:11 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] uniqueness not always correct
Hello,

    I have modified the translate function in order to improve its
compatibility with Oracle.  It now supports the replacement of multiple
characters and it will also shorten the length of the string when characters
are replaced with nothing.

[Note: The arguments are different from the original translate]
Can this function replace the existing function in the distribution?

-------NEW FUNCTION--------------------------------------
text *
translate(text *string, text *from, text *to)
{
        text       *ret;
        char       *ptr_ret, *from_ptr, *to_ptr, *source, *target, *temp,
rep;
        int        m, fromlen, tolen, retlen, i;

        if ((string == (text *) NULL) ||
                ((m = VARSIZE(string) - VARHDRSZ) <= 0))
                return string;

        target   = (char *) palloc(VARSIZE(string) - VARHDRSZ);
        source   = VARDATA(string);
        temp     = target;

        fromlen = VARSIZE(from) - VARHDRSZ;
        from_ptr = VARDATA(from);
        tolen = VARSIZE(to) - VARHDRSZ;
        to_ptr   = VARDATA(to);
        retlen = 0;
        while (m--)
        {
          rep = *source;
          for(i=0;i<fromlen;i++) {
            if(from_ptr[i] == *source)  {
              if(i < tolen) {
                rep = to_ptr[i];
              } else {
                rep = 0;
              }
              break;
            }
          }
          if(rep != 0) {
            *target++ = rep;
            retlen++;
          }
          source++;
        }

        ret = (text *) palloc(retlen + VARHDRSZ);
        VARSIZE(ret) = retlen + VARHDRSZ;
        ptr_ret = VARDATA(ret);
        for(i=0;i<retlen;i++) {
          *ptr_ret++ = temp[i];
        }
        pfree(target);
        return ret;
}


Thanks,
Edwin S. Ramirez



Thu, 16 Dec 1999 09:24:14 -0500
Edwin Ramirez <ramirez@doc.mssm.edu>
Oracle Compatibility (Translate function)
> I have modified the translate function in order to improve its
> compatibility with Oracle.  It now supports the replacement of 
> multiple characters and it will also shorten the length of the string 
> when characters are replaced with nothing.
> [Note: The arguments are different from the original translate]
> Can this function replace the existing function in the distribution?

afaik yes. Does anyone have a problem with this (it allows
substitution of multiple characters)? I think the system tables will
need to be updated; I'll do this within the next week or so if noone
else has already taken this on.

btw, there is some chance that when we go to native support for
NATIONAL CHARACTER etc then TRANSLATE() will need to become SQL92
compliant (and basically a different function). But that is an issue
for later, and we may be able to solve it without having to give up on
the Oracle version.

                  - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 17 Dec 1999 17:21:57 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Oracle Compatibility (Translate function)
I have just applied a user patch to fix this reported problem.


> Vadim Mikheev <vadim@krs.ru> writes:
> > Yes, I reproduced this (Solaris 2.5/sparc). 
> > Seems like CIDR problem(??!):
> 
> Yes.  Looks like the low-order bits of a CIDR address are garbage,
> but network_cmp() compares them as though all bits are significant.
> So, indeed, it may think two different instances of '1.2.3/24'
> are not equal.
> 
> The regular inet comparison functions at least *try* to mask out
> garbage bits, but I think they get it wrong too --- they should be
> taking the smaller of ip_bits(a1) and ip_bits(a2) as the number of
> bits to compare.  They don't.  Thus, for example,
> 
> regression=> select '1.2.5/16'::cidr < '1.2.3/24'::cidr;
> ?column?
> --------
> f
> (1 row)
> 
> which looks wrong to me.
> 
> In short, it's a bug in the inet data types, not a generic problem
> with unique indexes.
> 
> 			regards, tom lane
> 
> ************
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Tue, 7 Mar 2000 17:50:57 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [BUGS] uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have just applied a user patch to fix this reported problem.

If you had read the followup, you would have seen that I have doubts
about this patch, and in fact Ryan acknowledges that it probably doesn't
do the right thing for INET.  I think there is more work to do here.

			regards, tom lane

Tue, 07 Mar 2000 18:22:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: [BUGS] uniqueness not always correct 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I have just applied a user patch to fix this reported problem.
> 
> If you had read the followup, you would have seen that I have doubts
> about this patch, and in fact Ryan acknowledges that it probably doesn't
> do the right thing for INET.  I think there is more work to do here.

Reversed out.

"I never met a patch I didn't like."  :-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Tue, 7 Mar 2000 20:44:21 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Re: [BUGS] uniqueness not always correct
This bug appears to still exist in 7.0:
	
	test=> create table test (zone int4, net cidr, unique(zone, net));
	NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key'
	for table 'test'
	CREATE
	test=> insert into test (zone, net) values (1, '1.2.3/24');
	INSERT 22157 1
	test=> insert into test (zone, net) values (1, '2.3.4/24');
	INSERT 22158 1
	test=> select * from test;
	 zone |   net    
	------+----------
	    1 | 1.2.3/24
	    1 | 2.3.4/24
	(2 rows)
	
	test=> insert into test (zone, net) values (1, '2.3.4/24');
	INSERT 22159 1
	test=> 


> Solaris 2.6/sparc; postgres 6.5.1
> 
> dns=> create table test (zone int4, net cidr, unique(zone, net));
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> CREATE
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21750 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> INSERT 21751 1
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21752 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> ERROR:  Cannot insert a duplicate key into a unique index
> dns=> select * from test;
> zone|net     
> - ----+--------
>    1|1.2.3/24
>    1|2.3.4/24
>    1|1.2.3/24
> (3 rows)
> 
> 
> Once a unique error is reported, uniqueness seems to be maintained.
> Also, if you enter 4 values, then try a duplicate, it all works.
> 
> The threshold seems to be 3.
> 
> A select before the duplicate add also seems to fix it.
> 
> ~f
> 
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Wed, 31 May 2000 19:28:34 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: uniqueness not always correct
This is Vadim's comment on the bug.


> Frank Cusack wrote:
> > 
> > Solaris 2.6/sparc; postgres 6.5.1
> > 
> > dns=> create table test (zone int4, net cidr, unique(zone, net));
> > NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> > CREATE
> > dns=> insert into test (zone, net) values (1, '1.2.3/24');
> > INSERT 21750 1
> > dns=> insert into test (zone, net) values (1, '2.3.4/24');
> > INSERT 21751 1
> > dns=> insert into test (zone, net) values (1, '1.2.3/24');
> > INSERT 21752 1
> > dns=> insert into test (zone, net) values (1, '2.3.4/24');
> > ERROR:  Cannot insert a duplicate key into a unique index
> 
> Yes, I reproduced this (Solaris 2.5/sparc). 
> Seems like CIDR problem(??!):
> 
> ais=> create table test (zone int4, net int4, unique(zone, net));
>                                         ^^^^
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> CREATE
> ais=> insert into test (zone, net) values (1, 1);
> INSERT 7712479 1
> ais=> insert into test (zone, net) values (1, 2);
> INSERT 7712480 1
> ais=> insert into test (zone, net) values (1, 1);
> ERROR:  Cannot insert a duplicate key into a unique index
> 
> Vadim
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Wed, 31 May 2000 19:28:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> This bug appears to still exist in 7.0:
> 	test=> create table test (zone int4, net cidr, unique(zone, net));

Yeah.  IIRC, the issue is that the CIDR data-type-specific btree
comparison function looks at all bits in the datatype, including bits
that are past the specified length (/24, here) and weren't necessarily
zeroed by the datatype input routine.  It's not clear whether the
comparator or the input routine or both are wrong --- *should* those
bits be significant, or not?

The discussion about how to fix it bogged down, and apparently
no one did anything.  I recall feeling that we had some confusion
between what the semantics of CIDR and INET types ought to be,
but I don't understand them well enough to know what they should do.
Right now the same operators are used for both, which seems like it
can't be right.

I was hoping someone would dig through the archives or talk to Paul
Vixie again and come away with a clear understanding of the semantics
of these two datatypes (and why we need two, if we do).

Alternatively, if no one cares enough about these types to even
understand what they should do, maybe we should rip 'em out?

			regards, tom lane

Wed, 31 May 2000 22:13:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: uniqueness not always correct 
Tom Lane writes:

[CIDR and INET]
> Alternatively, if no one cares enough about these types to even
> understand what they should do, maybe we should rip 'em out?

Actually, I'm a happy user of these types, so that would certainly make me
unhappy...

CIDR stores network addresses, so '10.8/16' might be some network. INET
stores both host addresses and, optionally, the network it's in, so
'10.8.7.6/16' is the given host in the network '10.8/16'. Alternatively,
INET '10.8.7.6' is just a host with no network. IMO, there is one of two
bugs in the CIDR input routine:

1) '10.8.7.6/16' in not rejected

2) Since it is accepted, at least the hidden fields need to be zeroed.

(But note that this bug is only exposed when you use the type improperly
in the first place.)

Using the same operators for cidr and inet is fine as long as this is
fixed.


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Sat, 3 Jun 2000 01:48:23 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: uniqueness not always correct 
I can confirm this is fixed in the current source tree, to be released
as 7.1 in a few months:


test=> create table test (zone int4, net cidr, unique(zone, net));
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key'
for table 'test'
CREATE
test=> insert into test (zone, net) values (1, '1.2.3/24');
INSERT 19822 1
test=> insert into test (zone, net) values (1, '2.3.4/24');
INSERT 19823 1
test=> insert into test (zone, net) values (1, '1.2.3/24');
ERROR:  Cannot insert a duplicate key into unique index test_zone_key
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
test=> insert into test (zone, net) values (1, '2.3.4/24');
ERROR:  Cannot insert a duplicate key into unique index test_zone_key
test=> 


> Solaris 2.6/sparc; postgres 6.5.1
> 
> dns=> create table test (zone int4, net cidr, unique(zone, net));
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> CREATE
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21750 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> INSERT 21751 1
> dns=> insert into test (zone, net) values (1, '1.2.3/24');
> INSERT 21752 1
> dns=> insert into test (zone, net) values (1, '2.3.4/24');
> ERROR:  Cannot insert a duplicate key into a unique index
> dns=> select * from test;
> zone|net     
> - ----+--------
>    1|1.2.3/24
>    1|2.3.4/24
>    1|1.2.3/24
> (3 rows)
> 
> 
> Once a unique error is reported, uniqueness seems to be maintained.
> Also, if you enter 4 values, then try a duplicate, it all works.
> 
> The threshold seems to be 3.
> 
> A select before the duplicate add also seems to fix it.
> 
> ~f
> 
> 
> 
> ************
> 
> 


-- 
  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

Fri, 29 Sep 2000 22:16:44 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: uniqueness not always correct
Yes, I can confirm this is now fixed.


> Frank Cusack wrote:
> > 
> > Solaris 2.6/sparc; postgres 6.5.1
> > 
> > dns=> create table test (zone int4, net cidr, unique(zone, net));
> > NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> > CREATE
> > dns=> insert into test (zone, net) values (1, '1.2.3/24');
> > INSERT 21750 1
> > dns=> insert into test (zone, net) values (1, '2.3.4/24');
> > INSERT 21751 1
> > dns=> insert into test (zone, net) values (1, '1.2.3/24');
> > INSERT 21752 1
> > dns=> insert into test (zone, net) values (1, '2.3.4/24');
> > ERROR:  Cannot insert a duplicate key into a unique index
> 
> Yes, I reproduced this (Solaris 2.5/sparc). 
> Seems like CIDR problem(??!):
> 
> ais=> create table test (zone int4, net int4, unique(zone, net));
>                                         ^^^^
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_zone_key' for table 'test'
> CREATE
> ais=> insert into test (zone, net) values (1, 1);
> INSERT 7712479 1
> ais=> insert into test (zone, net) values (1, 2);
> INSERT 7712480 1
> ais=> insert into test (zone, net) values (1, 1);
> ERROR:  Cannot insert a duplicate key into a unique index
> 
> Vadim
> 
> ************
> 
> 


-- 
  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

Fri, 29 Sep 2000 22:18:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: uniqueness not always correct