Thread
-
uniqueness not always correct
Frank Cusack <fcusack@iconnet.net> — 1999-11-11T09:14:03Z
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
-
Re: [BUGS] uniqueness not always correct
Vadim Mikheev <vadim@krs.ru> — 1999-11-11T10:07:25Z
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: [BUGS] uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> — 1999-12-08T10:57:53Z
> 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
Bruce Momjian <pgman@candle.pha.pa.us> — 1999-12-08T11:45:07Z
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: [BUGS] uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> — 1999-12-16T01:29:10Z
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
-
Re: [BUGS] uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> — 1999-12-16T01:43:11Z
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
-
Re: uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> — 2000-05-31T23:28:34Z
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
-
Re: uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> — 2000-05-31T23:28:54Z
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
-
Re: uniqueness not always correct
Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-01T02:13:07Z
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
-
Re: uniqueness not always correct
Peter Eisentraut <peter_e@gmx.net> — 2000-06-02T23:48:23Z
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
-
Re: uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> — 2000-09-30T02:16:44Z
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
-
Re: uniqueness not always correct
Bruce Momjian <pgman@candle.pha.pa.us> — 2000-09-30T02:18:18Z
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