Skip site navigation (1) Skip section navigation (2)

Crash when calling a pl/pgsql function with no row to pass as an argument

From: Chris Campbell <chris(at)bignerdranch(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Cc: Aaron Hillegass <aaron(at)bignerdranch(dot)com>
Subject: Crash when calling a pl/pgsql function with no row to pass as an argument
Date: 2004-02-17 17:26:32
Message-ID: 6DB83DBC-616E-11D8-8F01-000393147784@bignerdranch.com (view raw or flat)
Thread:
Lists: pgsql-bugs
(Attempting to send this to pgsql-bugs a second time without Cc:'ing  
pgsql-patches, since it doesn't appear that my February 15 message got  
through.)

======================================================================== 
====
                         POSTGRESQL BUG REPORT TEMPLATE
======================================================================== 
====


Your name		:	Chris Campbell
Your email address	:   chris(at)bignerdranch(dot)com


System Configuration
---------------------
   Architecture (example: Intel Pentium)  	:   PowerPC G3

   Operating System (example: Linux 2.4.18) 	:   Mac OS X 10.3.2 (Darwin  
7.2.0)

   PostgreSQL version (example: PostgreSQL-7.4.1):   PostgreSQL-7.4.1

   Compiler used (example:  gcc 2.95.2)		:   gcc 3.3 20030304


Please enter a FULL description of your problem:
------------------------------------------------

postmaster crashes if it tries to call a pl/plgsql function that  
requires a table row as an argument, and there is no row produced in  
the query that can be passed in. There is currently an assertion in the  
code to guard against this case, but it's not an error case, so it  
needs to be handled more gracefully than crashing. :)



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

In order to encounter the situation described above, you have to  
execute a query that calls a pl/pgsql function expecting a table row as  
an argument, but have the query produce no row that can be passed in.  
For example, doing a left join between a patient and dentist table  
where there is no dentist row for a corresponding patient row. And then  
call a pl/pgsql function, passing in the nonexistent dentist row.


CREATE TABLE patient (
    patient_id INTEGER,
    first_name TEXT,
    last_name TEXT,
    dentist_id INTEGER
);

CREATE TABLE dentist (
    dentist_id INTEGER,
    first_name TEXT,
    last_name TEXT
);

CREATE OR REPLACE FUNCTION full_name(dentist) RETURNS text AS '
DECLARE
    d ALIAS FOR $1;
BEGIN
    RETURN d.first_name || '' '' || d.last_name;
END;
' LANGUAGE 'plpgsql';

-- Note: John Smith has no dentist
INSERT INTO patient (patient_id, first_name, last_name) VALUES (1,  
'John', 'Smith');

-- Get a list of patient IDs and dentist names
SELECT p.patient_id, full_name(d) AS dentist_name
FROM   patient p
        LEFT JOIN dentist d ON (p.dentist_id = d.dentist_id);



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Change the assertion protecting against this case in  
src/pl/plpgsql/src/pl_exec.c to an if statement, so that the row  
argument is only copied into the function's arguments if the row  
actually exists. Otherwise, a row with no columns is passed in to the  
function, which gets NULLs when it tries to access any of the row's  
columns. I think this is correct behavior -- if there was no row, then  
there should be no values passed into the function.

See the attached file pl_exec.c.patch (against postgresql 7.4.1).


Attachment: pl_exec.c.patch
Description: application/octet-stream (848 bytes)

Responses

pgsql-bugs by date

Next:From: Richard HuxtonDate: 2004-02-17 19:36:06
Subject: Re: memory does not return back
Previous:From: Pavel StehuleDate: 2004-02-17 15:47:57
Subject: Re: plpgsql - variable's names conflict with table field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group