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

ERROR: cannot open relation center_out_analog_proc

From: "G(dot) Anthony Reina" <reina(at)nsi(dot)edu>
To: "pgsql-hackers(at)postgreSQL(dot)org" <pgsql-hackers(at)postgreSQL(dot)org>
Subject: ERROR: cannot open relation center_out_analog_proc
Date: 2001-03-06 19:46:57
Message-ID: 3AA53EB1.F6E279D@nsi.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
I was trying to add a column to a table and fill it but ran into a big
error. Apparently now Postgres can't open this table to vacuum or to
select although it does show up when I ask psql to describe the table
(i.e. db01=# /d center_out_analog_proc).

I'm using Postgres 7.0.3 on a PII/400 MHz with RedHat Linux (kernel
2.2.14-5).

The command that started the problem was from the script:

-- Re-arranges the columns in a table
--
-- Tony Reina
-- Created: 6 March 2001

-- The BEGIN and COMMIT statements ensure that either all statements are
done or none are done
BEGIN WORK;

-- ADD THE NEW COLUMN TO THE TABLE
ALTER TABLE center_out_analog_proc ADD COLUMN name text;

-- SELECT the columns from the table in whatever new format you wish.
Place into a temporary table.
SELECT subject, arm, target, rep, channel, name, cut_off_freq,
           quality, analog_data INTO temp_table FROM
center_out_analog_proc;

-- DROP THE OLD TABLE
DROP TABLE center_out_analog_proc;

-- MAKE THE NEW TABLE INTO THE OLD TABLE
ALTER TABLE temp_table RENAME TO center_out_analog_proc;

-- FILL THE NEW COLUMN WITH THE CORRECT DATA
UPDATE center_out_analog_proc SET name = (SELECT name FROM
center_out_analog AS a WHERE
       a.subject = center_out_analog_proc.subject AND a.arm =
center_out_analog_proc.arm AND
       a.target = center_out_analog_proc.target AND a.rep =
center_out_analog_proc.rep AND
       a.channel = center_out_analog_proc.channel);

-- VACUUM THE TABLE
VACUUM VERBOSE ANALYZE center_out_analog_proc;

COMMIT WORK;

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


When I ran this, I had an error in the UPDATE command (so the entire
transaction aborted). I assumed that becuase the transaction aborted
that nothing would have changed in the db. However, after this happened,
I corrected the UPDATE command but ran into this error when I re-ran the
script:

db01=# \i alter_table_format.sql
BEGIN
psql:alter_table_format.sql:14: NOTICE:  mdopen: couldn't open
center_out_analog_proc: No such file or directory
psql:alter_table_format.sql:14: NOTICE:  mdopen: couldn't open
center_out_analog_proc: No such file or directory
psql:alter_table_format.sql:14: NOTICE:  mdopen: couldn't open
center_out_analog_proc: No such file or directory
psql:alter_table_format.sql:14: NOTICE:  mdopen: couldn't open
center_out_analog_proc: No such file or directory
psql:alter_table_format.sql:14: ERROR:  cannot open relation
center_out_analog_proc
psql:alter_table_format.sql:17: NOTICE:  current transaction is aborted,
queries ignored until end of transaction block
*ABORT STATE*
psql:alter_table_format.sql:20: NOTICE:  current transaction is aborted,
queries ignored until end of transaction block
*ABORT STATE*
psql:alter_table_format.sql:26: NOTICE:  mdopen: couldn't open
center_out_analog_proc: No such file or directory
psql:alter_table_format.sql:26: NOTICE:  mdopen: couldn't open
center_out_analog_proc: No such file or directory
psql:alter_table_format.sql:26: NOTICE:  mdopen: couldn't open
center_out_analog_proc: No such file or directory
psql:alter_table_format.sql:26: NOTICE:  mdopen: couldn't open
center_out_analog_proc: No such file or directory
psql:alter_table_format.sql:26: NOTICE:  current transaction is aborted,
queries ignored until end of transaction block
*ABORT STATE*
psql:alter_table_format.sql:29: NOTICE:  current transaction is aborted,
queries ignored until end of transaction block
*ABORT STATE*
COMMIT

When I try to vacuum the table or the database I get:

NOTICE:  Pages 190: Changed 0, reaped 0, Empty 0, New 0; Tup 9280: Vac
0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 652, MaxLen 652; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.07s/0.14u sec.
NOTICE:  --Relation circles_analog_proc --
NOTICE:  Pages 187: Changed 0, reaped 0, Empty 0, New 0; Tup 9140: Vac
0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 652, MaxLen 652; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.08s/0.13u sec.
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
NOTICE:  RelationIdBuildRelation: smgropen(center_out_analog_proc): No
such file or directory
NOTICE:  --Relation center_out_analog_proc --
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
ERROR:  cannot open relation center_out_analog_proc
db01=# select distinct monkey from center_out_analog_proc;
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
ERROR:  cannot open relation center_out_analog_proc


Likewise, a select gives me:

db01=# select distinct arm from center_out_analog_proc;
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
NOTICE:  mdopen: couldn't open center_out_analog_proc: No such file or
directory
ERROR:  cannot open relation center_out_analog_proc



Could someone help? Apparently something has gotten corrupted.

Thanks.

-Tony





Responses

pgsql-hackers by date

Next:From: Alfred PerlsteinDate: 2001-03-06 19:49:45
Subject: Re: How to shoot yourself in the foot: kill -9 postmaster
Previous:From: Mike MascariDate: 2001-03-06 19:38:18
Subject: RE: mailing list messages

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