Re: [BUGS] uniqueness not always correct - Mailing list pgsql-hackers

Never mind.  Fixed.  I had forgotten to add a line to pg_amproc.h.  So
obvious, hard to imagine how I could have missed it...  :-)


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


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