Re: Many Pl/PgSQL parameters -> AllocSetAlloc(128)?

(cross-posting to HACKERS)

Reuven M. Lerner wrote:
> I'm creating a new OpenACS package that uses PostgreSQL, and in doing
> so have encountered what seems to be a problem in PostgreSQL.

[...snip...]

> CREATE OR REPLACE FUNCTION add_news__test
> (integer,varchar,timestamptz,varchar,varchar,varchar,
> varchar,integer,timestamptz,integer,timestamptz,varchar,varchar,
> varchar,integer,boolean, varchar, varchar, varchar, timestamptz,
> integer, varchar, integer, integer)
^^^^^^^^
[...snip...]

> p_last_mod_date alias for $20; -- default null/timestamptz
> p_modified_by alias for $21; -- default null/integer
> p_last_mod_date alias for $20; -- default null/timestamptz
> p_modified_by alias for $21; -- default null/integer
^^^^^^^^^^^^^ above two lines repeated

> p_image_filename alias for $22; -- default null/text
> p_headline_page alias for $23; -- default null/integer
^^^^^^^^^^^^^^^^^^^^
more importantly, you call the function (below) with a varchar here, not
integer
> p_headline_position alias for $24; -- default null/integer

[...snip...]

> now(), -- p_last_mod_date
^^^^^^^ try 'now'::timestamptz
> '298'::integer, -- p_modified_by
> 'image.jpeg'::varchar, -- p_image_filename
> 'Category page'::varchar, -- p_headline_page
^^^^^^^^^^^ this one should be an integer

> '1'::integer -- p_headline_position
> );

You found a real bug, I can confirm it on CVS tip.

However your workaround is to call the function *exactly* as declared.
Otherwise in parse_func.c:gen_cross_product() the following code is
executed:

<snippet>
nanswers = 1;
for (i = 0; i < nargs; i++)
{
nanswers *= (arginh[i].nsupers + 2);
cur[i] = 0;
}

iter = result = (Oid **) palloc(sizeof(Oid *) * nanswers);
</snippet>

I get nanswers = 16777216, so right off the bat 67MB or so is allocated.
Then there's this:

<snippet>
/* compute the cross product from right to left */
for (;;)
{
oneres = (Oid *) palloc0(FUNC_MAX_ARGS * sizeof(Oid));
</snippet>

I'm guessing this gets executed nanswers times. I saw memory usage grow
to 880 MB and then killed the process.

I'm not sure of the best way to fix this yet, but I found that when
calling the function with argument types matching the prototype
perfectly, this code never gets executed.

HTH,

Joe

p.s. here's a backtrace:

#0 AllocSetAlloc (context=0x830a624, size=128) at aset.c:731
#1 0x081bcb14 in MemoryContextAllocZero (context=0x830a624, size=128)
at mcxt.c:505
#2 0x080c5c03 in gen_cross_product (arginh=0xbfffd120, nargs=24) at
parse_func.c:1094
#3 0x080c59b6 in argtype_inherit (nargs=24, argtypes=0xbfffd350) at
parse_func.c:975
#4 0x080c5836 in func_get_detail (funcname=0x831451c, fargs=0x83178e8,
nargs=24, argtypes=0xbfffd350, funcid=0xbfffd33c,
rettype=0xbfffd340, retset=0xbfffd347 "\bÁ\002",
true_typeids=0xbfffd348) at parse_func.c:891
#5 0x080c4c4c in ParseFuncOrColumn (pstate=0x8317810,
funcname=0x831451c, fargs=0x83178e8, agg_star=0 '\0',
agg_distinct=0 '\0', is_column=0 '\0') at parse_func.c:241
#6 0x080c41de in transformExpr (pstate=0x8317810, expr=0x8317714) at
parse_expr.c:399
#7 0x080cb4ed in transformTargetEntry (pstate=0x8317810,
node=0x8317714, expr=0x0, colname=0x0, resjunk=0 '\0')
at parse_target.c:60
#8 0x080cb53b in transformTargetList (pstate=0x8317810,
targetlist=0x831774c) at parse_target.c:193
#9 0x080b61c8 in transformSelectStmt (pstate=0x8317810, stmt=0x8317768)
at analyze.c:1771
#10 0x080b41b7 in transformStmt (pstate=0x8317810, parseTree=0x8317768,
extras_before=0xbfffd574, extras_after=0xbfffd578)
at analyze.c:407
#11 0x080b402b in do_parse_analyze (parseTree=0x8317768,
pstate=0x8317810) at analyze.c:234
#12 0x080b3f44 in parse_analyze (parseTree=0x8317768,
paramTypes=0x830a624, numParams=137405988) at analyze.c:159
#13 0x08159c3c in pg_analyze_and_rewrite (parsetree=0x8317768,
paramTypes=0x0, numParams=0) at postgres.c:482
#14 0x08159f83 in exec_simple_query (
query_string=0x8313c40 " select add_news__test(\n
1000::integer,", ' ' <repeats 15 times>, "\n 'en_US'::varchar,", ' '
<repeats 15 times>, "\n '2003-6-23'::timestamptz, \n 'text text
text'::varchar, \n 'language'::varchar, "...) at
postgres.c:795
#15 0x0815bd1b in PostgresMain (argc=4, argv=0x829aa9c,
username=0x829aa64 "postgres") at postgres.c:2753
#16 0x0813a531 in BackendFork (port=0x82a80c0) at postmaster.c:2471
#17 0x0813a026 in BackendStartup (port=0x82a80c0) at postmaster.c:2118
#18 0x08138b5f in ServerLoop () at postmaster.c:1090
#19 0x081384dd in PostmasterMain (argc=5, argv=0x829a4c8) at
postmaster.c:872
#20 0x0810f713 in main (argc=5, argv=0xbfffe334) at main.c:211
#21 0x420156a4 in __libc_start_main () from /lib/tls/libc.so.6