_pkey SOS!!!!

Hi,
there's beer in it for the person who solves this one.

I have a postgres database running under redhat which I access over a LAN through SSH.
I had a problem with a table a couple of weeks ago and simply copied over with an older version of that table which had fewer records in.

Silly me didn't realise that the _pkey table would consequently be screwed up
I discovered this through vacuumdb - here's what I got back -

------------------------------------------

NOTICE: Index profiles_pkey: NUMBER OF INDEX' TUPLES (989) IS NOT THE SAME AS HEAP' (821).
Recreate the index.
NOTICE: Index profiles_pkey: NUMBER OF INDEX' TUPLES (989) IS NOT THE SAME AS HEAP' (821).
Recreate the index.
-----------------------------------------------------------------------------------

my big question is therefore - How do I recreate the index?????????


any help would be much appreciated

Thanks

It sounds to me like the index is still referencing the old table, not the NEW one that you just copied over. That's why it's wack. Not to worry, nothing is broken; messing with indexes is usually pretty safe. There are several things we could do in this case.

First, here's the REINDEX command:

http://www.commandprompt.com/ppbook/r27795.htm

Here's an excerpt from the page, see the link above for a full description:



I am assuming in this case your command would be something like :



But notice from the description above, you'll need to shut down Postgres and restart it in standalone mode, before running that command.

Usually I just drop and recreate indexes, unless they are insanely big, since it's usually pretty safe to drop them. But since I don't know if your index is a special kind of index, (like if it's a function based index) try out the REINDEX command above and let me know how it works out. If it doesn't then we can consider just dropping the thing and recreating it.

Some people even drop and recreate indexes now and then as a matter of performance tuning. (For example if you have a table with a alot of deletions in it, the index may not be optimized for that table).

Thanks Ted,

That worked a treat - REINDEX INDEX profiles_pkey; created a new index for the profiles table and now I can update as previous.

Many many thanks


David

Awesome!

Glad to hear that worked out for you.