Thread: uniqueness not always correct
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
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
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
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. 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
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
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 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
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
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
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
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
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