Обсуждение: 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.conrelidIN (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 constrainttext" 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 constrainttext" 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 thatcan be easily fixed by adding a "re.DOTALL" in the SA regex. I open an SA-Issue. Thanks for your help. Greetings, elim. > 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 constrainttext" 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