size of function body

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