Please Cc: to teo@flex.ro

Hello all,

I think I have found a bug in unique index.
The bug has been tested with:
-PostgreSQL 6.5 final version running on a RedHat 5.2 i386
-PostgreSQL 6.5 final version running on a RedHat 6.0 i386
-PostgreSQL 6.4.2 running on a RedHat 5.2 i386
and the results are the same.

Create a test database and create the following table:
CREATE TABLE "livrari" (
  "nr_npr" int4,
  "data_npr" date,
  "nr_ordin" int4,
  "sursa" character varying(32),
  "destinatie" character varying(32),
  "produs" character varying(8),
  "spatii_sursa" text,
  "caracteristici" text,
  "calitate" character varying(16),
  "nr_transport" character varying(16),
  "delegat" character varying(32),
  "brut" float8,
  "tara" float8,
  "net" float8,
  "cod_operatiune" int4,
  "pret" float8,
  "tva" float8,
  "qwerty12345ytrewq54321" int4); 

Then try creating the following unique index :

test=> create unique index livrari_unic on livrari (nr_npr, data_npr,
nr_ordin, sursa, destinatie, produs, spatii_sursa, caracteristici,
calitate, nr_transport, delegat);
CREATE

Now try to select the records from table (actually no records, nothing
has been inserted):

test=> select * from livrari;
ERROR:  index_info: no amop 403 655369 1

I thought that my database is somehow corrupted and tried to vacuum it :

test=> vacuum analyze;
VACUUM

Now, I was trying to select again the records:

test=> select * from livrari;
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible.  Terminating. 

Are the some limitations on the number and type of the fields contained
by the index?
Is there some mistakes that I make?

Please Cc: to teo@flex.ro

Best regards,
-- 
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA

Tue, 20 Jul 1999 19:52:47 +0300
Constantin Teodorescu <teo@flex.ro>
BUG found in unique index!
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Constantin
> Teodorescu
> Sent: Wednesday, July 21, 1999 1:53 AM
> To: pgsql-hackers@postgreSQL.org
> Subject: [HACKERS] BUG found in unique index!
> 
> 
> Please Cc: to teo@flex.ro
> 
> Hello all,
> 
> I think I have found a bug in unique index.
> The bug has been tested with:
> -PostgreSQL 6.5 final version running on a RedHat 5.2 i386
> -PostgreSQL 6.5 final version running on a RedHat 6.0 i386
> -PostgreSQL 6.4.2 running on a RedHat 5.2 i386
> and the results are the same.
> 
> Create a test database and create the following table:
> CREATE TABLE "livrari" (
>   "nr_npr" int4,
>   "data_npr" date,
>   "nr_ordin" int4,
>   "sursa" character varying(32),
>   "destinatie" character varying(32),
>   "produs" character varying(8),
>   "spatii_sursa" text,
>   "caracteristici" text,
>   "calitate" character varying(16),
>   "nr_transport" character varying(16),
>   "delegat" character varying(32),
>   "brut" float8,
>   "tara" float8,
>   "net" float8,
>   "cod_operatiune" int4,
>   "pret" float8,
>   "tva" float8,
>   "qwerty12345ytrewq54321" int4); 
> 
> Then try creating the following unique index :
> 
> test=> create unique index livrari_unic on livrari (nr_npr, data_npr,
> nr_ordin, sursa, destinatie, produs, spatii_sursa, caracteristici,
> calitate, nr_transport, delegat);
> CREATE
>

Your index has 11 columns.
Currently indices could have <= 8(7 ?) columns.
It seems create index should cause an error in this case. 

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Wed, 21 Jul 1999 08:49:31 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] BUG found in unique index!