From: | Joel Burton <jburton(at)scw(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Logical expn not shortcircuiting in trigger function? |
Date: | 2001-04-20 14:16:45 |
Message-ID: | Pine.LNX.4.21.0104201015100.31836-100000@olympus.scw.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Problem: PostgreSQL apparently isn't short-circuiting a logical
expression, causing an error when it tries to evaluate OLD in an
INSERT trigger.
PostgreSQL normally 'short-circuits' logical expressions; that is,
once it figures out that it can't resolve an expression to truth,
it stops evaluating all the possibilities.
For example:
CREATE FUNCTION crash() RETURNS boolean AS '
BEGIN
RAISE EXCEPTION ''crash()'';
RETURN TRUE; -- will never get here
END;
' LANGUAGE 'plpgsql';
SELECT 1 WHERE crash();
ERROR: crash()
SELECT 1 WHERE 1=2 AND crash();
?column?
----------
(0 rows)
doesn't crash() because it realizes that, as both 1=2 and crash() must
return true, that it isn't worth checking crash().
However, I have a procedure called by a trigger that is called for
both INSERTs and UPDATEs. For INSERTs, we always want to check a class
capacity. For UPDATEs, we only want to check the capacity if the
registration status has changed:
CREATE FUNCTION reg_chk_capacity() RETURNS opaque AS '
DECLARE
seats int;
BEGIN
IF TG_OP=''INSERT'' OR
(TG_OP=''UPDATE'' AND (OLD.statuscode <> NEW.statuscode))
THEN
seats := Reg_SeatsLeft(NEW.InstID);
IF seats < 1
THEN
RAISE EXCEPTION ''reg_chk_capacity__inst_filled: InstID=%,
RegID=%'', NEW.InstID, NEW.RegID;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
(Reg_SeatsLeft() is a simple SQL function w/o any references to NEW or
OLD)
If I try to INSERT into this table, I get
ERROR: record old is unassigned yet
So, why hasn't the logic short-circuited? Am I missing something?
--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-04-20 14:26:18 | Re: Database Connect |
Previous Message | Vilson farias | 2001-04-20 13:31:29 | Re: very slow execution of stored procedures |