============================================================================
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:
---------------------------------------------------------------------
