Re: RESTORE Error

Thanks for the reply, Tom. Yes, I had copied some data from a VIEW into a
TABLE (named temp_measurement), then deleted the VIEW and renamed the
TABLE, but must've created my script before performing the rename...

And, yes, it was a Data-only restore.

So, after recreating my scripts and creating my database and loading the
data, it's FINALLY working (yeah)!! =)

And, just for the record we're running version 8.0.8 of Postgres..
C:\Program Files\PostgreSQL\8.0\bin>psql --version
psql (PostgreSQL) 8.0.8

Thanks for everyone's help throughtout the week working through the issues
that I've had; you guys ROCK and the help is greatly appreciated!
-Jeanna

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: Thursday, January 11, 2007 9:28 PM
To: Jeanna Geier
Cc: pgsql-general
Subject: Re: [GENERAL] RESTORE Error

"Jeanna Geier" <jgeier(at)apt-cafm(dot)com> writes:
> And log file shows the following (what I posted before was through
pgAdmin):
> 2007-01-11 15:38:47 ERROR: relation "temp_measurement" does not exist
> 2007-01-11 15:38:47 ERROR: syntax error at or near "7094982" at character
1
> 2007-01-11 15:38:47 ERROR: syntax error at or near "67459" at character 1

OK, that makes it come a bit into focus. I think what pg_restore tried
to send was

COPY temp_measurement FROM STDIN;
7094982 ... rest of row 1 ...
67459 ... rest of row 2 ...

and the COPY command failed because of the stated reason, and then the
backend found itself chewing on a lot of data that was being sent as SQL
commands (because back then pg_restore depended on feedback from the
backend to tell whether an archive entry was COPY data or SQL commands).

So the bottom line here is you're trying to restore into a table that's
not there. How come? I suppose you were trying a data-only restore,
else this is a pg_dump or pg_restore bug.

Another point is that AFAICT 8.0 pg_restore does know enough to stop
if the COPY command fails --- least the case seems to work for me.
[ digs in CVS logs... ] Oh, it seems we fixed that in 8.0.7:

2006-02-05 15:59 tgl

* src/bin/pg_dump/: pg_backup_archiver.c, pg_backup_archiver.h,
pg_backup_db.c (REL8_0_STABLE): Fix pg_restore to properly discard
COPY data when trying to continue after an error in a COPY
statement. Formerly it thought the COPY data was SQL commands, and
got quite confused.

Stephen Frost

So I suppose you're running some earlier 8.0.x subrelease, in which case
an update would be an extremely good idea.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings