Thread: COPY error: pqReadData() -- backend closed the channel unexpectedly
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
Lee Joramo <lee.list@joramo.com> writes:
> [PostgreSQL 6.5.2 on powerpc-unknown-linux-gnu, compiled by gcc 2.95.2]
Hm. Did you compile at -O0? Pre-7.1 PG is known to have a lot of
problems on PPC if compiled with any optimization at all.
> 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>):
Are there any lines with more than 2700 characters worth of ad copy?
Pre-7.1 PG has a limit of 1/3 page or about 2700 bytes for any indexed
column ... and 6.5 tends to fall over rather than give an error if you
exceed the limit :-(
This particular line is well below that, but you could still see the
problem appear or disappear depending on which entries happen to fall
on the same disk page, so subtracting a line that isn't directly causing
the problem might be enough to mask the bug.
If that's not it, I'm not sure ... but I'd still recommend updating to
7.0.3 just on general principles.
> 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.
On many Linuxes, processes started from boot scripts are by default
started with "ulimit -c 0", which prevents creation of core files.
You may need to say "ulimit -c unlimited" in the postmaster startup
script to allow creation of corefiles.
regards, tom lane
Thanks for the helpful feedback. >> [PostgreSQL 6.5.2 on powerpc-unknown-linux-gnu, compiled by gcc 2.95.2] > >Hm. Did you compile at -O0? Pre-7.1 PG is known to have a lot of >problems on PPC if compiled with any optimization at all. I don't remember. I installed it nine months ago. I normally keep notes of what I do, but I don't seem to have recorded what I did while installing postgre. Most likely I do not have specfic notes for the postgre installation because because it was part of the initial LinuxPCC 2000 installation from CD. >> 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>): > >Are there any lines with more than 2700 characters worth of ad copy? >Pre-7.1 PG has a limit of 1/3 page or about 2700 bytes for any indexed >column ... and 6.5 tends to fall over rather than give an error if you >exceed the limit :-( The longest line in the 'classifieds.dat' file is 1152 characters and the corrosponding adcopy field is 1140 characters. So I should be well under this problem. (That being said, I am glad to hear about this problem so that I can catch it before it occurs.) >This particular line is well below that, but you could still see the >problem appear or disappear depending on which entries happen to fall >on the same disk page, so subtracting a line that isn't directly causing >the problem might be enough to mask the bug. Except, that the file loads just fine without the offending line, _AND_ when the the offending line is loaded by itself it causes the 'backend closed' error. I beleive, that the way that I isolated the bad line also precludes your idea. I found the bad line by repeatedly cutting the 'classifieds.dat' file in halfs, until I isolated the bad line. >If that's not it, I'm not sure ... but I'd still recommend updating to >7.0.3 just on general principles. I sure agree with that. Unfortunately, our public web server which is a Cobalt RaQ3 that runs 6.5.2 as well. I am not going to attempt an upgrade of the Cobalt, since much of the systems admin interface is driven by postgre. (Plus this machine is hosted by a far away company.) I am beginning to work up plans to change this situation, but until then I am stuck with 6.5.2 >On many Linuxes, processes started from boot scripts are by default >started with "ulimit -c 0", which prevents creation of core files. >You may need to say "ulimit -c unlimited" in the postmaster startup >script to allow creation of corefiles. Thanks, I give that a try. -- Lee A. Joramo ljoramo@nickads.com The Nickel Want Ads www.nickads.com Internet Manager 970-242-5555
Lee Joramo <lee.list@joramo.com> writes:
> Most likely I do not have specfic notes for the
> postgre installation because because it was part of the initial LinuxPCC
> 2000 installation from CD.
If you can confirm that the server you are running is from the LinuxPPC
2000 CD, then I will try to duplicate the problem here ... I have that
same Linux distro running on a laptop, but never bothered to fire up
its PgSQL, since it's so out of date :-(.
I will need the full declaration of the table (most reliable way to show
this is "pg_dump -s -t tablename databasename").
regards, tom lane