Re: BUG #13941: Different value "pg_constraint.consrc" for similar Check Constrait

От кого: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Дата: 10 февраля 2016, 19:01:01

On Wed, Feb 10, 2016 at 9:52 AM, David G. Johnston < david(dot)g(dot)johnston(at)gmail(dot)com > wrote:
On Wed, Feb 10, 2016 at 9:47 AM, Andres Freund < andres(at)anarazel(dot)de > wrote:
On 2016-02-10 10:53:28 +0000, gsv371(at)ukr(dot)net wrote:
> PostgreSQL 9.5:
> 1.
> ALTER TABLE schedule ADD CONSTRAINT schedule_fact_quant_val CHECK
> (fact_quant BETWEEN 0 AND 23.59 AND (fact_quant - trunc(fact_quant)) <
> 0.6);
> result (pg_constraint.consrc): "(((fact_quant >= (0)::numeric) AND
> (fact_quant <= 23.59)) AND ((fact_quant - trunc(fact_quant)) < 0.6))"
> 2.
> ALTER TABLE schedule ADD CONSTRAINT schedule_fact_quant_val CHECK
> (((fact_quant >= (0)::numeric) AND (fact_quant <= 23.59)) AND ((fact_quant -
> trunc(fact_quant)) < 0.6));
> result (pg_constraint.consrc): "((fact_quant >= (0)::numeric) AND
> (fact_quant <= 23.59) AND ((fact_quant - trunc(fact_quant)) < 0.6))"
> ----------------------------------
> PostgreSQL 9.4 - identical results!

Why do you consider that a bug?

​Probably because of this 9.5 release note item:
""" ​  Version 9.5 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:
Adjust operator precedence to match the SQL standard (Tom Lane)
The precedence of <=, >= and <> has been reduced to match that of <, > and =. The precedence of IS tests (e.g., x IS NULL) has been reduced to be just below these six comparison operators. Also, multi-keyword operators beginning with NOT now have the precedence of their base operator (for example, NOT BETWEEN now has the same precedence as BETWEEN) whereas before they had inconsistent precedence, behaving like NOT with respect to their left operand but like their base operator with respect to their right operand. The new configuration parameter operator_precedence_warning can be enabled to warn about queries in which these precedence changes result in different parsing choices. ​"""

​To clarify - the release note probably explains why the 9.4 (with extra parens) and 9.5 (without) constraints behave the same.  It is not a bug in the 9.5 version while it could be considered one in 9.4 that has now been corrected...  In either case it appears to be working as designed but I haven't attempted to figure out exactly ​how the changes apply to this specific expression.
David J.
Hi, just sorry for my english , it's Google translator .
This is the reason the lack of detailed description of the problems discussed in " bug " .
Maybe it's not a bug in the truest sense.
There is a process: In the empty base creates the necessary data structure .
Then, based on the contents of the tables " pg_catalog" obtain and maintain the description of this structure (snapshot).
In the future, comparing the (snapshot) with another (snapshot) received from the production database , a decision about the difference between the structures and the need to transform the production database .

The essence of the problem :
Developer creates check constrait according to [ 1 ] describe the bug .
In reference (s napshot) to store the result of [ 1 ] describe the bug .
The production database is missing the check constraint and the decision of its creation .
Add instruction is generated from the reference (snapshot) and corresponds to [2 ] describe the bug.
After this addition, we obtain check constrait which is functionally equivalent to the reference , but differs from the description of the reference .
And this in turn causes a re-creation check constrait for each of the subsequent verification of the production database structure.

Thank you and best regards .
Sergiy Gavrylenko.