Thread: Re: [BUGS] uniqueness not always correct

Re: [BUGS] uniqueness not always correct

From
Vadim Mikheev
Date:
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

Re: [HACKERS] Re: [BUGS] uniqueness not always correct

From
Tom Lane
Date:
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

Re: [HACKERS] Re: [BUGS] uniqueness not always correct

From
Frank Cusack
Date:
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

Re: [BUGS] uniqueness not always correct

From
Bruce Momjian
Date:
> 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

Re: [BUGS] uniqueness not always correct

From
Bruce Momjian
Date:
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

Re: [HACKERS] Re: [BUGS] uniqueness not always correct

From
Bruce Momjian
Date:
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

Re: [HACKERS] Re: [BUGS] uniqueness not always correct

From
Bruce Momjian
Date:
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

Oracle Compatibility (Translate function)

From
Edwin Ramirez
Date:
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




Re: [HACKERS] Oracle Compatibility (Translate function)

From
Thomas Lockhart
Date:
> 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


Re: [HACKERS] Re: [BUGS] uniqueness not always correct

From
Bruce Momjian
Date:
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,
Pennsylvania19026
 


Re: [HACKERS] Re: [BUGS] uniqueness not always correct

From
Tom Lane
Date:
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


Re: [HACKERS] Re: [BUGS] uniqueness not always correct

From
Bruce Momjian
Date:
> 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,
Pennsylvania19026
 


Re: [BUGS] uniqueness not always correct

From
Bruce Momjian
Date:
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,
Pennsylvania19026
 


Re: [BUGS] uniqueness not always correct

From
Bruce Momjian
Date:
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,
Pennsylvania19026