Обсуждение: Yikes! Bitten by line length?
Hi All. Happy Monday..;^) I've now got my app converted from using sybase to using PostgreSQL. Got the first successful compile today. However, that doesn't mean it works. I'm getting some strange core dumps, so I went through my logs, and it turns out that the core dumps are happening after this sql is attempted: select I_SESSION(null,'54',2,'E2K','0x577A977F7FCCBF5A567C6097A5A5A9D47DA39B82298C6E9F89DA4D4F67E39B856692DDAA8A938E637D7F6C70A679A67AA881D68926976C966CB5A0B48E793795472F216B4A652933406A2B5B4554660B5734765B3A623A5421A02D2B5E5B5916008B4E6012106A0A3258291D123C1F6B4A341EF82152673435512D243158561B1520582463622363702F2640DD9A6B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'); I know about the 8K limit for a row but this isn't even close. So, I went into psql to try to run this query by hand: GRE=# select I_SESSION(null,'54',2,'E2K','0x577A977F7FCCBF5A567C6097A5A5A9D47DA39B82298C6E9F89DA4D4F67E39B856692DDAA8A938E637D7F6C70A679A67AA881D68926976C966CB5A0B48E793795472F216B4A652933406A2B5B4554660B5734765B3A623A5421A02D2B5E5B5916008B4E6012106A0A3258291D123C1F6B4A341EF82152673435512D243158561B1520582463622363702F2640DD9A6B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'); GRE'# ' GRE-# ); ERROR: parser: parse error at or near "0000000000000000000000000000000000" GRE=# [Note that the final ' wasn't found, so I had to add it, and then close the parenthesis and execute the whole mess with the semi colon.] At any rate, this is not the error message I'd have expected. It seems that the input buffer is wrapping around the end, and so the whole string isn't being processed properly. Am I doing something silly? -Ken
Ken Corey <ken@kencorey.com> writes:
> GRE=# select
>
I_SESSION(null,'54',2,'E2K','0x577A977F7FCCBF5A567C6097A5A5A9D47DA39B82298C6E9F89DA4D4F67E39B856692DDAA8A938E637D7F6C70A679A67AA881D68926976C966CB5A0B48E793795472F216B4A652933406A2B5B4554660B5734765B3A623A5421A02D2B5E5B5916008B4E6012106A0A3258291D123C1F6B4A341EF82152673435512D243158561B1520582463622363702F2640DD9A6B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');
> GRE'# '
> GRE-# );
> ERROR: parser: parse error at or near
> "0000000000000000000000000000000000"
> GRE=#
> [Note that the final ' wasn't found, so I had to add it, and then close
> the parenthesis and execute the whole mess with the semi colon.]
That's pretty odd; you seem to have a broken psql. What PG version are
you running? Do you have libreadline (history support) in there, and if
so what libreadline version is it?
regards, tom lane
Tom Lane wrote: > Ken Corey <ken@kencorey.com> writes: > > GRE=# select > > I_SESSION(null,'54',2,'E2K','0x577A977F7FCCBF5A567C6097A5A5A9D47DA39B82298C6E9F89DA4D4F67E39B856692DDAA8A938E637D7F6C7000000000000000000000000000000000000000000000000000000000000000000'); > > GRE'# ' > > GRE-# ); > > ERROR: parser: parse error at or near > > "0000000000000000000000000000000000" > > GRE=# > > > [Note that the final ' wasn't found, so I had to add it, and then close > > the parenthesis and execute the whole mess with the semi colon.] > > That's pretty odd; you seem to have a broken psql. What PG version are > you running? Do you have libreadline (history support) in there, and if > so what libreadline version is it? I downloaded pg 7.0.3, untarred it, went into the directory, typed: configure make make install No libreadline (though I should get it...I'd like to have up-arrow to go back to previous lines in psql!) Any other ideas? I've downloaded 7.1b3 to try out. Since I won't have any real critical data in there until March timeframe, I can afford to play with the latest version. If that fixes it, I'll report back to this list. -Ken
As promised, here's the explanation of what's going on...(it's almost too embarrassingly stupid to print) The definition of I_SESSION is: create FUNCTION I_SESSION (varchar(255), numeric(9,0), int, varchar(255), varchar(512)) RETURNS INT4 AS ' ... LANGUAGE 'plpgsql'; Of course, the data I was trying to enter in the last arguement was roughly 550 bytes long...while the definition of the last variable was 512. In my code, I've changed it to 768, and gotten a completely different error. (Yes, I'm sure that this too is in my code...;^) The error message was less than helpful (cryptic under psql, and a core dump when called through libpq), but the bug was completely mine. This fails in the same way under both 7.0.3 and 7.1b3 (both attempted under Solaris 2.7, FWIW). -Ken Ken Corey wrote: > > Tom Lane wrote: > > Ken Corey <ken@kencorey.com> writes: > > > GRE=# select > > > I_SESSION(null,'54',2,'E2K','0x577A977F7FCCBF5A567C6097A5A5A9D47DA39B82298C6E9F89DA4D4F67E39B856692DDAA8A938E637D7F6C7000000000000000000000000000000000000000000000000000000000000000000'); > > > GRE'# ' > > > GRE-# ); > > > ERROR: parser: parse error at or near > > > "0000000000000000000000000000000000" > > > GRE=# > > > > > [Note that the final ' wasn't found, so I had to add it, and then close > > > the parenthesis and execute the whole mess with the semi colon.] > > > > That's pretty odd; you seem to have a broken psql. What PG version are > > you running? Do you have libreadline (history support) in there, and if > > so what libreadline version is it? > > I downloaded pg 7.0.3, untarred it, went into the directory, typed: > > configure > make > make install > > No libreadline (though I should get it...I'd like to have up-arrow to go > back to previous lines in psql!) > > Any other ideas? > > I've downloaded 7.1b3 to try out. Since I won't have any real critical > data in there until March timeframe, I can afford to play with the > latest version. If that fixes it, I'll report back to this list. > > -Ken
Drat! Thought this wa identified...but sure enough it's still causing troubles. Will report shortly. -Ken Ken Corey wrote: > As promised, here's the explanation of what's going on...(it's almost > too embarrassingly stupid to print) > > The definition of I_SESSION is: > > create FUNCTION I_SESSION (varchar(255), numeric(9,0), int, > varchar(255), varchar(512)) > RETURNS INT4 > AS ' > ... > LANGUAGE 'plpgsql'; > > Of course, the data I was trying to enter in the last arguement was > roughly 550 bytes long...while the definition of the last variable was > 512. In my code, I've changed it to 768, and gotten a completely > different error. (Yes, I'm sure that this too is in my code...;^) > > The error message was less than helpful (cryptic under psql, and a core > dump when called through libpq), but the bug was completely mine. This > fails in the same way under both 7.0.3 and 7.1b3 (both attempted under > Solaris 2.7, FWIW). > > -Ken > > Ken Corey wrote: > > > > Tom Lane wrote: > > > Ken Corey <ken@kencorey.com> writes: > > > > GRE=# select > > > > I_SESSION(null,'54',2,'E2K','0x577A977F7FCCBF5A567C6097A5A5A9D47DA39B82298C6E9F89DA4D4F67E39B856692DDAA8A938E637D7F6C7000000000000000000000000000000000000000000000000000000000000000000'); > > > > GRE'# ' > > > > GRE-# ); > > > > ERROR: parser: parse error at or near > > > > "0000000000000000000000000000000000" > > > > GRE=# > > > > > > > [Note that the final ' wasn't found, so I had to add it, and then close > > > > the parenthesis and execute the whole mess with the semi colon.] > > > > > > That's pretty odd; you seem to have a broken psql. What PG version are > > > you running? Do you have libreadline (history support) in there, and if > > > so what libreadline version is it? > > > > I downloaded pg 7.0.3, untarred it, went into the directory, typed: > > > > configure > > make > > make install > > > > No libreadline (though I should get it...I'd like to have up-arrow to go > > back to previous lines in psql!) > > > > Any other ideas? > > > > I've downloaded 7.1b3 to try out. Since I won't have any real critical > > data in there until March timeframe, I can afford to play with the > > latest version. If that fixes it, I'll report back to this list. > > > > -Ken
Ken Corey <ken@kencorey.com> writes:
> Drat! Thought this wa identified...but sure enough it's still causing troubles. Will report shortly.
Do you mean your previous description was incorrect, or that it is
correct but there are more squirrelies besides that one? If what you
said was correct as far as it went, then we do have a bug to fix,
I think. plpgsql should either work or generate a reasonable error
message when the actual parameter length exceeds the declared length
of the formal parameter.
Right offhand, I would have said that the declared length of a function
parameter would be completely ignored, but perhaps I am mistaken ...
regards, tom lane
Ken Corey <ken@kencorey.com> writes:
> 1) Problem: Under Solaris, when entering a really long sql parameter
> into psql, the first bytes would be chopped off:
> GRE=# select
>
I_SESSION(null,'1',2,'E2K','0x637B9C8B443E9AA0AF4C696D7E8AB27B4FA57B5A6DBDD29851B74BDE5D97A3995F8F8A825F8B4D95D7687CAE94E77F809B589363685A718B72A28C90AA872936297B4020360B46752B2E2042172232171C59F730193153466B2F4A42270E563A29FF4ED30C47E340396056691B1485512D1F3E2E2554124D2D47821E3BF86031335C24444E00063112644D41F09C6F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');
> GRE'# ');
> ERROR: parser: parse error at or near
> "000000000000000000000000000000000"
> GRE=#
> Cause:
> Solaris' fgets function *only* returns the last part of a string if the
> string is over 256 bytes! The test was this little program:
> #include <stdio.h>
> int main(int argc,char **argv) {
> char line[1024];
> while((fgets(line,1024,stdin)) != EOF) {
> printf("Got '%s'\n",line);
> }
> }
> Enter in really big lines (> 256 characters), and you'll see what I
> mean.
Yeesh. I wonder if any other Unixen are equally broken?
Perhaps we should reduce psql's fgets() buffers to 256 bytes or less to
avoid this problem?
regards, tom lane
Tom Lane wrote:
> Ken Corey <ken@kencorey.com> writes:
> > Drat! Thought this wa identified...but sure enough it's still causing troubles. Will report shortly.
>
> Do you mean your previous description was incorrect, or that it is
> correct but there are more squirrelies besides that one? If what you
> said was correct as far as it went, then we do have a bug to fix,
> I think. plpgsql should either work or generate a reasonable error
> message when the actual parameter length exceeds the declared length
> of the formal parameter.
>
> Right offhand, I would have said that the declared length of a function
> parameter would be completely ignored, but perhaps I am mistaken ...
Aha. Well. Finally got to the bottom of it, and got it all fixed.
There were two problems.
1) Problem: Under Solaris, when entering a really long sql parameter
into psql, the first bytes would be chopped off:
GRE=# select
I_SESSION(null,'1',2,'E2K','0x637B9C8B443E9AA0AF4C696D7E8AB27B4FA57B5A6DBDD29851B74BDE5D97A3995F8F8A825F8B4D95D7687CAE94E77F809B589363685A718B72A28C90AA872936297B4020360B46752B2E2042172232171C59F730193153466B2F4A42270E563A29FF4ED30C47E340396056691B1485512D1F3E2E2554124D2D47821E3BF86031335C24444E00063112644D41F09C6F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');
GRE'# ');
ERROR: parser: parse error at or near
"000000000000000000000000000000000"
GRE=#
Cause:
Solaris' fgets function *only* returns the last part of a string if the
string is over 256 bytes! The test was this little program:
#include <stdio.h>
int main(int argc,char **argv) {
char line[1024];
while((fgets(line,1024,stdin)) != EOF) {
printf("Got '%s'\n",line);
}
}
Enter in really big lines (> 256 characters), and you'll see what I
mean.
Solution:
The fix is to install the GNU Readline library, and recompile postgres
to use it. Job done, and the interface is *much* nicer, now.
2) The libpq problem was my mis-handling of pointers. Gotta love C. As
near as I can tell, it's working okay now (although I think I'm getting
some error messages back that I should explore, at least it's not
core-dumping).
-Ken
Tom Lane wrote:
[...] extraneous bug description removed.
> > Solaris' fgets function *only* returns the last part of a string if the
> > string is over 256 bytes! The test was this little program:
>
> > #include <stdio.h>
> > int main(int argc,char **argv) {
> > char line[1024];
> > while((fgets(line,1024,stdin)) != EOF) {
> > printf("Got '%s'\n",line);
> > }
> > }
>
> > Enter in really big lines (> 256 characters), and you'll see what I
> > mean.
>
> Yeesh. I wonder if any other Unixen are equally broken?
FWIW, I tested it on my Linux box (2.2.15-4mdk, a mandrake 7.2 distro),
and it worked fine. I can't speak to other unixes.
> Perhaps we should reduce psql's fgets() buffers to 256 bytes or less to
> avoid this problem?
Hrm...I don't know so much about that. That would limit it on boxes
where the fgets works as advertised.
Actually, how hard is it to add another test to the auto-configurtion
file? The little program above is small enough that it should be a
fairly simple deal to compile it during configuration, pipe input
through it, and see if fputs is limited. If it is, then the
configuration script could output a big warning message saying 'for your
own good, download and install readline, and then recompile'.
*shrug* At the very least, perhaps it could be put in the FAQ and/or
install docs?
-Ken
Ken Corey <ken@kencorey.com> writes:
>> Perhaps we should reduce psql's fgets() buffers to 256 bytes or less to
>> avoid this problem?
> Hrm...I don't know so much about that. That would limit it on boxes
> where the fgets works as advertised.
In theory it should not, but then in theory Solaris shouldn't have this
behavior either.
regards, tom lane
Ken Corey <ken@kencorey.com> writes:
> Solaris' fgets function *only* returns the last part of a string if the
> string is over 256 bytes! The test was this little program:
>
> #include <stdio.h>
> int main(int argc,char **argv) {
> char line[1024];
> while((fgets(line,1024,stdin)) != EOF) {
> printf("Got '%s'\n",line);
> }
> }
>
> Enter in really big lines (> 256 characters), and you'll see what I
> mean.
I very much doubt it is the fgets function.
I suspect it is the terminal driver. The terminal driver only hands
data back to the application when it sees a newline. It probably only
holds 256 characters.
Try using an editor to create a file with a line longer than 256
characters, and run `test < file', to see whether it is fgets or the
terminal driver.
Ian
Ian Lance Taylor wrote: > I very much doubt it is the fgets function. > > I suspect it is the terminal driver. The terminal driver only hands > data back to the application when it sees a newline. It probably only > holds 256 characters. > > Try using an editor to create a file with a line longer than 256 > characters, and run `test < file', to see whether it is fgets or the > terminal driver. You're exactly on the money. Thanks. Where were you this morning? *smile* Since a partiularly long SQL query was the first time I'd seen this kind of a problem (and others might see it), perhaps it still deserves a paragraph in the FAQ, even though it has *nothing* to do with postgreSQL directly? -Ken
Ken Corey <ken@kencorey.com> writes: > You're exactly on the money. Thanks. Where were you this morning? > *smile* Ha, well, funny you should ask, this morning rolling blackouts in Northern California shut down my e-mail access. Silicon Valley joins the third world. Ian
Tom Lane writes:
> Perhaps we should reduce psql's fgets() buffers to 256 bytes or less to
> avoid this problem?
Sure.
Or maybe fscanf("%s") works?
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/