From: | Jean-Pierre Pelletier <jppelletier(at)e-djuster(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Number of parentheses in check constraints affected by operator_precedence_warning |
Date: | 2016-06-03 22:00:36 |
Message-ID: | fa0535ec6d6428cfec40c7e8a6d11156@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
We noticed on PostgreSQL 9.5.3 that the number of parentheses in check
constraints expressions
vary depending on the setting of operator_precedence_warning.
-- The following reproduces this issue which we first saw by restoring
into 9.5.3 a 9.4.8 dump
on two servers which had different operator_precedence_warning.
SET operator_precedence_warning = off; -- on would yield a different
number of parentheses
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 INT);
-- 1) Check Constraints
ALTER TABLE t1
ADD CONSTRAINT cc1 CHECK ((col1 IS NULL) OR (((col2 IS NULL) AND (col3
IS NULL)) AND (col4 IS NULL))),
ADD CONSTRAINT cc2 CHECK (((col1 IS NOT NULL) OR (col2 IS NOT NULL)) OR
(col3 IS NOT NULL));
SELECT conname, consrc FROM pg_constraint WHERE conrelid = 't1'::regclass;
-- With 9.5.3 operator_precedence_warning = on (or pg 9.4)
-- cc1 ((col1 IS NULL) OR (((col2 IS NULL) AND (col3 IS NULL)) AND (col4
IS NULL)))
-- cc2 (((col1 IS NOT NULL) OR (col2 IS NOT NULL)) OR (col3 IS NOT NULL))
-- With 9.5.3 operator_precedence_warning = off
-- cc1 ((col1 IS NULL) OR ((col2 IS NULL) AND (col3 IS NULL) AND (col4 IS
NULL)))
-- cc2 ((col1 IS NOT NULL) OR (col2 IS NOT NULL) OR (col3 IS NOT NULL))
-- 2) WHEN condition on triggers also have this issue, for example:
DROP FUNCTION IF EXISTS f1();
CREATE FUNCTION f1() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
END;
$$;
CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW WHEN (((((new.col1 IS
DISTINCT FROM old.col1) OR (new.col2 IS DISTINCT FROM old.col2)) OR
((new.col3 IS NOT NULL) AND (new.col3 IS DISTINCT FROM old.col3))) OR
((new.col3 IS NULL) AND (new.col3 IS DISTINCT FROM old.col3)))) EXECUTE
PROCEDURE f1();
We got these results on Windows x64.
Thanks,
Jean-Pierre Pelletier
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-06-03 23:13:52 | Re: Number of parentheses in check constraints affected by operator_precedence_warning |
Previous Message | David Steele | 2016-06-03 21:41:35 | Re: [GENERAL] Permission Denied Error on pg_xlog/RECOVERYXLOG file |