backend process crash - PL/pgSQL functions - parsing problem?

backend process crash - PL/pgSQL functions - parsing problem?

Post by Damon Ha » Sun, 02 Feb 2003 09:33:43



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 */

 
 
 

backend process crash - PL/pgSQL functions - parsing problem?

Post by Tom La » Sat, 08 Feb 2003 07:10:28



> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

backend process crash - PL/pgSQL functions - parsing problem?

Post by Damon Ha » Sat, 15 Feb 2003 07:02:46



> 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

 
 
 

1. Problem parsing functions makes PgSQL Restore impossible

Hi!

I think I found a problem (bug?) in PgSQL:

If there is a:

<functionA>( <functionB> ( args...) )

in a CREATE VIEW, PgSQL then translates it as:
<functionB> ( args... ).<functionB>.

This makes 'pg_dump' generate invalid Views and thus Views could not
be restored afterwards :(

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EXAMPLE taken (part of a real View) from PgAdmin views:

1. Written:
CREATE VIEW test AS SELECT
sequence_last_value(pgadmin_get_sequence(c.oid)) AS
sequence_last_value FROM pg_class c;

2. pg_dump generates ?!?:
CREATE VIEW test AS SELECT
pgadmin_get_sequence(c.oid).sequence_last_value AS sequence_last_value
FROM pg_class c;
NOTE: PgAdmin also shows the same data!

3. psql restores data generated by pg_dump and produces an error:
ERROR:  parser: parse error at or near "."
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

BACKGROUND:
I user PostgreSQL 7.1.3 and PgAdmin 7.1.0 (both latest versions I
hope);

After PgAdmin openes a database it usually creates a number of system
Tables and Views that start with 'pgadmin_'.

After a pg_dump is made on such a database and then restored
2 errors appeare in CREATE VIEW part as discribed above.
Errors are during creation of VIEWS named 'pgadmin_sequences' and
'pgadmin_tables'.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
QUESTION:
What should I do to successfully Backup & Restore these (or such type)
of Views?

Best regards
sth

2. Test your RDBMS knowledge at new site - 'top ten' list has been updated!

3. pgsql-server/src/backend/parser parse_expr.c

4. fpw26 and windows/browse/boxes

5. pgsql-server/src/backend/parser scan.l

6. Stupid Question, but need help!

7. automatic restart on reboot

8. pgsql-server/src/backend/parser gram.y

9. pgsql-server/src/backend/parser parse_agg.c

10. pgsql-server/src/backend/parser analyze.c

11. pgsql-server/src/backend/parser scan.l

12. pgsql-server/src/backend/parser parse_expr.c