From: | "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Many Pl/PgSQL parameters -> AllocSetAlloc(128)? |
Date: | 2003-06-24 05:18:44 |
Message-ID: | 16119.57140.38343.327041@henrietta-szold.lerner.co.il |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers pgsql-patches |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Grant | 2003-06-24 05:54:45 | Re: [pgsql-advocacy] interesting PHP/MySQL thread |
Previous Message | nolan | 2003-06-24 05:07:32 | Re: [GENERAL] interesting PHP/MySQL thread |
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2003-06-24 05:29:37 | Re: Two weeks to feature freeze |
Previous Message | Dann Corbit | 2003-06-24 05:14:36 | Re: Two weeks to feature freeze |
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2003-06-24 06:41:44 | Re: Many Pl/PgSQL parameters -> AllocSetAlloc(128)? |
Previous Message | Tom Lane | 2003-06-24 03:55:06 | Re: Patch to be verbose about being unable to read ~/.pgpasss... |