Обсуждение: size of function body
Hi, after some fooling around I figured out that a function body can't be larger than the defined postgres' block size, which defaults to 8192 byte. The same time I read enlarging the default block size has a negative performance impact. How would the "long time experts" decide on the following issues given: (1) running postgres 7.2.1 on a 32 bit system (2) needing (if not splitting up, which would be a tedious work) about at least 30 / 35 functions larger than about 20 to 25 kbyte each (3) those functions are called for almost every query, which means all input/output from the application to the database is done by calling functions Another issue: If one has got nested function (function 1 calling function 2, 3, 4, 5 to do the work) and a "subfunction" (2, 3, 4 or 5) gets dropped and recreated, the reference of function 1 to that subfunction gets lost. Is there any easy way to "recompile" all depending subfunctions? If anyone wants to point me to some documentation, I am glad to read through those issues on my own. Just didn't find anything documented. Kind regards ... Ralph ...
Ralph Graulich wrote: > Hi, > > > after some fooling around I figured out that a function body can't be > larger than the defined postgres' block size, which defaults to 8192 > byte. The same time I read enlarging the default block size has a negative > performance impact. > > How would the "long time experts" decide on the following issues given: > > (1) running postgres 7.2.1 on a 32 bit system > (2) needing (if not splitting up, which would be a tedious work) about at > least 30 / 35 functions larger than about 20 to 25 kbyte each > (3) those functions are called for almost every query, which means all > input/output from the application to the database is done by calling > functions Given this set of issues, I would write my functions in C. Joe
Ralph Graulich <maillist@shauny.de> writes:
> after some fooling around I figured out that a function body can't be
> larger than the defined postgres' block size, which defaults to 8192
> byte.
Say what? That hasn't been true since TOAST was introduced in 7.1.
regards, tom lane
Hi, [...] >> a function body can't be larger than the defined postgres' >>block size, which defaults to 8192 byte. > Say what? That hasn't been true since TOAST was introduced in 7.1. Just read about the possible errors and thought I was running into this one. But as you said, this limit was widened with 7.1 and onwards, I seem to have an other problem. Given a function with 29 parameters, consisting of 5 INTEGERs and the rest being VARCHARs, reALIASing all the parameter in the DECLARE block and using them in several SQL statements within the function's body, I began to receive that error: ERROR: parser: parse error at or near "SELECT" 89299< The plain text size of the function is: 8190 Jul 16 21:31 function_update_dam.func which counts for 8190 bytes. So I just doubled a simple UPDATE-Statement at the end of the function just to get the code size larger than 8192 bytes. UPDATE dam_key_family_4 SET fam_dosage_idx='54.5fs'; Just doubled that statement. And the error occurs again (just using NULL for testing) SELECT update_dam(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); ERROR: parser: parse error at or near "SELECT" 90122< Things I recognized: The numbers after the "SELECT" get higher each time I try the statement and seems to wrap around in the negative range, when it reaches values larger than 1,000,000 (presumably 1,048,576?). Difficult to describe, just tell me what more detailled information you need. Kind regards ... Ralph ...
Ralph Graulich <maillist@shauny.de> writes: > Given a function with 29 parameters, consisting of 5 INTEGERs and the rest > being VARCHARs, reALIASing all the parameter in the DECLARE block and > using them in several SQL statements within the function's body, I began > to receive that error: > ERROR: parser: parse error at or near "SELECT" 89299< Hmm. Are there any long literal strings in your function text? (Not sure, but I think "long" may mean "> 256 bytes" in this context.) If so, you might be running into a recently-discovered bug in plpgsql. Try substituting the latest version of pl_funcs.c, see http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/pl_funcs.c If that doesn't do it for you, we'll need to see a reproducible test case. regards, tom lane
Hi Tom, > Hmm. Are there any long literal strings in your function text? Sorry for asking more questions to track down the problem: What exactly do you mean by "long literal string"? Does it mean the length of a variable name or the length of the content of a variable? The longest variable names are: running_total_of_coverings_by_same_family_sire running_total_of_coverings_by_different_family_sire running_total_of_active_breeding_years But only used three or four times. But I do concatenating of string lots of time within the code, which means I store several derived variable contents into one single variable and put that in a TEXT field in an UPDATE statement. Can this cause the problem you described? Nonetheless I am willing to try the patch, for sure! Kind regards ... Ralph ...
Ralph Graulich <maillist@shauny.de> writes:
>> Hmm. Are there any long literal strings in your function text?
> What exactly do you mean by "long literal string"? Does it mean the length
> of a variable name or the length of the content of a variable?
The particular case that the bug was reported for was a long string
literal, eg 'lots of text here'. I'm not sure what else the dstring
functions are used for, however. There might be many other cases that
could trigger that bug.
regards, tom lane
Hi Tom, > The particular case that the bug was reported for was a long string > literal, eg 'lots of text here'. Thanks for explaining this case to me. Indeed I am using long string literals, now that I know what this means *g*. I am deriving a describing text out of several variables, constant text string and a some queries for putting together some kind of "data and facts sheet" stored in a TEXT field. Some text can be quite really large, as it is derived by recursion. Thus I think I am hit by that particular bug. After getting the current source patch, I'll recompile, test it and let you know wether that fixed my problem. Thanks for your assistance! Kind regards ... Ralph ...
Hi Tom, > The particular case that the bug was reported for was a long string > literal, eg 'lots of text here'. [...] Downloaded the 1.18 version of pl_funcs.c and put it in the source code tree, "make clean", "make", and it hangs: gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -Wno-error -fpic -I. -I../../../../src/include -I/usr/local/ssl/include -c -o pl_funcs.o pl_funcs.c pl_funcs.c:8: unterminated character constant pl_funcs.c:13: unterminated character constant pl_funcs.c:15: unterminated character constant pl_funcs.c:17: unterminated character constant make[4]: *** [pl_funcs.o] Error 1 make[4]: Leaving directory `/usr/src/postgresql-7.2.1/src/pl/plpgsql/src' make[3]: *** [all] Error 2 make[3]: Leaving directory `/usr/src/postgresql-7.2.1/src/pl/plpgsql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/postgresql-7.2.1/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/src/postgresql-7.2.1/src' make: *** [all] Error 2 Kind regards ... Ralph ...
Ralph Graulich <maillist@shauny.de> writes:
> Downloaded the 1.18 version of pl_funcs.c and put it in the source code
> tree, "make clean", "make", and it hangs:
> gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -Wno-error -fpic
> -I. -I../../../../src/include -I/usr/local/ssl/include -c -o pl_funcs.o
> pl_funcs.c
> pl_funcs.c:8: unterminated character constant
> pl_funcs.c:13: unterminated character constant
> pl_funcs.c:15: unterminated character constant
> pl_funcs.c:17: unterminated character constant
It compiles fine here. You sure you got a clean download?
regards, tom lane
[...] > > pl_funcs.c:15: unterminated character constant > > pl_funcs.c:17: unterminated character constant > It compiles fine here. You sure you got a clean download? After I did a "make distclean", ran configure again and made the necessary changes for supporting more than 16 function parameters, it compiled fine for me, too. Maybe "make clean" just wasn't enough this time. Installed and tested. Added the testing UPDATE statement again. It works. Added the testing UPDATE statement 20 times more, just to get the code larger. Even this way it works, now. Seems like patching pl_funcs.c fixed that bug for me! So the 8190/2 byte limit for the bug occuring was more or less accidently... *shrugs* Maybe we can have a bug description included in the online error docs somewhere, so others can use it for future reference? Many thanks for your friendly help. Kind regards ... Ralph ... happy child again
Hi Jon, [...] > > (3) those functions are called for almost every query, which means all > > input/output from the application to the database is done by calling > > functions > Given this set of issues, I would write my functions in C. Given the fact that I have very little knowledge of C, I better stick to SQL in which I have so-to-say ten years experience more than in C. :-) On the other hand, I'd love to try coding at least one of the functions in C, if I can count on assistance or some easy example. Maybe I get to love it that way and this encourages me learning more of C. Maybe you can point me to some good examples? Thanks alot. Kind regards ... Ralph ...
Ralph Graulich wrote: > Given the fact that I have very little knowledge of C, I better stick to > SQL in which I have so-to-say ten years experience more than in C. :-) > > On the other hand, I'd love to try coding at least one of the functions in > C, if I can count on assistance or some easy example. Maybe I get to love > it that way and this encourages me learning more of C. > > Maybe you can point me to some good examples? Thanks alot. > Sure, look in the contrib directory in your source tree. There are lots of examples there, for example look at contrib/fuzzystrmatch. Also see: http://www.postgresql.org/idocs/index.php?xfunc-c.html My main point was that if all of your I/O is going through the functions, and the functions themselves are large and complex, you may get a significant overall performance boost by using C functions instead. Good luck! Joe