Skip site navigation (1) Skip section navigation (2)

bug report

From: Martin Renters <martin(at)datafax(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: bug report
Date: 2000-04-27 16:04:11
Message-ID: 200004272004.QAA09916@snowbird.datafax.com (view raw or flat)
Thread:
Lists: pgsql-bugs
============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name		:	Martin Renters
Your email address	:	martin(at)datafax(dot)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:
---------------------------------------------------------------------



Responses

pgsql-bugs by date

Next:From: Dan FranklinDate: 2000-04-27 17:26:39
Subject: PostgreSQL 7.0RC1 access control bug with references
Previous:From: Ragnar Hojland EspinosaDate: 2000-04-27 12:21:39
Subject: postgres 7.0 beta 2 segfaulting [linux i386]

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