======================================================================== ====
======================================================================== ====

Your name : Chris Campbell Your email address : [EMAIL PROTECTED]

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

   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: Binary data


- Chris
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to