Thread: Backend died while dropping index

The backend died while I was dropping an index on a table. Now I cannot
access my data! Help!

My table looks like

create table user_usage (email varchar(100),date date);

I created the index:
CREATE INDEX user_usage_email_idx on user_usage(email);

When I dropped the index, the backend died. Now whenever I try to do an
insert I get:

ERROR:  mdopen: couldn't open user_usage_email_idx: No such file
ordirectory

How do I fix this? This is on 6.5.

Thanks,
Brian
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/
AIM: bbaquiran

brianb-pggeneral@edsamail.com wrote:
>
> The backend died while I was dropping an index on a table. Now I cannot
> access my data! Help!
>
> My table looks like
>
> create table user_usage (email varchar(100),date date);
>
> I created the index:
> CREATE INDEX user_usage_email_idx on user_usage(email);
>
> When I dropped the index, the backend died. Now whenever I try to do an
> insert I get:
>
> ERROR:  mdopen: couldn't open user_usage_email_idx: No such file
> ordirectory
>
> How do I fix this? This is on 6.5.
>
> Thanks,
> Brian

The easiest way to deal with this, is, as the "postgres" user:

1. Change to the location of the datafiles, which, for a
database, called "mydatabase" will look something like:

cd /usr/local/pgsql/data/base/mydatabase

2. Create an empty file of the name of the index:

touch user_usage_email_idx

3. Delete the file using psql:

psql mydatabase

> DROP INDEX user_usage_email_idx;

4. Recreate the index as appropriate

Hope that helps,

Mike Mascari

Mike Mascari <mascarm@mascari.com> writes:
> brianb-pggeneral@edsamail.com wrote:
>> How do I fix this? This is on 6.5.

> 1. Change to the location of the datafiles, which, for a
> database, called "mydatabase" will look something like:
> 2. Create an empty file of the name of the index:
> 3. Delete the file using psql:
> 4. Recreate the index as appropriate

And

5. Update to 7.0.2 at your earliest convenience, if not sooner.

No smileys here.  I've noticed several people lately saying they're
still on 6.5 (or earlier :-() because they're "waiting for 7.0 to
settle out".  Wake up and smell the roses folks.  7.0 certainly has
bugs, and maybe a few teething pains yet to go, but it has far fewer
bugs than any prior release --- and most of the bugs it does have
are undoubtedly present in prior releases as well.  Holding off on
an update isn't buying you anything except continued exposure to the
hundreds of bugs we've swatted since 6.5.

            regards, tom lane

I respectfully disagree Tom, for various reasons:

1)  Postgres 6.5.3 is a known quantity, and there are more people familiar
with
the common, critical problems, so there are more people who can help solve
problems.
2) 7.0.2 undoubtedly has some new bugs and problems that are not
   apparent yet - bugs that could sink a commercial business that depends
   on Postgres (like mine)
3) In some cases (perhaps not 6.5.3, but some), the entire database must be
    dumped and restored, which is difficult for a commercial business to
"just do it".
4) Various 3rd party applications and various exotic queries need to be
    verified against the new platform.
5) A variety of the common existing queries need to be re-evaluated and
compared
   in terms of speed, since there have been several reports of 'slowness' in
queries.

I'm sure you're frustrated by us laggards.  But frankly, my entire business
depends on
Postgres, and I'm not going to risk it just because there's an upgrade that
may or
may not fix some of my problems, and will undoubtedly create new ones.  If
you can
get the guys from Great Bridge to send out general announcements that they
have
tested it, and are satisfied that it will work properly, then I will
consider upgrading
sooner.

jb



----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Mike Mascari" <mascarm@mascari.com>
Cc: <brianb-pggeneral@edsamail.com>; <pgsql-general@postgresql.org>
Sent: Sunday, June 18, 2000 1:12 AM
Subject: Re: [GENERAL] Backend died while dropping index


> Mike Mascari <mascarm@mascari.com> writes:
> > brianb-pggeneral@edsamail.com wrote:
> >> How do I fix this? This is on 6.5.
>
> > 1. Change to the location of the datafiles, which, for a
> > database, called "mydatabase" will look something like:
> > 2. Create an empty file of the name of the index:
> > 3. Delete the file using psql:
> > 4. Recreate the index as appropriate
>
> And
>
> 5. Update to 7.0.2 at your earliest convenience, if not sooner.
>
> No smileys here.  I've noticed several people lately saying they're
> still on 6.5 (or earlier :-() because they're "waiting for 7.0 to
> settle out".  Wake up and smell the roses folks.  7.0 certainly has
> bugs, and maybe a few teething pains yet to go, but it has far fewer
> bugs than any prior release --- and most of the bugs it does have
> are undoubtedly present in prior releases as well.  Holding off on
> an update isn't buying you anything except continued exposure to the
> hundreds of bugs we've swatted since 6.5.
>
> regards, tom lane

> 1)  Postgres 6.5.3 is a known quantity, and there are more people
familiar
> with
> the common, critical problems, so there are more people who can help
solve
> problems.

> 2) 7.0.2 undoubtedly has some new bugs and problems that are not
>    apparent yet - bugs that could sink a commercial business that depends
>    on Postgres (like mine)


  I'll throw in my two cents, your experience hopefully won't be the same
as ours.

  This week, 6.5.3 somehow horked it's own data quite thoroughly for us.
Vacuuming the table would fail miserably - even trying to *describe* the
tables shut down the back end.  It would complain about a tuple being too
large - yet the largest tuple in the table was around 6K.  Pg_dump and
pg_dumpall would also promptly shut down the back end as soon as they tried
to touch the table.  Boy, that makes it fun.

  The cause (we suspect) is from one day when another programmer here did
something really dumb, and filled up the disk.  The docs for 7.0.2 say that
it is supposed to be more reliable in situations like that, and so we
decided that it was simply too dangerous to stay with a version that can
mangle it's own data beyond repair.

   So, there are arguments on both sides of the coin.  It's critical for
your database to be running at all times, so you don't want to bring it
down to upgrade.  At the same time, the upgrade may buy you a better chance
of staying up - or it might break everything.  You'll have to make an
educated guess, and possibly just flip a coin.

   And, of course, one of the prime rules of system administration is to
make changes reversible.  Back up the old version and the data, libraries,
interfaces, etc..  If something goes terribly wrong during the upgrade, you
can always switch back to the old version and be up and running in just a
few minutes....

  Ah - Here's one more thing, just in case it benefits anyone.  A good
upgrade procedure is to take a different machine (even if it's pretty
low-end) that is similar in OS and hardware, and install the new version on
it first.  Copy the data over.  *Then* test the things that are likely to
break, so that you don't have any surprises.

steve