rebuilding a table from a datafile

On Wed, Aug 16, 2000 at 02:36:10PM +0000, brianb-pggeneral@edsamail.com wrote:

This is with regards to Postgres 6.5.

While trying to ALTER RENAME a large table (203MB data file), an error
occured. \d displays the new table name, but there is no corresponding
file. a file with the original table name still exists, so presumably the
data is not lost. Is it possible for me to undo the RENAME by massaging the
pg_* tables, or otherwise retrieve the data in the table? It was an
insert-only table, and no deletes or updates were ever performed on it.

Hmm, what kind of failure mode caused this? Reading the renamrel code for
6.5.3, it does the file rename first, then updates pg_class. Anyway,
I'd try manually renaming the file to the name that appears in pg_class,
rather than trying to fixup the system relations. There're indices on
pg_class that're hard to rebuild under 6.5.X.

If that doesn't work, try updating pg_class.relname to mach the filename.
you'll need to be a DB superuser to do that.

Oh, make a backup of the table file first, just in case.

In any case, once you can read the table, do a dump/restore cycle. And
think about upgrading to 7.0.X. As someone else said, it's good, and
stay's crunchy in milk! (Seriously, lots of bug fixes)

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Ross J. Reedstrom writes:

On Wed, Aug 16, 2000 at 02:36:10PM +0000, brianb-pggeneral@edsamail.com wrote:

This is with regards to Postgres 6.5.

While trying to ALTER RENAME a large table (203MB data file), an error
occured. \d displays the new table name, but there is no corresponding
file. a file with the original table name still exists, so presumably the
data is not lost. Is it possible for me to undo the RENAME by massaging the
pg_* tables, or otherwise retrieve the data in the table? It was an
insert-only table, and no deletes or updates were ever performed on it.

Hmm, what kind of failure mode caused this? Reading the renamrel code for
6.5.3, it does the file rename first, then updates pg_class. Anyway,
I'd try manually renaming the file to the name that appears in pg_class,
rather than trying to fixup the system relations. There're indices on
pg_class that're hard to rebuild under 6.5.X.

If that doesn't work, try updating pg_class.relname to mach the filename.
you'll need to be a DB superuser to do that.

I am getting a "ERROR: cannot find attribute 1 of relation foo" when I try
to SELECT * from the table. Also, \d foo produces "Couldn't find table
foo!", but it is listed in pg_class.

Is it safe/advisable to modify pg_attribute directly in this case? I have
several other tables with the exact same structure that I can use as
reference.

Brian
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/ AIM: bbaquiran
Work: +63(2)7182222 Home: +63(2) 9227123

I'm smarter than average. Therefore, average, to me, seems kind of stupid.
People weren't purposely being stupid. It just came naturally.
-- Bruce "Tog" Toganazzini

brianb-pggeneral@edsamail.com writes:

This is with regards to Postgres 6.5.
While trying to ALTER RENAME a large table (203MB data file), an error
occured.

Table RENAME is pretty risky under 6.5 :-(. 7.0 is a little better,
but RENAME will never be trustworthy until we abandon the linkage
between file names and table names.

\d displays the new table name, but there is no corresponding
file. a file with the original table name still exists, so presumably the
data is not lost. Is it possible for me to undo the RENAME by massaging the
pg_* tables, or otherwise retrieve the data in the table? It was an
insert-only table, and no deletes or updates were ever performed on
it.

You could probably get away with

CREATE TABLE someothername (same parameters)

and then rm the (presumably 0-size) file 'someothername' and copy the
original-table-named file to that file name. This will not fill the
indexes, if any, on the new table, so drop and recreate them afterwards.

After that, update to 7.0.2 ;-)

regards, tom lane

Tom Lane writes:

brianb-pggeneral@edsamail.com writes:

This is with regards to Postgres 6.5.
While trying to ALTER RENAME a large table (203MB data file), an error
occured.

Table RENAME is pretty risky under 6.5 :-(. 7.0 is a little better,
but RENAME will never be trustworthy until we abandon the linkage
between file names and table names.

This is the tablespaces feature, right? Is there any news on when Postgres
will support this?

You could probably get away with

CREATE TABLE someothername (same parameters)

and then rm the (presumably 0-size) file 'someothername' and copy the
original-table-named file to that file name. This will not fill the
indexes, if any, on the new table, so drop and recreate them afterwards.

Yep, I got away with it alright. I can see my data now.

After that, update to 7.0.2 ;-)

Will do! Thanks Tom!

Brian
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/ AIM: bbaquiran
Work: +63(2)7182222 Home: +63(2) 9227123

I'm smarter than average. Therefore, average, to me, seems kind of stupid.
People weren't purposely being stupid. It just came naturally.
-- Bruce "Tog" Toganazzini