Subject: Problems with indeces ???

From: [email-masked] (Janne Soimasuo)
Date: Tue, 24 May 94 14:13:38 EET DST

Hi netters !

I think I have some kind of problem with postgres indeces or am I doing 
something wrong ?

I have a forest inventory database, which have among others following 
tables (classes): 
ObsUni(ID=int4, Name=char16, SDate=abstime,
                EDate=abstime, CDate=abstime, LayerId=char16, ObjectId=int4, 
                Typ_OU=int2, ObsUni=int4, RefPoi=int4 )
and
ObsVal(ObsUni=int4, Int_Val=int2,Value=float8)

ObsUni class have about 7000 tuples and ObsVal ~ 70 000.

ObsUni is joined with ObsVal, Typ_OU, ObsUni and RefPoi classes.

To make queries faster I made some indeces:
1st btree:
define index ObsUni_ind1b on ObsUni using btree (ID int4_ops)\g
define index ObsUni_ind5b on ObsUni using btree (Typ_OU int2_ops)\g
define index ObsUni_ind6b on ObsUni using btree (ObsUni int4_ops)\g

define index ObsVal_ind1b on ObsVal using btree (ObsUni int4_ops) \g
define index ObsVal_ind2b on ObsVal using btree (Int_Val int2_ops) \g

and then hash:
define index ObsUni_ind1h on ObsUni using hash (ID int4_ops)\g
define index ObsUni_ind5h on ObsUni using hash (Typ_OU int2_ops)\g
define index ObsUni_ind6h on ObsUni using hash (ObsUni int4_ops)\g

define index ObsVal_ind1h on ObsVal using hash (ObsUni int4_ops) \g
define index ObsVal_ind2h on ObsVal using hash (Int_Val int2_ops) \g

The system fails when I try to create hash indeces. Is there something wrong ?

Another problem is that when I make a query like:
retrieve (ObsUni.all) where ObsUni.ObsUni NOTNULL
I got answer:
FATAL: no response from backend: detected in PQexec

This query runs well if I don't have any indeces with class ObsUni but
after I've made those I got an error.

I've tried this with Linux and with SunOS, but no solution !
Postgres version is 4.2.beta

Thanks in advance !

Janne Soimasuo
Faculty of Forestry
University of Joensuu, Finland
[email-masked]

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================