Thread: PQendcopy:resetting connection Problem and Cannot insert a duplicate key into unique index

Hi,

I managed to restore my database. However, one table is not restored.
The error message that I was received was "copy: line 3057, Cannot insert a duplicate key into unique index
users_pkey..."and then "...PQendcopy:resetting connection" 

Then I went to the log file (my debug file was set to level 5),
ProcessUtility: COPY "users"  FROM stdin;
ERROR:  copy: line 3057, Cannot insert a duplicate key into unique index users_pkey"

My question is that line #3057.  Do I count it from the line where it did "COPY "users" FROM stdin" as the first line
andcount up to 3057 lines (that line is the problem)? 


Any help is appreciated.
My pgverions is postgresql-7.1.3-2.

Thanks
Mary

------------------------------------------------
Mary Y Wang

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> I managed to restore my database. However, one table is not restored.
> The error message that I was received was "copy: line 3057, Cannot insert a duplicate key into unique index
users_pkey..."and then "...PQendcopy:resetting connection" 

> Then I went to the log file (my debug file was set to level 5),
> ProcessUtility: COPY "users"  FROM stdin;
> ERROR:  copy: line 3057, Cannot insert a duplicate key into unique index users_pkey"

> My question is that line #3057.  Do I count it from the line where it did "COPY "users" FROM stdin" as the first line
andcount up to 3057 lines (that line is the problem)? 

IIRC, even as far back as 7.1, that should be read as "the 3057'th row
of COPY data for this table".  So you can count forward 3057 lines from
the COPY command in the dump file ... unless you have any embedded
newlines in your data, in which case it could get a bit painful to
count correctly.

            regards, tom lane

Thanks Tom.
I still couldn't find that particular line that caused that problem :-(.  Counting was very pain.
Is there anyway that I can tell psql just to "ignore" (I mean don't insert it duplicate key into unique index
users_pkey)and just keep going without doing the PQendcopy:resetting connection? 

I've a dev server that has some portion of the database (I believe the duplicate problem also occurred in that
database),and that user_table is querable with data.  Would I be able to find any more detailed information of which
exactstatement caused that problem? 

Mary




-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, February 03, 2010 7:43 AM
To: Wang, Mary Y
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PQendcopy:resetting connection Problem and Cannot insert a duplicate key into unique index

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> I managed to restore my database. However, one table is not restored.
> The error message that I was received was "copy: line 3057, Cannot insert a duplicate key into unique index
users_pkey..."and then "...PQendcopy:resetting connection" 

> Then I went to the log file (my debug file was set to level 5),
> ProcessUtility: COPY "users"  FROM stdin;
> ERROR:  copy: line 3057, Cannot insert a duplicate key into unique index users_pkey"

> My question is that line #3057.  Do I count it from the line where it did "COPY "users" FROM stdin" as the first line
andcount up to 3057 lines (that line is the problem)? 

IIRC, even as far back as 7.1, that should be read as "the 3057'th row of COPY data for this table".  So you can count
forward3057 lines from the COPY command in the dump file ... unless you have any embedded newlines in your data, in
whichcase it could get a bit painful to count correctly. 

            regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Feb 3, 2010 at 2:16 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
> Thanks Tom.
> I still couldn't find that particular line that caused that problem :-(.  Counting was very pain.
> Is there anyway that I can tell psql just to "ignore" (I mean don't insert it duplicate key into unique index
users_pkey)and just keep going without doing the PQendcopy:resetting connection? 

Not really directly.  What I'd do is remove the unique constraint,
insert, then use something like

select max(row_id) from table t1 join table t2 on
t1.somefield=t2.somefield and t1.row_id<>r2.row_id;

to find dupes and remove them.

Then I'd dump the whole db and migrate to a more modern version of pgsql.

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Wed, Feb 3, 2010 at 2:16 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
>> I still couldn't find that particular line that caused that problem :-(. �Counting was very pain.
>> Is there anyway that I can tell psql just to "ignore" (I mean don't insert it duplicate key into unique index
users_pkey)and just keep going without doing the PQendcopy:resetting connection? 

> Not really directly.  What I'd do is remove the unique constraint,
> insert, then use something like

> select max(row_id) from table t1 join table t2 on
> t1.somefield=t2.somefield and t1.row_id<>r2.row_id;

> to find dupes and remove them.

> Then I'd dump the whole db and migrate to a more modern version of pgsql.

If you were using a more modern version of pgsql, it would tell you what
the duplicated key was ;-).  So maybe you could try loading the dump
file into something newer as a means of debugging the problem.

            regards, tom lane

Thanks Scott and Tom.
Yes.  I know, I know that I need to upgrade :-)
What would be the newer version of pgsql (I mean a bit higher version of my current version) that provides that
capabilityof telling me what the duplicated key was ? 

Migration is always a challenge, especially I'm not sure if I will have any unexpected hiccups when I dump my whole DB.
Ialso need to upgrade the OS.  My first priority is to get this problem fixed so that my users will stop IM, email or
callme.  

Mary



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, February 03, 2010 2:21 PM
To: Scott Marlowe
Cc: Wang, Mary Y; pgsql-general@postgresql.org
Subject: Re: [GENERAL] PQendcopy:resetting connection Problem and Cannot insert a duplicate key into unique index

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Wed, Feb 3, 2010 at 2:16 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
>> I still couldn't find that particular line that caused that problem :-(.  Counting was very pain.
>> Is there anyway that I can tell psql just to "ignore" (I mean don't insert it duplicate key into unique index
users_pkey)and just keep going without doing the PQendcopy:resetting connection? 

> Not really directly.  What I'd do is remove the unique constraint,
> insert, then use something like

> select max(row_id) from table t1 join table t2 on
> t1.somefield=t2.somefield and t1.row_id<>r2.row_id;

> to find dupes and remove them.

> Then I'd dump the whole db and migrate to a more modern version of pgsql.

If you were using a more modern version of pgsql, it would tell you what the duplicated key was ;-).  So maybe you
couldtry loading the dump file into something newer as a means of debugging the problem. 

            regards, tom lane

On Wed, Feb 3, 2010 at 3:31 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
> Thanks Scott and Tom.
> Yes.  I know, I know that I need to upgrade :-)
> What would be the newer version of pgsql (I mean a bit higher version of my current version) that provides that
capabilityof telling me what the duplicated key was ? 

I'd recommend going to 8.3.x if possible, or 8.4.2, the latest version.

Not sure how far back it would tell you which value caused the
failure, but more importantly, 8.3 is will run circles around 7.1 and
is much easier to keep happy from a maintenance perspective.

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> What would be the newer version of pgsql (I mean a bit higher version of my current version) that provides that
capabilityof telling me what the duplicated key was ? 

Hmm ... [ experiments... ]  For the specific case of COPY into a table
with a pre-existing unique index, it looks like only CVS HEAD will give
you an error message with the exact index value.  However, everything
back to 7.4 will include the text of the whole current line of COPY
data, which probably would be enough to figure out the problem.

            regards, tom lane