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
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
'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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
