Обсуждение: BUG #14296: weird check constraint clause in pg_constraint
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI5NgpMb2dnZWQgYnk6ICAg ICAgICAgIFJhbGYgV2llYmlja2UKRW1haWwgYWRkcmVzczogICAgICByYWxm QHJ3Ny5kZQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS40Ck9wZXJhdGluZyBz eXN0ZW06ICAgTGludXgKRGVzY3JpcHRpb246ICAgICAgICAKCkkgY3JlYXRl ZCB0d28gdGFibGVzDQoNCkNSRUFURSBUYWJsZSB0YWJnb29kICggY29sIGlu dGVnZXIsIENPTlNUUkFJTlQgY2hlY2tlciBDSEVDSyAoIGNvbD4wICkpDQpD UkVBVEUgVGFibGUgdGFiYmFkICAoIGNvbCBpbnRlZ2VyLCBDT05TVFJBSU5U IGNoZWNrZXIgQ0hFQ0sgKCBjb2w+LTEgKSkNCg0KYW5kIGZldGNoIHRoZSBj aGVjayBjb25zdHJhaW50cyBmcm9tIHBnX2NvbnN0cmFpbnQNCg0KU0VMRUNU IHV0LnJlbG5hbWUsdWMuY29ubmFtZSx1Yy5jb25zcmMNCkZST00gcGdfY29u c3RyYWludCB1YyBJTk5FUiBKT0lOIHBnX2NsYXNzIHV0IE9OIHVjLmNvbnJl bGlkPXV0Lm9pZCANCldIRVJFIHVjLmNvbnR5cGU9J2MnDQoNCnRoZSBJIGdl dDoNCg0KInRhYmdvb2QiOyJjaGVja2VyIjsiKGNvbCA+IDApIg0KInRhYmJh ZCI7ImNoZWNrZXIiOyIoY29sID4gJy0xJzo6aW50ZWdlcikiDQoNClRoZSBz ZWNvbmQgY2xhdXNlIGlzIHdlaXJkLCBhbHRob3VnaCB3b3JraW5nLiBpdCBz aG91bGQgYmUganVzdCAiY29sID4KLTEiLg0KDQpUaGlzIHByb2JsZW0gaXMg b2JzZXJ2ZWQgc2VlbiBvbiBQb3N0Z3Jlc1NRTCA5LjUuNCwgaXQgd2FzIG5v dCBvYnNlcnZlZCBvbgo5LjMuMTQuCgo=
>>>>> "ralf" == ralf <ralf@rw7.de> writes:
ralf> I created two tables
ralf> CREATE Table tabgood ( col integer, CONSTRAINT checker CHECK ( col>0 ))
ralf> CREATE Table tabbad ( col integer, CONSTRAINT checker CHECK ( col>-1 ))
ralf> and fetch the check constraints from pg_constraint
ralf> SELECT ut.relname,uc.conname,uc.consrc
As a rule you should never look at consrc, adsrc, etc because they
become stale the instant anything gets renamed. The right way to get the
constraint definition is to use pg_get_constraintdef(oid), or
pg_get_expr(conbin,conrelid) for just the check expression.
ralf> "tabgood";"checker";"(col > 0)"
ralf> "tabbad";"checker";"(col > '-1'::integer)"
ralf> The second clause is weird, although working. it should be just
ralf> "col > -1".
ralf> This problem is observed seen on PostgresSQL 9.5.4, it was not
ralf> observed on 9.3.14.
This is intentional.
Quoth the comments in ruleutils.c:
/*
* INT4 can be printed without any decoration, unless it is
* negative; in that case print it as '-nnn'::integer to ensure
* that the output will re-parse as a constant, not as a constant
* plus operator. In most cases we could get away with printing
* (-nnn) instead, because of the way that gram.y handles negative
* literals; but that doesn't work for INT_MIN, and it doesn't
* seem that much prettier anyway.
*/
And the accompanying commit message: (from 542320c2b)
Be more careful about printing constants in ruleutils.c.
The previous coding in get_const_expr() tried to avoid quoting integer,
float, and numeric literals if at all possible. While that looks nice,
it means that dumped expressions might re-parse to something that's
semantically equivalent but not the exact same parsetree; for example
a FLOAT8 constant would re-parse as a NUMERIC constant with a cast to
FLOAT8. Though the result would be the same after constant-folding,
this is problematic in certain contexts. In particular, Jeff Davis
pointed out that this could cause unexpected failures in ALTER INHERIT
operations because of child tables having not-exactly-equivalent CHECK
expressions. Therefore, favor correctness over legibility and dump
such constants in quotes except in the limited cases where they'll
be interpreted as the same type even without any casting.
This results in assorted small changes in the regression test outputs,
and will affect display of user-defined views and rules similarly.
The odds of that causing problems in the field seem non-negligible;
given the lack of previous complaints, it seems best not to change
this in the back branches.
So the objective is not to preserve the original input, but to return
text that will be parsed to produce the same expression tree that was
stored.
--
Andrew (irc:RhodiumToad)
Hi Andrew!
Thanks for your help.
I'd like to verify, whether a given database complies with the schema
required by the application. And I don't want do change the check
constraints required by the application into postgresql' canonical
version. First, because this is awkward to read, and second because it
is not portable to other databases such as MySQL.
So I helped myself with a number of regular expressions, translating the
clause as reported by postgresql into the clause as required by the
application.
For postgresql 9.5 I ended up with
/\((\d*)\)::bigint\b/$1/
/\('(-?\d*(?:\.\d*)?)'::numeric\)::double precision\b/$1/
/'(-?\d*)'::(?:integer|bigint)\b/$1/
/\((\d*(?:\.\d*)?)\)::double precision\b/$1/
/\(\((-\d*\.\d*)\)\)::double precision\b/$1/
/('.*?')::character varying\b/$1/
/('.*?')::"text"/$1/
/\(("\w*")\)::"text"/$1/
/ = ANY \(ARRAY\[(.*?)]\)/ IN ($1)/
/ = ANY \(\(ARRAY\[(.*?)]\)::"text"\[\]\)/ IN ($1)/
/ <> ALL \(\(ARRAY\[(.*?)]\)::"text"\[\]\)/ NOT IN ($1)/
/ (=|<>|>=|<=|>|<) /$1/
The third line is for the problem discussed here.
Again, thanks for your help.
Best regards,
Ralf.
Am 26.08.2016 um 21:00 schrieb Andrew Gierth:
>
> ralf> I created two tables
>
> ralf> CREATE Table tabgood ( col integer, CONSTRAINT checker CHECK ( col>0 ))
> ralf> CREATE Table tabbad ( col integer, CONSTRAINT checker CHECK ( col>-1 ))
>
> ralf> and fetch the check constraints from pg_constraint
>
> ralf> SELECT ut.relname,uc.conname,uc.consrc
>
> As a rule you should never look at consrc, adsrc, etc because they
> become stale the instant anything gets renamed. The right way to get the
> constraint definition is to use pg_get_constraintdef(oid), or
> pg_get_expr(conbin,conrelid) for just the check expression.
>
> ralf> "tabgood";"checker";"(col > 0)"
> ralf> "tabbad";"checker";"(col > '-1'::integer)"
>
> ralf> The second clause is weird, although working. it should be just
> ralf> "col > -1".
>
> ralf> This problem is observed seen on PostgresSQL 9.5.4, it was not
> ralf> observed on 9.3.14.
>
> This is intentional.
>
> Quoth the comments in ruleutils.c:
>
> /*
> * INT4 can be printed without any decoration, unless it is
> * negative; in that case print it as '-nnn'::integer to ensure
> * that the output will re-parse as a constant, not as a constant
> * plus operator. In most cases we could get away with printing
> * (-nnn) instead, because of the way that gram.y handles negative
> * literals; but that doesn't work for INT_MIN, and it doesn't
> * seem that much prettier anyway.
> */
>
> And the accompanying commit message: (from 542320c2b)
>
> Be more careful about printing constants in ruleutils.c.
>
> The previous coding in get_const_expr() tried to avoid quoting integer,
> float, and numeric literals if at all possible. While that looks nice,
> it means that dumped expressions might re-parse to something that's
> semantically equivalent but not the exact same parsetree; for example
> a FLOAT8 constant would re-parse as a NUMERIC constant with a cast to
> FLOAT8. Though the result would be the same after constant-folding,
> this is problematic in certain contexts. In particular, Jeff Davis
> pointed out that this could cause unexpected failures in ALTER INHERIT
> operations because of child tables having not-exactly-equivalent CHECK
> expressions. Therefore, favor correctness over legibility and dump
> such constants in quotes except in the limited cases where they'll
> be interpreted as the same type even without any casting.
>
> This results in assorted small changes in the regression test outputs,
> and will affect display of user-defined views and rules similarly.
> The odds of that causing problems in the field seem non-negligible;
> given the lack of previous complaints, it seems best not to change
> this in the back branches.
>
> So the objective is not to preserve the original input, but to return
> text that will be parsed to produce the same expression tree that was
> stored.
>