Thread: repeated syntax errors in plpython modules cause database to reset

David Casti (david@casti.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
repeated syntax errors in plpython modules cause database to reset

Long Description
I am migrating from PL TCL to PL Python for my procedural language.  However, when my PL Python procedures generate
successivesyntax errors, the database resets.  Usually this happens after the second or third syntax error in a row
duringa debugging process. 

The syntax error in the log looks like this --

ERROR:  plpython: Unable to compile function __plpython_procedure_assign_pricing_plan_to_product_149343
exceptions.SyntaxError: invalid syntax (line 32)

-- The database blowup error looks like this --

Server process (pid 5424) exited with status 11 at Sat Jun  2 12:05:34 2001
Terminating any active server processes...
NOTICE:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend  died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am       going to terminate your database system connection and
exit.
        Please reconnect to the database system and repeat your query.
Server processes were terminated at Sat Jun  2 12:05:34 2001
Reinitializing shared memory and semaphores
DEBUG:  database system was interrupted at 2001-06-02 11:56:20 EDT
DEBUG:  CheckPoint record at (0, 83641580)
DEBUG:  Redo record at (0, 83641580); Undo record at (0, 0); Shutdown FALSE
DEBUG:  NextTransactionId: 21970; NextOid: 149692
DEBUG:  database system was not properly shut down; automatic recovery in progress...
DEBUG:  redo starts at (0, 83641644)
DEBUG:  ReadRecord: record with zero len at (0, 83735656)
DEBUG:  redo done at (0, 83735620)
DEBUG:  database system is in production state

-- I then must close all database connections and sessions and re-open them to continue debugging.

Sample Code
This is the function which I was debugging when I encountered this problem:

CREATE FUNCTION assign_pricing_plan_to_product(VARCHAR, VARCHAR, VARCHAR
                                                , TEXT, BIGINT, BIGINT) RETURNS BIGINT AS '

    (v_username, v_cookie, v_application, t_reason, i_pricing_plan_id, i_product_id) = args

    plpy.execute("""SELECT  die_object_is_of_rsrc_type(%d, ''pricing_plan'',
                            ''assign_pricing_plan_to_product'')""" \
                            % (i_pricing_plan_id))
    plpy.execute("""SELECT  die_object_is_of_rsrc_type(%d, ''product'',
                            ''assign_pricing_plan_to_product'')""" \
                            % (i_product_id))

    i_old_pricing_plan = plpy.execute("""SELECT right_rsrc_id AS i_old_pricing_plan
                                         FROM   product_pricing_plan
                                         WHERE  left_rsrc_id = """ + str(i_product_id))[0]["i_old_pricing_plan"]

    plpy.debug("12345 in assign_pricing_plan_to_product.  i_old_pricing_plan is " + str(i_old_pricing_plan))

    if i_old_pricing_plan:
        plpy.debug("in assign_pricing_plan_to_product, i_old_pricing_plan exists")
        plpy.execute("""SELECT  log_price_change(
                                ''""" + v_username + """''
                                , ''""" + v_cookie + """''
                                , ''Overwriting previous price plan''
                                , ''""" + v_application + """''
                                , ''CHANGE''
                                , """ + str(i_product_id) + """
                                , ''""" + t_reason + """''
                                , """ + str(i_old_pricing_plan) + """
                                , """ + str(i_pricing_plan_id) + """
                                )"""
    else:
        plpy.debug("in assign_pricing_plan_to_product, i_old_pricing_plan does not exist")
        plpy.execute("""SELECT  log_price_change(
                                ''""" + v_username + """''
                                , ''""" + v_cookie + """''
                                , ''Initializing product price plan for the first time''
                                , ''""" + v_application + """''
                                , ''NEW''
                                , """ + str(i_product_id) + """
                                , ''""" + t_reason + """''
                                , 0
                                , """ + str(i_pricing_plan_id) + """
                                )"""

    plpy.debug("in assign_pricing_plan_to_product, finished with log_price_change, about to create_relationship")
    i_rel_id = plpy.execute("""SELECT   create_relationship(''""" + v_username + """''
                                            , ''""" + v_cookie + """''
                                            , ''product_pricing_plan''
                                            , ''Assign a Pricing Plan to a Product''
                                            , ''product_pricing_plan''
                                            , ''''
                                            , """ + str(i_product_id) + """
                                            , """ + str(i_pricing_plan_id) + """
                                            ) as i_rel_id""")[0]["i_rel_id"]
    plpy.debug("in assign_pricing_plan_to_product, relationship created, returning")
    return i_rel_id
' LANGUAGE 'plpython';


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> repeated syntax errors in plpython modules cause database to reset

Hm.  Can you provide a debugger backtrace from the crash?  Either look
for a core file in the database subdirectory, or attach gdb to the
running backend process before you induce the crash.

            regards, tom lane