--Apple-Mail-5--209606438
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

================================================== ======================
====
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 (diffed against postgresql 7.4.1).


--Apple-Mail-5--209606438
Content-Transfer-Encoding: 7bit
Content-Type: application/octet-stream;
x-unix-mode=0644;
name="pl_exec.c.patch"
Content-Disposition: attachment;
filename=pl_exec.c.patch

*** pl_exec.c.orig Wed Oct 1 17:47:42 2003
--- pl_exec.c Sun Feb 15 16:28:09 2004
***************
*** 262,271 ****
HeapTuple tup;
TupleDesc tupdesc;

! Assert(slot != NULL && !fcinfo->argnull[i]);
! tup = slot->val;
! tupdesc = slot->ttc_tupleDescriptor;
! exec_move_row(&estate, NULL, row, tup, tupdesc);
}
break;

--- 262,273 ----
HeapTuple tup;
TupleDesc tupdesc;

! if (slot != NULL && !fcinfo->argnull[i])
! {
! tup = slot->val;
! tupdesc = slot->ttc_tupleDescriptor;
! exec_move_row(&estate, NULL, row, tup, tupdesc);
! }
}
break;


--Apple-Mail-5--209606438
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
format=flowed



Thanks!

- Chris

--Apple-Mail-5--209606438
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--Apple-Mail-5--209606438--

Chris Campbell <chris (AT) bignerdranch (DOT) com> writes:
I'm actually a bit surprised that there's no crash further upstream,
as the executor's code for handling whole-tuple variables is pretty
messy. But yes, this needs to be fixed.

I think you need to do something to explicitly set up the row value
with NULLs. Fortunately, exec_move_row will do that if you just pass
it a NULL tuple, so the fix as applied looks like

*** src/pl/plpgsql/src/pl_exec.c.orig Tue Feb 3 12:34:04 2004
--- src/pl/plpgsql/src/pl_exec.c Mon Feb 23 20:36:06 2004
***************
*** 262,271 ****
HeapTuple tup;
TupleDesc tupdesc;

! Assert(slot != NULL && !fcinfo->argnull[i]);
! tup = slot->val;
! tupdesc = slot->ttc_tupleDescriptor;
! exec_move_row(&estate, NULL, row, tup, tupdesc);
}
break;

--- 262,279 ----
HeapTuple tup;
TupleDesc tupdesc;

! if (!fcinfo->argnull[i])
! {
! Assert(slot != NULL);
! tup = slot->val;
! tupdesc = slot->ttc_tupleDescriptor;
! exec_move_row(&estate, NULL, row, tup, tupdesc);
! }
! else
! {
! /* If arg is null, treat it as an empty row */
! exec_move_row(&estate, NULL, row, NULL, NULL);
! }
}
break;


Thanks for the report!

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html