Thread: Re: COPY error: pqReadData() -- backend closed the channel unexpectedly
Thanks for the feed back. >Just a guess, but is there an embedded tab in the last field that you are >mistaking for a space? I've done that before and pulled my hair out trying >to figure out the issue. Nope. When I quoted the bad line in my orignal message, I replaced literal tabs with <TAB> with search and replace in my text editor. The tabs are just where they should be. (The bad line is quoted at the end of this message.) >Any other info in the log file that might give you a clue? Typically I find >some entry about a failed atoi (ascii to int) or something that can get me >pointed in the right direction. I have looked for a proper log file, and can find own. On inspecting the startup script it looks like postmaster is being involved with the following command: su -l postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql' For a log file, shouldn't it be something like: su -l postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql > path/logfile' >I have felt your pain before. It's something simple that you will kick >yourself over. Best of luck. I have felt this pain before too. I hope that I end up kicking myself and not cursing postgre ;-) -- Lee A. Joramo ljoramo@nickads.com The Nickel Want Ads www.nickads.com Internet Manager 970-242-5555 >> 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
Lee Joramo <lee.list@joramo.com> writes:
> I have looked for a proper log file, and can find own. On inspecting the
> startup script it looks like postmaster is being involved with the
> following command:
>
> su -l postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql'
-S suppresses all log output, so first you need to get rid of that.
A more useful startup script would go like
su -l postgres -c \
'/usr/bin/postmaster -i -D/var/lib/pgsql >/path/to/logfile 2>&1 &'
Note the trailing & to get it to run in background --- you need that
when you don't use -S.
regards, tom lane
And the solution is:
DELETE INDEX classifieds_adcopy
By deleteing the index, everything started to work correctly. Even after
recreating the index, everyting worked after multiple tests.
I came to this conclusion after much examination of the 'bad line' that
was causing my problems. I created a simple Python script to loop over
the 'adcopy' field of the bad line. For every loop it would add another
character of the adcopy, save it to a file and then issue a "COPY
classifieds FROM 'file'" to import the single line. For example the
following 8 lines represent the first 8 files that were generated:
825<TAB><TAB>f<TAB><TAB>f<TAB>N
825<TAB><TAB>f<TAB><TAB>f<TAB>Ne
825<TAB><TAB>f<TAB><TAB>f<TAB>Nee
825<TAB><TAB>f<TAB><TAB>f<TAB>Need
825<TAB><TAB>f<TAB><TAB>f<TAB>Need
825<TAB><TAB>f<TAB><TAB>f<TAB>Need m
825<TAB><TAB>f<TAB><TAB>f<TAB>Need mo
825<TAB><TAB>f<TAB><TAB>f<TAB>Need more
I also preformed this test with many other lines of data that I believed
to be good. The results of these tests confirmed to me that only the one
bad line was responsible. The good lines always loaded, the bad line
always failed. However as I repeatedly preformed this test on 'bad line',
it failed in an inconsistent way:
Failure 1:
825<TAB><TAB>f<TAB><TAB>f<TAB>Need more growin
Failure 2:
825<TAB><TAB>f<TAB><TAB>f<TAB>Nee
Failure 3:
825<TAB><TAB>f<TAB><TAB>f<TAB>Need more growing room ? Cozy up by one of
And so on ...
At this point, I thought that maybe the index was corrupted.
But this raises other questions. Should I be concerned about other
corruption to the database? What can I do to prevent this from happening
again. Should I delete the indexes before I preform a 'COPY table FROM'
operation, and then recreate the indexes?
Thanks very much for your help.
--
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:
> And the solution is:
> DELETE INDEX classifieds_adcopy
> By deleteing the index, everything started to work correctly. Even after
> recreating the index, everyting worked after multiple tests.
So that index had gotten corrupted at some time in the past, and just
emptying and refilling the table didn't fix it. Doesn't surprise me
a whole lot. (I had assumed you were destroying and rebuilding the
whole database, else I'd have suggested this sooner...)
It's possible that the index corruption was triggered by having inserted
an overlength adcopy value at some time in the past. Or maybe it's just
a garden-variety bug; 6.5 has its share of 'em. I do urge you to update
to 7.0.3 as soon as possible. (BTW, there are LinuxPPC RPMs of 7.0.3
available from our FTP server, so if it's having to compile from source
that's scaring you, you don't need to.)
regards, tom lane
Re: Re: COPY error: pqReadData() -- backend closed the channel unexpectedly
From
Tod McQuillin
Date:
On Tue, 9 Jan 2001, Tom Lane wrote:
> -S suppresses all log output, so first you need to get rid of that.
The postgres.1 man page from postgresql-7.0.3/doc/man.tar.gz says:
-S SortSize
Specifies the amount of memory to be used by inter-
nal sorts and hashes before resorting to temporary
disk files. The value is specified in kilobytes,
and defaults to 512 kilobytes. Note that for a com-
plex query, several sorts and/or hashes might be
running in parallel, and each one will be allowed
to use as much as SortSize kilobytes before it
starts to put data into temporary files.
Who is right?
--
Tod McQuillin
Tod McQuillin <devin@spamcop.net> writes:
> On Tue, 9 Jan 2001, Tom Lane wrote:
>> -S suppresses all log output, so first you need to get rid of that.
> The postgres.1 man page from postgresql-7.0.3/doc/man.tar.gz says:
> -S SortSize
Postmaster and backend switches are two different animals. To specify
a backend switch to the postmaster, you need -o. Thus:
postmaster -S -o "-S 1000"
selects silent postmaster mode and 1000K sortsize for the backend.
In short: postmaster.1 != postgres.1 ...
regards, tom lane
Re: Re: COPY error: pqReadData() -- backend closed the channel unexpectedly
From
Tod McQuillin
Date:
On Tue, 9 Jan 2001, Tom Lane wrote: > Postmaster and backend switches are two different animals. To specify > a backend switch to the postmaster, you need -o. Thus: > > postmaster -S -o "-S 1000" > > selects silent postmaster mode and 1000K sortsize for the backend. > > In short: postmaster.1 != postgres.1 ... OOPS! My mistake. No wonder I was confused. I was basing my incorrect assumptions on this: devin@kanemochi /tools/pgsql/bin% ls -l post* -r-xr-xr-x 1 devin wheel 1469829 Jan 4 03:21 postgres lrwxrwxr-x 1 devin wheel 8 Jan 4 03:21 postmaster -> postgres I was not aware the binary would behave differently depending on the name it was invoked as. Thanks for straightening me out. -- Tod McQuillin
Tod McQuillin <devin@spamcop.net> writes:
> I was not aware the binary would behave differently depending on the name
> it was invoked as.
Right. We used to have two separate binaries, and the postmaster would
actually exec() the backend (postgres) binary for every session launch.
About two years ago, Bruce made them into a single executable so that
session launch only needs fork() and not exec(). But he kept the old
switch-parsing mechanisms for backwards compatibiliy with existing
startup scripts. So our current switch syntax is a dreadfully ugly
legacy thingy :-(
Peter E. has done some good work towards providing a less ugly switch
notation for 7.1.
regards, tom lane
Re: Re: COPY error: pqReadData() -- backend closed the channel unexpectedly
From
Bruce Momjian
Date:
> Tod McQuillin <devin@spamcop.net> writes: > > I was not aware the binary would behave differently depending on the name > > it was invoked as. > > Right. We used to have two separate binaries, and the postmaster would > actually exec() the backend (postgres) binary for every session launch. > About two years ago, Bruce made them into a single executable so that > session launch only needs fork() and not exec(). But he kept the old > switch-parsing mechanisms for backwards compatibiliy with existing > startup scripts. So our current switch syntax is a dreadfully ugly > legacy thingy :-( > > Peter E. has done some good work towards providing a less ugly switch > notation for 7.1. Tom, can you be a little more specific on the changes? I am unsure of the details. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Peter E. has done some good work towards providing a less ugly switch
>> notation for 7.1.
> Tom, can you be a little more specific on the changes? I am unsure of
> the details.
Peter would be the right person to explain the details, not me.
regards, tom lane