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, pgsql-patches(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-15 21:50:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugspgsql-patches
                         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  

   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

    d ALIAS FOR $1;
    RETURN d.first_name || '' '' || d.last_name;
' 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 (diffed against postgresql 7.4.1).

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

pgsql-bugs by date

Next:From: Harry HochheiserDate: 2004-02-16 15:38:01
Subject: Re: Default Timestamp 'Now' bug with 7.4 on Panther.
Previous:From: Bruno Wolff IIIDate: 2004-02-15 19:55:54
Subject: Re: Problem with datatype REAL using the = (EQUAL) operator

pgsql-patches by date

Next:From: Tom LaneDate: 2004-02-15 22:17:18
Subject: Re: [PATCHES] dollar quoting
Previous:From: Andrew DunstanDate: 2004-02-15 20:55:36
Subject: Re: [PATCHES] dollar quoting

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