Thread: ERROR: cannot open relation center_out_analog_proc

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

"G. Anthony Reina" <reina@nsi.edu> writes:
> BEGIN WORK;
> ...
> DROP TABLE center_out_analog_proc;
> ...
> [fail transaction]

> psql:alter_table_format.sql:14: NOTICE:  mdopen: couldn't open
> center_out_analog_proc: No such file or directory

You can't roll back a DROP TABLE under pre-7.1 releases (and 7.0 has
a big fat warning notice to tell you so!).  The physical table file
is deleted immediately by the DROP, so rolling back the system catalog
changes doesn't get you back to a working table.

The only way to clean up at this point is to drop the table for real.
        regards, tom lane

Tom Lane wrote:

> You can't roll back a DROP TABLE under pre-7.1 releases (and 7.0 has
> a big fat warning notice to tell you so!).  The physical table file
> is deleted immediately by the DROP, so rolling back the system catalog
> changes doesn't get you back to a working table.
>
> The only way to clean up at this point is to drop the table for real.
>

Okay, so then you are saying that even though the DROP TABLE and ALTER
TABLE RENAME went through correctly, the line after that bombed out,
tried to rollback the transaction, and gave me the error?

I definitely missed that warning. Are there any big warnings for things
that don't work so well within a transaction (BEGIN WORK; COMMIT WORK)?

Thanks. Off to rebuild a table.
-Tony

"G. Anthony Reina" <reina@nsi.edu> writes:
> Tom Lane wrote:
>> You can't roll back a DROP TABLE under pre-7.1 releases (and 7.0 has
>> a big fat warning notice to tell you so!).  The physical table file
>> is deleted immediately by the DROP, so rolling back the system catalog
>> changes doesn't get you back to a working table.

> Okay, so then you are saying that even though the DROP TABLE and ALTER
> TABLE RENAME went through correctly, the line after that bombed out,
> tried to rollback the transaction, and gave me the error?

Right.  The system catalogs roll back just fine, but the Unix filesystem
doesn't know from rollbacks.

> I definitely missed that warning. Are there any big warnings for things
> that don't work so well within a transaction (BEGIN WORK; COMMIT WORK)?

ALTER TABLE RENAME is another one...

This is all fixed in 7.1 btw.
        regards, tom lane