Обсуждение: postgres 8.2 "end-of-copy marker corrupt"

Hello -

I'm trying to copy a file to a table in Postgres 8.2 and keep receiving the following message

  ERROR: end-of-copy marker corrupt
  CONTEXT:  COPY domain_dom, line 3147756: ""

The psql statement is
   \copy domain_dom from 'Z:/Syntryx/ClassDomainProject/1170886621.txt'

I've checked the end of the file using a hex editor, and can't find anything wrong with it.  I've also counted the number of values in the last record, and can't find anything wrong there.

Searching the net has not revealed any information on this.

Any suggestions/help would be very much appreciated.

Thank you.

david


 

"David Monarchi" <david.e.monarchi@gmail.com> writes:
> I'm trying to copy a file to a table in Postgres 8.2 and keep receiving the
> following message

>   ERROR: end-of-copy marker corrupt
>   CONTEXT:  COPY domain_dom, line 3147756: ""

Have you looked at the input file near that line number?  My guess is
misquoting of a \. sequence, or possibly (since you seem to be on
Windows) a LF-vs-CR/LF end-of-line format issue.  It need not be
close to the actual end of that file...

            regards, tom lane

Thanks for the suggestions, Tom.  The file is about 3G from a FreeBSD box, but I'm doing some work on it on an XP box.  I have a number of other files that I created in exactly the same way from a PG 8.2 db on the BSD box.  They all load fine. 

Each line is terminated with a LF (0A), including the last line.  This is the structure that of all of the other files that I can load have.

Some of the text fields in the file had \ in them, so I simply replaced all of them with blanks to see if somewhere there was an escape sequence that was causing the problem.

At this point, based on what you said, the only thing I can think of is an errant quote. 

I'm tempted to remove all single and double quotation marks, but that will cause some other problems.  Can you think of anything to do other than write a program to load the data one record at a time to see where it breaks?  (There are 20M records in the file.  )

Thanks again,
David

"David Monarchi" <david.e.monarchi@gmail.com> writes:
> Each line is terminated with a LF (0A), including the last line.  This is
> the structure that of all of the other files that I can load have.
> Some of the text fields in the file had \ in them, so I simply replaced all
> of them with blanks to see if somewhere there was an escape sequence that
> was causing the problem.

Hm ... AFAICS from looking at the code, it's impossible to get that
error message unless the file contains \ followed by . followed by
something that's not either CR or LF.  Are you sure you changed all
the \ characters?

Are you using CSV mode or traditional COPY format?  What's the
setting of client_encoding?

            regards, tom lane

On Wed, Mar 28, 2007 at 10:15:35AM -0400, Tom Lane wrote:
> "David Monarchi" <david.e.monarchi@gmail.com> writes:
> > Each line is terminated with a LF (0A), including the last line.  This is
> > the structure that of all of the other files that I can load have.
> > Some of the text fields in the file had \ in them, so I simply replaced all
> > of them with blanks to see if somewhere there was an escape sequence that
> > was causing the problem.
>
> Hm ... AFAICS from looking at the code, it's impossible to get that
> error message unless the file contains \ followed by . followed by
> something that's not either CR or LF.

What is the output of:

grep -c "\\\."

and if that is a "small" number, the output of:

grep -n "\\\."

and

grep "\\\." | hexdump -C

Hi Tom -

Thanks for the suggestions.  You were correct, and it was a combination of problems.  The field delimiter was a tab, and there were text fields that ended with a \, so of course that caused problems.  There were also single quotes in the text fields, but they didn't occur in pairs.  Rather they were possessive case nouns.  And finally there were double quotes.  And, oddly enough, they didn't always occur in pairs.  In fact, sometimes the double quote was the only character in the text field.  The double quotes didn't turn out to be a problem.

I did the following.
1) replaced all \tab combinations with blankTab.
2) replaced all 's with two single quotes, '', because \' doesn't work in a copy statement.

Following this, everything loaded just fine.

Thank you again for you help, and for pointing me in the right direction.

cheers,
david