Crash when calling a pl/pgsql function with no row to pass as an argument - Mailing list 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@bignerdranch.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).



Thanks!

- Chris