Обсуждение: backend process crash - PL/pgSQL functions - parsing problem?

My apologies for the long posting to the general group. Please follow
up via email if appropriate and I will post a summary.

I have a related set of PL/pgSQL functions which, when executed, crash
the backend associated with my connection, dropping me out of the
front end (psql) with the message:

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

The postgresql log indicates that the backend is crashing with
SIGSEGV. This is postgreSQL 7.2 running on Linux (Intel, roughly
Mandrake 8.2)

I adjusted whatever came to mind in the functions themselves until,
just by luck, I found that if I shortened the length of the function
names, the crash disappeared.

Below follows a simplified rendering of the functions which can
reproduce the crash behavior. Strangely, either shortening the
function names or deleting the filler comments eliminates the
crashing.

Since such changes seem completely textual, I can only speculate that
there is a problem in the parsing. However, it's interesting to note
that the crash only happens when the functions b_345...() and
c_345...() are called from the body of a_345(). They can be
successfully
called independently from psql - in fact, following such a call to
b_345...(), subsequent calls to a_345...() succeed (for the remainder
of the connection.)

I have working versions of my functions, but I'd like to understand
the whys and wherefores of a problem that I'm likely to encounter
again.


thanks,

Damon Hart


/* begin pg_error.sql */

CREATE OR REPLACE FUNCTION a_34567890(INTEGER) RETURNS INTEGER AS '
BEGIN

EXECUTE ''SELECT b_3456789012345678901234567890('' || $1 || '');'';
EXECUTE ''SELECT c_3456789012345678901234567890('' || $1 || '');'';
RETURN $1;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION b_3456789012345678901234567890(INTEGER)
RETURNS INTEGER AS '
BEGIN

CREATE OR REPLACE FUNCTION c_3456789012345678901234567890(INTEGER)
RETURNS INTEGER AS ''
DECLARE

BEGIN

-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments

RETURN $1;

END;
'' LANGUAGE ''plpgsql'';

-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments
-- just a bunch of comments

RETURN $1;

END
' LANGUAGE 'plpgsql';

/* end pg_error.sql */

damonhart@yahoo.com (Damon Hart) writes:
> The postgresql log indicates that the backend is crashing with
> SIGSEGV. This is postgreSQL 7.2 running on Linux (Intel, roughly
> Mandrake 8.2)

> I adjusted whatever came to mind in the functions themselves until,
> just by luck, I found that if I shortened the length of the function
> names, the crash disappeared.

I think this is probably due to this bug:

2002-05-05 13:38  tgl

    * src/pl/plpgsql/src/pl_funcs.c (REL7_2_STABLE):
    plpgsql_dstring_append was broken for long strings.

If so, updating to 7.2.2 or later should fix it.

            regards, tom lane

tgl@sss.pgh.pa.us (Tom Lane) wrote in message news:<15793.1044566157@sss.pgh.pa.us>...
> I think this is probably due to this bug:
>
> 2002-05-05 13:38  tgl
>
>     * src/pl/plpgsql/src/pl_funcs.c (REL7_2_STABLE):
>     plpgsql_dstring_append was broken for long strings.
>
> If so, updating to 7.2.2 or later should fix it.
>

thanks, I had hesitated to try out a newer version, but with your
insight I will give this a shot and post a result.

Damon