Re: Let's talk up 6.3
От | Paul Raines |
---|---|
Тема | Re: Let's talk up 6.3 |
Дата | |
Msg-id | raines-980401120853.A0117744@flora04 обсуждение исходный текст |
Список | pgsql-hackers |
>> > 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
В списке pgsql-hackers по дате отправления: