Thread: URGENT: Index problems
I added a hash index to a varchar value and when i vacuumed i got the following error: Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME AS HEAP' (9181). dropping and recreating the index gave me the same error (on this index only) dropping the database and reimporting the data and then recreating the index and vacuuming gave me the same error. any ideas ? ... and does anyone know what the error means ? thanks in advance. Steve
okay. i followed the instructions very carefully and get the following error when i try to run pg_upgrade .. Cannot find database template1 in ./data/base. Are you running ./pg_upgrade as the postgres superuser? i am running as superuser and template1 is not there. Steve "Steve Brett" <steve.brett@e-mis.com> wrote in message news:9qjgpp$3h3$1@news.tht.net... > I added a hash index to a varchar value and when i vacuumed i got the > following error: > > Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME AS > HEAP' (9181). > > dropping and recreating the index gave me the same error (on this index > only) > > dropping the database and reimporting the data and then recreating the index > and vacuuming gave me the same error. > > any ideas ? ... and does anyone know what the error means ? > > thanks in advance. > > Steve > >
apparently this is the fix:
psql -d mydb -c "select * from title;" > before
# Save for after cmp test...
cd /usr/local/pgsql # my install root...
vacuumdb mydb # clean up before moving...
cp -pr data data.backup # make a backup...
pg_dumpall -s > schema.sql # dump schema w/out data...
killall postmaster # stop the server...
sleep 3
mv data data.old # set it aside...
cd /usr/src/pgsql/src # to the src tree...
gmake install # reinstall binaries...
cd /usr/local/pgsql # back to my install root...
initdb # recreate template1, sys
stuff...
postmaster -i -o "-F -S 4096 -s" >& log & # restart server...
sleep 3
pg_upgrade -f schema.sql data >& upgrade.log # reload schema...
cp -p data.old/base/mydb/* data/base/mydb/ # replace data...
psql -d mydb -c "select * from title;" > after
# verify we still have data...
diff before after # quick sanity check...
will let you know if it works ...
"Steve Brett" <steve.brett@e-mis.com> wrote in message
news:9qjgpp$3h3$1@news.tht.net...
> I added a hash index to a varchar value and when i vacuumed i got the
> following error:
>
> Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME AS
> HEAP' (9181).
>
> dropping and recreating the index gave me the same error (on this index
> only)
>
> dropping the database and reimporting the data and then recreating the
index
> and vacuuming gave me the same error.
>
> any ideas ? ... and does anyone know what the error means ?
>
> thanks in advance.
>
> Steve
>
>
"Steve Brett" <steve.brett@e-mis.com> writes:
> i followed the instructions very carefully and get the following error when
> i try to run pg_upgrade ..
pg_upgrade hasn't worked since 7.0. Where did you find instructions
that told you to run it?
>> I added a hash index to a varchar value and when i vacuumed i got the
>> following error:
>>
>> Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME AS
>> HEAP' (9181).
If you have any rows that contain NULL in the indexed column, then this
result isn't very surprising, because hash indexes don't index nulls.
(Current sources have been fixed not to issue the cross-check notice
message for hash indexes, btw.)
Personally I'd advise not bothering with hash indexes; use a plain btree
index instead. Does more, works better, doesn't have concurrency
problems.
regards, tom lane
many thanks for the help. i found the instructions on a mailing list via a search of the web. i eventually added a btree index but was under the (probably mistaken!) impression that hash indexes were better for varchar values. once again many thanks, Steve "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:26967.1003331234@sss.pgh.pa.us... > "Steve Brett" <steve.brett@e-mis.com> writes: > > i followed the instructions very carefully and get the following error when > > i try to run pg_upgrade .. > > pg_upgrade hasn't worked since 7.0. Where did you find instructions > that told you to run it? > > >> I added a hash index to a varchar value and when i vacuumed i got the > >> following error: > >> > >> Index customer_ha_hash: NUMBER OF INDEX' TUPLES (9176) IS NOT THE SAME AS > >> HEAP' (9181). > > If you have any rows that contain NULL in the indexed column, then this > result isn't very surprising, because hash indexes don't index nulls. > (Current sources have been fixed not to issue the cross-check notice > message for hash indexes, btw.) > > Personally I'd advise not bothering with hash indexes; use a plain btree > index instead. Does more, works better, doesn't have concurrency > problems. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly