From a0e1ab133c2d46521a74e55423ac2ba866682dae Mon Sep 17 00:00:00 2001
From: Bill Finn <bill@angaza.com>
Date: Mon, 26 Aug 2019 21:22:44 +0000
Subject: [PATCH] PGDialect.get_check_constraints: Handle "NOT VALID"

PostgreSQL supports creating "not valid" check constraints that contain
the string " NOT VALID" in their declaration. Here, we update our
regular expression to properly parse check constraints that have yet to
be validated.

Fixes #4824.
---
 lib/sqlalchemy/dialects/postgresql/base.py |  2 +-
 test/dialect/postgresql/test_reflection.py | 19 +++++++++++++++++++
 2 files changed, 20 insertions(+), 1 deletion(-)

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 5aea02cfca..022d9cd809 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -3451,7 +3451,7 @@ class PGDialect(default.DefaultDialect):
         # "CHECK (((a > 1) AND (a < 5)))"
         # "CHECK (((a = 1) OR ((a > 2) AND (a < 5))))"
         def match_cons(src):
-            m = re.match(r"^CHECK *\(\((.+)\)\)$", src)
+            m = re.match(r"^CHECK *\(\((.+)\)\)( NOT VALID)?$", src)
             if not m:
                 util.warn("Could not parse CHECK constraint text: %r" % src)
                 return ""
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index ea72d57105..2149decd62 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -1535,6 +1535,25 @@ class ReflectionTest(fixtures.TestBase):
             ):
                 testing.db.dialect.get_check_constraints(conn, "foo")
 
+    def test_reflect_with_not_valid_check_constraint(self):
+        conn = mock.Mock(
+            execute=lambda *arg, **kw: mock.Mock(
+                fetchall=lambda: [
+                    ("some name", "CHECK ((a IS NOT NULL)) NOT VALID")
+                ]
+            )
+        )
+        with mock.patch.object(
+            testing.db.dialect, "get_table_oid", lambda *arg, **kw: 1
+        ):
+            check_constraints = testing.db.dialect.get_check_constraints(
+                conn, "foo"
+            )
+            eq_(
+                check_constraints,
+                [{u"name": u"some name", u"sqltext": u"a IS NOT NULL"}],
+            )
+
 
 class CustomTypeReflectionTest(fixtures.TestBase):
     class CustomType(object):
-- 
2.47.3