From: | "Dmitry Samokhin" <sdld(at)mail(dot)ru> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4997: Expression evaluation rules |
Date: | 2009-08-20 07:48:12 |
Message-ID: | 200908200748.n7K7mCwD076227@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4997
Logged by: Dmitry Samokhin
Email address: sdld(at)mail(dot)ru
PostgreSQL version: 8.3.7
Operating system: Windows 2003 Server
Description: Expression evaluation rules
Details:
As described in section "4.2.12. Expression Evaluation Rules" in the
documentation, "the order of evaluation of subexpressions is not defined",
and the proposed solution is to use the CASE statement. Consider the
following query:
SELECT CASE WHEN TRUE THEN TRUE ELSE 1 / 0 = 1 END;
It returns 'TRUE' as expected, I don't see the 'division by zero' error, so
the ELSE branch is not evaluated at all.
Then, consider this test case:
-- Start of DDL script
CREATE OR REPLACE FUNCTION trg_mytable_after()
RETURNS trigger AS
$BODY$
BEGIN
IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
IF (CASE WHEN TG_OP = 'DELETE' THEN TRUE ELSE OLD.a <> NEW.a END) THEN
RAISE NOTICE 'OK!';
END IF;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
CREATE TABLE mytable
(
a integer NOT NULL,
CONSTRAINT pk_mytable PRIMARY KEY (a)
)
WITH (
OIDS=FALSE
);
CREATE TRIGGER trg_mytable_after
AFTER INSERT OR UPDATE OR DELETE
ON mytable
FOR EACH ROW
EXECUTE PROCEDURE trg_mytable_after();
-- End of DDL script
INSERT INTO mytable (a) VALUES (1);
DELETE FROM mytable WHERE a = 1;
On DELETE statement, the error occurs:
ERROR record "new" is not assigned yet
DETAIL The tuple structure of a not-yet-assigned record is indeterminate
So it seems although (TG_OP = 'DELETE') is TRUE, the ELSE branch of the CASE
statement (OLD.a <> NEW.a) in the trigger procedure is still evaluated.
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2009-08-20 09:33:15 | Re: BUG #4997: Expression evaluation rules |
Previous Message | Alvaro Herrera | 2009-08-20 03:27:15 | Re: fillfactor hides autovacuum parameters in 8.4.0 |