Обсуждение: TOC errors

I am doing a restore:

pg_restore --no-owner -L /tmp/db.list --single-transaction --dbname=condor3_production $DBFILE

and I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2056; 0 35202 TABLE DATA adv_ptf_release_maps pedzan
pg_restore: [archiver (db)] COPY failed: ERROR:  duplicate key value violates unique constraint
"adv_ptf_release_maps_apar_defect_version_map_id_ptf_id_rele_key"
DETAIL:  Key (apar_defect_version_map_id, ptf_id, release_id)=(72022, 16678, 436) already exists.
CONTEXT:  COPY adv_ptf_release_maps, line 651290: "651290       72022   16678   436     2009-06-07 14:08:55.627762
2009-06-0714:08:55.627762" 

I've used the custom format.  The error tells me a line number but I can't look at it.  Is there a way that I can
convertthe custom format to something I can view? 

Also, I have turned off all the foreign key checking while I was doing the restore but I guess this is the unique
checkingthat is causing the error. Can I turn that off (without doing a table modify)? 

Thank you,
Perry

On Aug 2, 2013, at 12:29 PM, Perry Smith <pedzsan@gmail.com> wrote:

> I am doing a restore:
>
> pg_restore --no-owner -L /tmp/db.list --single-transaction --dbname=condor3_production $DBFILE
>
> and I get this error:
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2056; 0 35202 TABLE DATA adv_ptf_release_maps pedzan
> pg_restore: [archiver (db)] COPY failed: ERROR:  duplicate key value violates unique constraint
"adv_ptf_release_maps_apar_defect_version_map_id_ptf_id_rele_key"
> DETAIL:  Key (apar_defect_version_map_id, ptf_id, release_id)=(72022, 16678, 436) already exists.
> CONTEXT:  COPY adv_ptf_release_maps, line 651290: "651290       72022   16678   436     2009-06-07 14:08:55.627762
 2009-06-07 14:08:55.627762" 
>
> I've used the custom format.  The error tells me a line number but I can't look at it.  Is there a way that I can
convertthe custom format to something I can view? 
>
> Also, I have turned off all the foreign key checking while I was doing the restore but I guess this is the unique
checkingthat is causing the error. Can I turn that off (without doing a table modify)? 
>

The other confusing part about this is "its not true" :-)... The tuple shows up only once in the original database that
wasdumped.  So, its like it is getting a read error or something while doing the restore. 

psql --version
psql (PostgreSQL) 9.0.4
contains support for command-line editing

Thank you for any help and suggestions.
Perry

Perry Smith escribió:

> On Aug 2, 2013, at 12:29 PM, Perry Smith <pedzsan@gmail.com> wrote:

> > I've used the custom format.  The error tells me a line number but I
> > can't look at it.  Is there a way that I can convert the custom
> > format to something I can view?

Sure, pg_restore can save its output to a file instead of to a database.

> > Also, I have turned off all the foreign key checking while I was
> > doing the restore but I guess this is the unique checking that is
> > causing the error. Can I turn that off (without doing a table
> > modify)?

No.

> The other confusing part about this is "its not true" :-)... The tuple
> shows up only once in the original database that was dumped.  So, its
> like it is getting a read error or something while doing the restore.

Probably one of the copies is not visible to the index, which would
explain why the other one was allowed in in the first place.  To verify
this, do a select for that value, but first set enable_indexscan and
enable_bitmapscan to off.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Perry Smith <pedzsan@gmail.com> writes:

> I am doing a restore:
>
> pg_restore --no-owner -L /tmp/db.list --single-transaction --dbname=condor3_production $DBFILE
>
> and I get this error:
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2056; 0 35202 TABLE DATA adv_ptf_release_maps pedzan
> pg_restore: [archiver (db)] COPY failed: ERROR:  duplicate key value violates unique constraint
"adv_ptf_release_maps_apar_defect_version_map_id_ptf_id_rele_key"
> DETAIL:  Key (apar_defect_version_map_id, ptf_id, release_id)=(72022, 16678, 436) already exists.
> CONTEXT:  COPY adv_ptf_release_maps, line 651290: "651290       72022   16678   436     2009-06-07 14:08:55.627762
 2009-06-07 14:08:55.627762" 
>
> I've used the custom format.  The error tells me a line number but I can't look at it.  Is there a way that I can
convertthe custom format to something I can view? 
>
> Also, I have turned off all the foreign key checking while I was
> doing the restore but I guess this is the unique checking that is
> causing the error. Can I turn that off (without doing a table
> modify)?

I think you are restoring into a DB with non-empty tables.

Your target DB/tables needs either...

a. to be initially empty
b. to be populated with non-conflicting data
c. dump and/or  restore invoked using 'clean' options

Or even possibly you are restoring a data-only dump from a
non-constrained origin system into one with unique constraints.

HTH

>
> Thank you,
> Perry
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800