>> > bbrmdc=> create index mdc1_simu_pk on mdc1_simu using btree (
>> > bbrmdc-> runnum char_ops );
>> ^^^^^^^^
>> bpchar_ops must be used !!!
>> char_ops is for 'char' data type, not for 'char(N)'.
>> But it's much better to DON'T USE ANY XXX_ops at all (and USING btree too -
>> btree is default) - both features aren't standard and useless in your case.
>>
Okay, I destroyed the database and recreated it. I then created
the following tables and indices;
bbrmdc=> create table mdc1_simu (
bbrmdc-> runnum char(6) not null,
bbrmdc-> version varchar(10) not null,
bbrmdc-> jobgrp varchar(8) not null,
bbrmdc-> bldrnum int4 not null,
bbrmdc-> status text,
bbrmdc-> cpusecs int4,
bbrmdc-> outsize int4,
bbrmdc-> machine text,
bbrmdc-> location text,
bbrmdc-> jobdate abstime,
bbrmdc-> who text,
bbrmdc-> note text );
CREATE
bbrmdc=> create table mdc1_runs (
bbrmdc-> runnum char(6) not null,
bbrmdc-> runtype text,
bbrmdc-> nevents int4,
bbrmdc-> who text,
bbrmdc-> note text );
CREATE
bbrmdc=> create unique index mdc1_runs_pk on mdc1_runs ( runnum );
CREATE
bbrmdc=> create index mdc1_simu_pk on mdc1_simu ( runnum );
CREATE
bbrmdc=> create index mdc1_simu_ver on mdc1_simu ( version );
CREATE
I then filled the tables from my Perl DBI script copying Oracle
data to Postgres (same as before). This time, it worked without
failing do the index FATAL.
I immediatetly tried my subselect.
bbrmdc=> select distinct runtype from mdc1_runs where
bbrmdc-> runnum in (select runnum from mdc1_simu where version = '3.1.0');
After a couple of minutes, I killed the postgres process. I quit my
psql and then reconnectd. I tried a simple select and it hung too.
Killed it and reconnected. I dropped the three indices and tried a
vacuum. It also hung forever. I killed the postgres process,
restarted the postmaster, deleted the pg_vlock file, and retried the
vacuum. It worked. A simple select then works too.
I recreated the indices exactly as above, and selects still
work. The subselect also worked too and took about 12 seconds.
I destroyed the database and started over. This time, after
transfering the data, I first tried a simple select. It worked
fine. Then the subselect. It hung again. Killed and reconnected.
A simple select also hangs. Killed it, restarted the postmaster,
reconnected and did a vacuum. Now both simple select and
subselect work fine.
Any clues?
pr
--
_________________________________________________________________________
Paul Raines raines@slac.stanford.edu 650-926-2369
Stanford Linear Accelerator BABAR Group Software Team
http://www.slac.stanford.edu/~raines/index.html <======== PGP public key
