* Tom Lane ([EMAIL PROTECTED]) wrote:
> Stephen Frost <[EMAIL PROTECTED]> writes:
> > It seems like pg_restore really should be able to handle COPY errors
> > correctly by skipping to the end of the COPY data segment when the
> > initial COPY command comes back as an error.
>
> Send a patch ;-)
This is what I get for knowing how to copy & paste C code, eh? ;-)
Attached is a patch to pg_restore, against HEAD but I think it'd work
against 8.1 just fine, to better handle it when a COPY command fails
(for whatever reason) during a DB restore.
Attempting to restore from a dump with 2 table objects under 8.1:
------------------------
[EMAIL PROTECTED]:/data/sfrost/postgres> pg_restore -d tsf -Fc tiger_test.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 412; 16672 1135494071 SCHEMA
tiger_test postgres
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied
for database tsf
Command was:
CREATE SCHEMA tiger_test;
pg_restore: [archiver (db)] could not execute query: ERROR: schema
"tiger_test" does not exist
Command was: ALTER SCHEMA tiger_test OWNER TO postgres;
pg_restore: [archiver] Error from TOC entry 21177; 1259 1135494072 TABLE
bg01_d00 postgres
pg_restore: [archiver] could not set search_path to "tiger_test": ERROR:
schema "tiger_test" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR: type
"public.geometry" does not exist
Command was: CREATE TABLE bg01_d00 (
ogc_fid integer,
wkb_geometry public.geometry,
area numeric(20,5),
perimeter numeric...
pg_restore: [archiver (db)] could not execute query: ERROR: schema
"tiger_test" does not exist
Command was: ALTER TABLE tiger_test.bg01_d00 OWNER TO postgres;
pg_restore: [archiver (db)] Error from TOC entry 21178; 1259 1135497928 TABLE
bg02_d00 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: type
"public.geometry" does not exist
Command was: CREATE TABLE bg02_d00 (
ogc_fid integer,
wkb_geometry public.geometry,
area numeric(20,5),
perimeter numeric...
pg_restore: [archiver (db)] could not execute query: ERROR: schema
"tiger_test" does not exist
Command was: ALTER TABLE tiger_test.bg02_d00 OWNER TO postgres;
pg_restore: [archiver (db)] Error from TOC entry 21609; 0 1135494072 TABLE DATA
bg01_d00 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"bg01_d00" does not exist
Command was: COPY bg01_d00 (ogc_fid, wkb_geometry, area, perimeter,
bg01_d00_, bg01_d00_i, state, county, tract, blkgroup, name, lsad, ls...
pg_restore: [archiver (db)] Error from TOC entry 21610; 0 1135497928 TABLE DATA
bg02_d00 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or
near "1" at character 1
Command was: 1
0103000000010000007D000000F1283A3752FB55C0E38C825CB98041403BC3D4963AFB55C0D8D30E7F4D80414015376E313FFB55C07AE40F069E7F4140...
WARNING: errors ignored on restore: 9
------------------------
As you can see, it's treating the data (the 01030000.... bit) as a
command, which is most certainly not right, especially when it *knows*
that the COPY command failed.
Attempting to restore from a dump with 2 table objects with patch:
------------------------
[EMAIL PROTECTED]:/data/sfrost/postgres/testinstall> bin/pg_restore -d tsf -Fc
-h localhost ../tiger_test.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 412; 16672 1135494071 SCHEMA
tiger_test postgres
pg_restore: [archiver (db)] could not execute query: ERROR: role "postgres"
does not exist
Command was: ALTER SCHEMA tiger_test OWNER TO postgres;
pg_restore: [archiver (db)] Error from TOC entry 21177; 1259 1135494072 TABLE
bg01_d00 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: type
"public.geometry" does not exist
Command was: CREATE TABLE bg01_d00 (
ogc_fid integer,
wkb_geometry public.geometry,
area numeric(20,5),
perimeter numeric...
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"tiger_test.bg01_d00" does not exist
Command was: ALTER TABLE tiger_test.bg01_d00 OWNER TO postgres;
pg_restore: [archiver (db)] Error from TOC entry 21178; 1259 1135497928 TABLE
bg02_d00 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: type
"public.geometry" does not exist
Command was: CREATE TABLE bg02_d00 (
ogc_fid integer,
wkb_geometry public.geometry,
area numeric(20,5),
perimeter numeric...
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"tiger_test.bg02_d00" does not exist
Command was: ALTER TABLE tiger_test.bg02_d00 OWNER TO postgres;
pg_restore: [archiver (db)] Error from TOC entry 21609; 0 1135494072 TABLE DATA
bg01_d00 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"bg01_d00" does not exist
Command was: COPY bg01_d00 (ogc_fid, wkb_geometry, area, perimeter,
bg01_d00_, bg01_d00_i, state, county, tract, blkgroup, name, lsad, ls...
pg_restore: [archiver (db)] Error from TOC entry 21610; 0 1135497928 TABLE DATA
bg02_d00 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"bg02_d00" does not exist
Command was:
COPY bg02_d00 (ogc_fid, wkb_geometry, area, perimeter, bg02_d00_, bg02_d00_i,
state, county, tract, blkgroup, name, lsad, ...
WARNING: errors ignored on restore: 7
------------------------
Here it correctly handles that the COPY command failed and just skips
past the data portion of the COPY. This lets it see the second object
properly (which we expect to fail) so that it can attempt to load it.
For a small case like this it's meaningless (this was just my test
case), for very large databases, being able to make it past errors
like these is essential...
Thanks!
Stephen
Index: src/bin/pg_dump/pg_backup_db.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_db.c,v
retrieving revision 1.66
diff -c -r1.66 pg_backup_db.c
*** src/bin/pg_dump/pg_backup_db.c 15 Oct 2005 02:49:38 -0000 1.66
--- src/bin/pg_dump/pg_backup_db.c 20 Jan 2006 20:05:33 -0000
***************
*** 306,311 ****
--- 306,315 ----
}
else
{
+ /* Catch that this is a failed copy command, and
+ * set pgCopyIn accordingly */
+ if (strncasecmp(qry->data,"COPY ",5) == 0) AH->pgCopyIn
= -1;
+
strncpy(errStmt, qry->data, DB_MAX_ERR_STMT);
if (errStmt[DB_MAX_ERR_STMT - 1] != '\0')
{
***************
*** 389,395 ****
*---------
*/
! if (PQputline(AH->connection, AH->pgCopyBuf->data) != 0)
die_horribly(AH, modulename, "error returned by PQputline\n");
resetPQExpBuffer(AH->pgCopyBuf);
--- 393,401 ----
*---------
*/
! /* If this is a failed copy command (pgCopyIn == -1) then just
! * fall through */
! if (AH->pgCopyIn == 1 && PQputline(AH->connection, AH->pgCopyBuf->data)
!= 0)
die_horribly(AH, modulename, "error returned by PQputline\n");
resetPQExpBuffer(AH->pgCopyBuf);
***************
*** 400,406 ****
if (isEnd)
{
! if (PQendcopy(AH->connection) != 0)
die_horribly(AH, modulename, "error returned by
PQendcopy\n");
AH->pgCopyIn = 0;
--- 406,414 ----
if (isEnd)
{
! /* If this is a failed copy command (pgCopyIn == -1) then just
! * fall through */
! if (AH->pgCopyIn == 1 && PQendcopy(AH->connection) != 0)
die_horribly(AH, modulename, "error returned by
PQendcopy\n");
AH->pgCopyIn = 0;
***************
*** 615,621 ****
/* Could switch between command and COPY IN mode at each line */
while (qry < eos)
{
! if (AH->pgCopyIn)
qry = _sendCopyLine(AH, qry, eos);
else
qry = _sendSQLLine(AH, qry, eos);
--- 623,633 ----
/* Could switch between command and COPY IN mode at each line */
while (qry < eos)
{
! /* If this is a working COPY *or* a failed COPY, call
! * _sendCopyLine to handle the incoming data from the COPY
! * command, it will just circular-file the data if we're
! * running a failed COPY. */
! if (AH->pgCopyIn == 1 || AH->pgCopyIn == -1)
qry = _sendCopyLine(AH, qry, eos);
else
qry = _sendSQLLine(AH, qry, eos);
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
