Обсуждение: 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(??!):
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
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
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
> 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
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
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
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
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
> 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
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
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
> 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
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
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