Thread

1. bug report

   Martin Renters <martin@datafax.com> — 2000-04-27T16:04:11Z

   ============================================================================
                           POSTGRESQL BUG REPORT TEMPLATE
   ============================================================================
   
   
   Your name		:	Martin Renters
   Your email address	:	martin@datafax.com
   
   
   System Configuration
   ---------------------
     Architecture (example: Intel Pentium)  	: Intel Pentium
   
     Operating System (example: Linux 2.0.26 ELF) 	: Solaris X86 2.6
   
     PostgreSQL version (example: PostgreSQL-6.5.1):   PostgreSQL-7.0.0 snapshot 2000/04/27
   
     Compiler used (example:  gcc 2.8.0)		: gcc 2.7.2.3
   
   
   Please enter a FULL description of your problem:
   ------------------------------------------------
   
   Backend crash with attached script
   
   The original issue that triggered this was an attempt to implement field
   auditing in 7.0beta3.  It worked fine as long as the fields were not NULL.
   I've simplified the code as much as possible to only print values
   rather than writing them into an audit table.
   
   In particular, in the trigger the values printed for old and new are
   correct if the field being updated is non NULL.  If either the old or new
   value for the field is NULL then the values printed in the notice are
   wrong.
   
   i.e.
   
   	original 	new		log
   	1		2		old=1, new=2
   	NULL		3		old=<NULL>, new=<NULL>
   	4		NULL		old=<NULL>, new=<NULL>
   
   The actual value stored as a result of the update is correct, only the
   messages printed are wrong (as are any calculations done inside the
   function).
   
   
   
   Please describe a way to repeat the problem.   Please try to provide a
   concise reproducible example, if at all possible: 
   ----------------------------------------------------------------------
   
   DROP FUNCTION CMP_INT(INTEGER, INTEGER);
   CREATE FUNCTION CMP_INT(INTEGER, INTEGER)
     RETURNS BOOL AS '
   BEGIN
   	RAISE NOTICE ''cmp_int:old value=%, new value=%'', $1, $2;
   	return ''t'';
   END;
   ' LANGUAGE 'plpgsql';
   
   DROP TABLE TEST;
   CREATE TABLE TEST (
   	DF_RECID	INTEGER,
   	DF_MODID	INTEGER
   );
   
   DROP FUNCTION TEST_AUDIT_PROC();
   CREATE FUNCTION TEST_AUDIT_PROC() RETURNS OPAQUE AS '
   BEGIN
   	RAISE NOTICE ''test_audit_proc():old=%, new=%'', OLD.DF_RECID,
   		NEW.DF_RECID;
   	PERFORM CMP_INT(OLD.DF_RECID, NEW.DF_RECID);
   	return new;
   end;
   ' language 'plpgsql';
   CREATE TRIGGER TEST_AUDIT BEFORE UPDATE ON TEST
    FOR EACH ROW EXECUTE PROCEDURE TEST_AUDIT_PROC();
   
   
   
   
   
   INSERT INTO TEST VALUES(1,1);
   INSERT INTO TEST VALUES(2,2);
   INSERT INTO TEST VALUES(NULL, 3);
   
   SELECT * FROM TEST;
   
   UPDATE TEST SET DF_RECID=123 WHERE DF_MODID=1;
   
   SELECT * FROM TEST;
   
   UPDATE TEST SET DF_RECID=456 WHERE DF_MODID=3;
   
   
   
   
   If you know how this problem might be fixed, list the solution below:
   ---------------------------------------------------------------------
   
   
   
   

2. Re: bug report

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-28T03:59:38Z

   Martin Renters <martin@datafax.com> writes:
   > Backend crash with attached script
   
   > The original issue that triggered this was an attempt to implement field
   > auditing in 7.0beta3.  It worked fine as long as the fields were not NULL.
   > I've simplified the code as much as possible to only print values
   > rather than writing them into an audit table.
   
   > In particular, in the trigger the values printed for old and new are
   > correct if the field being updated is non NULL.  If either the old or new
   > value for the field is NULL then the values printed in the notice are
   > wrong.
   
   I've fixed the immediate coredump exhibited by your script, which is
   that the plpgsql RAISE statement was careless about checking for a NULL
   value supplied to it.  But I suspect that that was not your original
   problem but was something you tripped over while investigating it.
   
   Most likely, your real problem is this: at present, a called function
   cannot distinguish which of its arguments are NULL --- if any of them
   are NULL, then all appear to be (and the function's result is forced
   to be NULL, too, which might make one wonder why it's executed at all).
   This happens because the function call protocol only provides a single
   isNull flag for the entire call operation.
   
   This problem doesn't arise for triggers, since the data is not passed
   to them as parameters, but it's a big problem for any functions you
   call from the trigger.
   
   Revising the function call protocol is high on our to-do list for 7.1.
   
   			regards, tom lane