pretty_bool in pg_get_constraintdef has no effect since pg >= 9
Hello,
since postgres 9, `pg_get_constraintdef(cons.oid, pretty_bool)` does not work as expected. The result is always in "pretty"-format (with newlines), regardless of whether `pretty_bool` is true or false. Calling `pg_get_constraintdef(constraint_oid)` without `pretty_bool`, also returns "pretty"-format.
Test with:
```
CREATE TABLE tbl (
id int NOT NULL DEFAULT 0
,CHECK
(
CASE
WHEN id < 0 THEN false
WHEN id >= 0 THEN true
END
)
);
SELECT cons.conname as name, pg_get_constraintdef(cons.oid, false) as src FROM pg_catalog.pg_constraint cons WHERE cons.conrelid IN (SELECT oid from pg_class WHERE relname='tbl') AND cons.contype = 'c';
```
Result on postgres 8:
```
CHECK (CASE WHEN (id < 0) THEN false WHEN (id >= 0) THEN true ELSE NULL::boolean END)
```
Result on postgres >= 9:
```
CHECK (
CASE
WHEN (id < 0) THEN false
WHEN (id >= 0) THEN true
ELSE NULL::boolean
END)
```
I'm migrating from postgres 8 to 11 and a sqlalchemy script throws a warning "SAWarning: Could not parse CHECK constraint text" because there a no newlines expected in `re.match(r"^CHECK *\((.+)\)( NOT VALID)?$", src)`.
Greetings,
elim.
On 25 Feb 2020, at 13:56, eli.mach@mailbox.org wrote:
since postgres 9, `pg_get_constraintdef(cons.oid, pretty_bool)` does not work as expected. The result is always in "pretty"-format (with newlines), regardless of whether `pretty_bool` is true or false. Calling `pg_get_constraintdef(constraint_oid)` without `pretty_bool`, also returns "pretty"-format.
This is not a bug, but a deliberate change which was made in 62e666400d back in
2013, the argument being that changes in whitespace should not affect forward
compatibility.
I'm migrating from postgres 8 to 11 and a sqlalchemy script throws a warning "SAWarning: Could not parse CHECK constraint text" because there a no newlines expected in `re.match(r"^CHECK *\((.+)\)( NOT VALID)?$", src)`.
Surely SA has been updated to work with more recent version of postgres? I've
not used SA myself, but are you sure you are using the right version of the
tool?
cheers ./daniel
The SA-Version I use is the newest version 1.3.13. Seems that the SA regex never had to deal with newlines. I think that can be easily fixed by adding a "re.DOTALL" in the SA regex. I open an SA-Issue. Thanks for your help.
Greetings,
elim.
Show quoted text
On 25 February 2020 14:30 Daniel Gustafsson <daniel@yesql.se> wrote:
On 25 Feb 2020, at 13:56, eli.mach@mailbox.org wrote:
since postgres 9, `pg_get_constraintdef(cons.oid, pretty_bool)` does not work as expected. The result is always in "pretty"-format (with newlines), regardless of whether `pretty_bool` is true or false. Calling `pg_get_constraintdef(constraint_oid)` without `pretty_bool`, also returns "pretty"-format.
This is not a bug, but a deliberate change which was made in 62e666400d back in
2013, the argument being that changes in whitespace should not affect forward
compatibility.I'm migrating from postgres 8 to 11 and a sqlalchemy script throws a warning "SAWarning: Could not parse CHECK constraint text" because there a no newlines expected in `re.match(r"^CHECK *\((.+)\)( NOT VALID)?$", src)`.
Surely SA has been updated to work with more recent version of postgres? I've
not used SA myself, but are you sure you are using the right version of the
tool?cheers ./daniel