Thread: _pkey SOS!!!!

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Location
    london
    Posts
    2
    Rep Power
    0

    _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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Arrow


    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:

    Synopsis
    REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

    Description
    Use the REINDEX command to rebuild any indices that have become corrupt. This is especially useful if system indices become corrupted. To fix them, shutdown postmaster and start it using the -o "-O -P" command-line parameter. This opens a standalone server that allows for re-indexing of system indices. Run the REINDEX DATABASE command once you are at the psql prompt.
    I am assuming in this case your command would be something like :

    REINDEX INDEX profiles_pkey;
    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).
    Last edited by Ted Striker; June 23rd, 2002 at 05:40 AM.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Location
    london
    Posts
    2
    Rep Power
    0

    Smile Thanks


    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Thumbs up


    Awesome!

    Glad to hear that worked out for you.

IMN logo majestic logo threadwatch logo seochat tools logo