Many Pl/PgSQL parameters -> AllocSetAlloc(128)?
От | Reuven M. Lerner |
---|---|
Тема | Many Pl/PgSQL parameters -> AllocSetAlloc(128)? |
Дата | |
Msg-id | 16119.57140.38343.327041@henrietta-szold.lerner.co.il обсуждение исходный текст |
Ответы |
Re: Many Pl/PgSQL parameters -> AllocSetAlloc(128)?
(Joe Conway <mail@joeconway.com>)
|
Список | pgsql-general |
I'm creating a new OpenACS package that uses PostgreSQL, and in doing so have encountered what seems to be a problem in PostgreSQL. Specifically, I defined a Pl/PgSQL function with 24 parameters. This should be OK under PostgreSQL 7.3, which I'm running. (I'm using the RPM version under Red Hat 7.3, and haven't encountered any other problems with PostgreSQL on this machine.) I can define the function just fine, as you can see form this stub that I created for testing: 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) returns integer as ' declare p_item_id alias for $1; -- default null/integer -- p_locale alias for $2; -- default null/varchar -- p_publish_date alias for $3; -- default null/timestamptz p_text alias for $4; -- default null/text p_nls_language alias for $5; -- default null/varchar p_title alias for $6; -- default null/varchar p_mime_type alias for $7; -- default ''text/plain''/varchar -- p_package_id alias for $8; -- default null/integer p_archive_date alias for $9; -- default null/timestamptz p_approval_user alias for $10; -- default null/integer p_approval_date alias for $11; -- default null/timestamptz p_approval_ip alias for $12; -- default null/varchar -- p_relation_tag alias for $13; -- default null/varchar -- p_creation_ip alias for $14; -- default null/varchar p_creation_user alias for $15; -- default null/integer -- p_is_live_p alias for $16; -- default ''f''/boolean p_subtitle alias for $17; -- default null/text p_abstract alias for $18; -- default null/text p_notes alias for $19; -- default null/text 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 p_headline_position alias for $24; -- default null/integer v_add_news_id integer; v_item_id integer; v_id integer; v_revision_id integer; v_parent_id integer; v_name varchar; v_log_string varchar; begin RAISE NOTICE ''blah'' end; ' language 'plpgsql'; But when I try to invoke the function, one of the postmaster processes consumes all available memory until I get an AllocSetAlloc error. Here's a sample invocation: select add_news__test( 1000::integer, -- p_item_id 'en_US'::varchar, -- p_locale '2003-6-23'::timestamptz, -- p_publish_date 'text text text'::varchar, -- p_text 'language'::varchar, -- p_nls_language 'title'::varchar, -- p_title 'text/plain'::varchar, -- p_mime_type '1049'::integer, -- p_package_id '2003-7-7'::timestamptz, -- p_archive_date '298'::integer, -- p_approval_user '2003-06-23'::timestamptz, -- p_approval_date '212.29.241.228'::varchar, -- p_approval_ip 'relation'::varchar, -- p_relation_tag '212.29.241.228'::varchar, -- p_creation_ip '298'::integer, -- p_creation_user 't'::boolean, -- p_is_live_p 'subtitle'::varchar, -- p_subtitle 'abstract'::varchar, -- p_abstract 'note'::varchar, -- p_notes now(), -- p_last_mod_date '298'::integer, -- p_modified_by 'image.jpeg'::varchar, -- p_image_filename 'Category page'::varchar, -- p_headline_page '1'::integer -- p_headline_position ); I thought that it was my function definition that was causing trouble. But I create a new dummy function (as you can see from its definition above) that does nothing but produce a notification. And yet, that function also consumes all available memory. Several questions: - It is OK to have 24 parameters to a function in 7.3, right? (No, I wouldn't normally want to have that many, but I find it hard to see where I can give up on any of them.) - I've tried to change "text" parameters to varchar, and set all null parameters to non-null values, just to see if either of these would make a difference. Unfortunately, they did not. - Would compiling PostgreSQL myself, rather than using the RPM version, change things? - Is there a parameter in postgresql.conf that I can/should update? - I've already raised the ceiling on memory usage, so far as I can tell -- and invoking my function now consumes lots more memory before giving me this error. So the problem is PostgreSQL, not my machine. - Am I missing some other important issue? At this point, I think that I'm going to look at a completely different implementation strategy that will (a) avoid the use of functions and (b) allow me to hit my deadline. But it's a shame that I have to ignore the OpenACS functionality on which I wanted to depend, simply because of this issue. Reuven
В списке pgsql-general по дате отправления:
Предыдущее
От: nolan@celery.tssi.comДата:
Сообщение: Re: [pgsql-advocacy] interesting PHP/MySQL thread