BUG #13941: Different value "pg_constraint.consrc" for similar Check Constrait
The following bug has been logged on the website:
Bug reference: 13941
Logged by: Sergiy Gavrylenko
Email address: gsv371@ukr.net
PostgreSQL version: 9.5.0
Operating system: Windows Vista
Description:
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!
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 2016-02-10 10:53:28 +0000, gsv371@ukr.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?
Regards,
Andres
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, Feb 10, 2016 at 9:47 AM, Andres Freund <andres@anarazel.de> wrote:
On 2016-02-10 10:53:28 +0000, gsv371@ukr.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.
"""
David J.
On Wed, Feb 10, 2016 at 9:52 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Wed, Feb 10, 2016 at 9:47 AM, Andres Freund <andres@anarazel.de> wrote:
On 2016-02-10 10:53:28 +0000, gsv371@ukr.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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Wed, Feb 10, 2016 at 9:47 AM, Andres Freund <andres@anarazel.de> wrote:
Why do you consider that a bug?
Probably because of this 9.5 release note item:
Adjust operator precedence to match the SQL standard (Tom Lane)
No, I think the difference is actually because of 2146f1340 (Avoid
recursion when processing simple lists of AND'ed or OR'ed clauses).
Before 9.5 you got nested BoolExprs out of either the BETWEEN ... AND
or the AND ... AND formulations. As of 2146f1340, the grammar produces a
single 3-argument BoolExpr for AND ... AND. I'm not sure offhand whether
it also produced that for BETWEEN ... AND at the time, but in any case it
would not have done so after 34af082f9 (Represent BETWEEN as a special
node type in raw parse trees).
Whether you have nested BoolExprs or a pre-flattened one has exactly zero
impact on semantics or execution behavior, since the planner will flatten
the nested case anyway; but you can tell the difference in ruleutils.c
output by counting the parens.
So the BETWEEN case hasn't changed, the AND ... AND case has, and I
entirely concur with Andres that this is not a bug.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
От кого: "David G. Johnston" <david.g.johnston@gmail.com>
Дата: 10 февраля 2016, 19:01:01
On Wed, Feb 10, 2016 at 9:52 AM, David G. Johnston < david.g.johnston@gmail.com > wrote:
On Wed, Feb 10, 2016 at 9:47 AM, Andres Freund < andres@anarazel.de > wrote:
On 2016-02-10 10:53:28 +0000, gsv371@ukr.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.
От кого: "Andres Freund" <andres@anarazel.de>
Дата: 11 февраля 2016, 01:56:06
On 2016-02-10 10:53:28 +0000, gsv371@ukr.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?
Regards,
Andres
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.
On Wed, Feb 10, 2016 at 5:07 PM, Сергей Гавриленко <gsv371@ukr.net> wrote:
От кого: "David G. Johnston" <david.g.johnston@gmail.com>
Дата: 10 февраля 2016, 19:01:01On Wed, Feb 10, 2016 at 9:52 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:On Wed, Feb 10, 2016 at 9:47 AM, Andres Freund <andres@anarazel.de> wrote:
On 2016-02-10 10:53:28 +0000, gsv371@ukr.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 (snapshot) 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.
What are you looking for from us?
The best I can get from the above is you dislike the fact that when the
system dumps a definition is doesn't match the code that was used during
its original creation. If that is the case I'm not sure what to say...the
system parses and then acts upon the original query to update itself and
then throws away the original. If asked it can faithfully construct code
that will have the equivalent effect.
David J.
On 2/10/2016 5:36 PM, David G. Johnston wrote:
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 (snapshot)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 constraitwhich is functionally
equivalent to the reference, but differs from the description of the
reference.
And this in turn causes a re-creation check constraitfor each of the
subsequent verification of the production database structure.
we follow a decidedly different and IMHO simpler process.
1) data base designer creates a .SQL file that generates schema version
1. this is checked into source code control along with the application
components, and is used to initialize a new database.
2) database designer/developer creates a delta .SQL file that will
update X to X+1, and a .SQL file that will create X+1 from scratch.
these are both checked into source code control.
production either runs the delta update file, or the current full schema
file as appropriate. we keep a row in our 'settings table with
setting='schema_version', value='X' (for version X), so automated
installation scripts can apply the correct updates in order.
this process lets us update 'static data' as well as schema structure
(for some databases, the static data has its own versioning, and
seperate set of .sql scripts)
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--- Исходное сообщение ---
От кого: "David G. Johnston" < david.g.johnston@gmail.com >
Дата: 11 февраля 2016, 03:36:07 On Wed, Feb 10, 2016 at 5:07 PM, Сергей Гавриленко < gsv371@ukr.net > wrote:
От кого: "David G. Johnston" < david.g.johnston@gmail.com >
Дата: 10 февраля 2016, 19:01:01
On Wed, Feb 10, 2016 at 9:52 AM, David G. Johnston < david.g.johnston@gmail.com > wrote:
On Wed, Feb 10, 2016 at 9:47 AM, Andres Freund < andres@anarazel.de > wrote:
On 2016-02-10 10:53:28 +0000, gsv371@ukr.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.
What are you looking for from us?
Лучшее, что я могу получить от выше вам не нравится тот факт, что, когда система сбрасывает определение которое не совпадает с кодом, который был использован в ходе его первоначального творения. Если дело обстоит именно так я не уверен, что сказать ... система анализирует, а затем действует на исходном запросе обновить себя, а затем выбрасывает оригинал. Если спросить его можно добросовестно построить код, который будет иметь эквивалентный эффект.
David J.
You are not quite right, I do not like the fact that I cant recreate Database Components, defined in another database.
Ne matter what code was used for the initial creation of these elements!
In general terms, so:
1. if , add constrait Name check (A) ==> 'B'
why, add constrait Name check (B) =/=> 'B', as in 8.4 - 9.4?
2. if for reasons of the standard optimization and harmonization implemented:
add constrait Name check (A) ==> 'B'
add constrait Name check (B) ==> 'C'
add constrait Name check (C) ==> 'C'
why not implemented: add constrait Name check (A) ==> 'C'.?
Thank's.
Sergiy Gavrylenko.