Re: [GENERAL] Many Pl/PgSQL parameters -> AllocSetAlloc(128)? - Mailing list pgsql-hackers

(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