Thread: COPY error: pqReadData() -- backend closed the channel unexpectedly

COPY error: pqReadData() -- backend closed the channel unexpectedly

From
Lee Joramo
Date:
I am running to the following error when copying a file to a table:
    "backend closed the channel unexpectedly"
____________

My basic system info:
[PostgreSQL 6.5.2 on powerpc-unknown-linux-gnu, compiled by gcc 2.95.2]
____________

The Table:
able    = classifieds
+----------------------------------+-
---------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+-
---------------------------------+-------+
| category_number                  | int4                             |
   4 |
| bullet                           | char()                           |
   1 |
| border                           | bool                             |
   1 |
| image                            | varchar()                        |
  32 |
| rmc                              | bool                             |
   1 |
| adcopy                           | text                             |
 var |
+----------------------------------+-
---------------------------------+-------+
Indices:  classifieds_adcopy
          classifieds_bullet
          classifieds_category_number
          classifieds_image
____________

A copy of the psql command and error message:

rmcars2=> DELETE FROM classifieds;
rmcars2=> COPY classifieds FROM '/path/classifieds.dat';
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible.  Terminating.
____________

The 'classifieds.dat' consists of about 2200 lines. I have determined
that the problem is caused by just the following line (literal tabs have
been replaced with <TAB>):

825<TAB><TAB>f<TAB><TAB>f<TAB>Need more growing room ? Cozy up by one of
2 fireplaces, and stay warm this winter! This beautifully maintained
rancher, in paradise hills, offers 3 bedrooms, 2 baths, dining and family
rooms, and a large eat-in kitchen loaded with cupboards. 1844 square feet
of comfortable living on a large corner lot make this the perfect home
for the growing family. All these amenities and more at an affordable
price of only $144,900. Listing#00-5968 Call Shirley McGuiness 255-3810
or 254-8074 Keller Williams Realty
____________

Additional information:

The 'classifieds.dat' file is generated from our classified ad management
system. I am pulling this data into postgre for publication on the web.
The 'classifieds.dat' files generate from two previous weeks work just
fine. (And still do!)

If I remove the offending line form the file, the COPY command works just
fine.

After I isolated the line causing the problems, I assumed that I would
quickly find the source of the problem, but I have not. Here is what I
have done to isolated the problem:
* I inspected the line for wayward quotes or escape characters.
  none found
* I have tried truncating the line until it work.
  Results were strangely inconsistent
* I substituted letters for punctuation.
  no effect
* substituted letters and numbers with the letter 'a'
  It worked!
* substituted spaces with '_'
  It worked!!!!! (I don't understand this!)

The above where each performed on a copy of the original bad line.

I have searched the mailing list archive, I did find number of messages
regarding the same error message, but none that seem to apply to my
situation. I also learned in the archive that when a 'backend closed'
error occurs I should be able to find a 'core' file located in the
database's directory. But I have not found a core file.

thanks
--
Lee A. Joramo                      ljoramo@nickads.com
The Nickel Want Ads                www.nickads.com
Internet Manager                   970-242-5555