pretty_bool in pg_get_constraintdef has no effect since pg >= 9

Started by Nonameabout 6 years ago3 messagesbugs
Jump to latest
#1Noname
eli.mach@mailbox.org

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.

#2Daniel Gustafsson
daniel@yesql.se
In reply to: Noname (#1)
Re: pretty_bool in pg_get_constraintdef has no effect since pg >= 9

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

#3Noname
eli.mach@mailbox.org
In reply to: Daniel Gustafsson (#2)
Re: pretty_bool in pg_get_constraintdef has no effect since pg >= 9

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