Skip site navigation (1) Skip section navigation (2)

Corrupted index/unique column after vacuuming

From: "William M(dot) Shubert" <wms(at)igoweb(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Corrupted index/unique column after vacuuming
Date: 2001-07-10 02:43:24
Message-ID: 3B4A6BCB.14E024E6@igoweb.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi. I was running a postgres script that made modifications to a
database with a column tagged as UNIQUE, and in the middle ran "vacuumdb
--analyze" from another console. "vacuumdb" reported an error along the
lines of "duplicate key" (sorry, I did not save the exact error!). After
that, all attemps to vacuum reported the same error; and I could not
re-create the index for the unique column because I would get the same
"duplicate key" message.

The postgres version:

     wms=> SELECT version();
                                version
     -------------------------------------------------------------
      PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
     (1 row)
     wms=>

I'm running Red Hat 7.0, using the "postgresql-7.0.2-17" package.

The script that was running is very simple, but very strange. It looked
like this:

     CREATE TABLE user_names (
       name CHAR(10) NOT NULL,
       lc_name CHAR(10) NOT NULL UNIQUE
     );
     INSERT INTO user_names (name, lc_name) VALUES ('wms','wms');
     UPDATE user_names SET name = 'wms' WHERE lc_name = 'wms';
     INSERT INTO user_names (name, lc_name) VALUES ('wms','wms');
     UPDATE user_names SET name = 'wms' WHERE lc_name = 'wms';
     INSERT INTO user_names (name, lc_name) VALUES ('wms','wms');
     UPDATE user_names SET name = 'wms' WHERE lc_name = 'wms';
     INSERT INTO user_names (name, lc_name) VALUES
     ('rascal','rascal');
     UPDATE user_names SET name = 'rascal' WHERE lc_name =
     'rascal';
     INSERT INTO user_names (name, lc_name) VALUES ('wms','wms');
     UPDATE user_names SET name = 'wms' WHERE lc_name = 'wms';
     INSERT INTO user_names (name, lc_name) VALUES
     ('rascal','rascal');
     ... continues for another 400,000 lines ...

As you can see, this sets up a table, and then does many inserts and
updates, each in their own transaction. Most of the inserts fail because
they "lc_name" field will conflict with a row already in existance, but
all of the updates succeed and update exactly one row (although in all
but a few cases the update has no effect, because it is assigning the
value to the column that it already has).

I tried to repeat this problem, but couldn't do it...it only happened
once. I realize that this is an old version of Postgres, but I read an
email recently where one of the Postgresql developers was requesting
examples of corrupted index files in version 7.0 or later, so I decided
to send it in anyway. Sorry, but at the time I didn't think and just
dropped the table and re-started...now I wish I'd saved the exact error
messages and preserved the database too!

Thanks for the great database!
--
                                    Bill Shubert (wms(at)igoweb(dot)org)
                                    http://www.igoweb.org/~wms/


pgsql-bugs by date

Next:From: Hiroshi InoueDate: 2001-07-10 03:39:42
Subject: Re: small ODBC problem
Previous:From: NamrataDate: 2001-07-09 12:25:04
Subject: problem with compilation!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group