Many Pl/PgSQL parameters -> AllocSetAlloc(128)? - Mailing list 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