From b23b6db14ac33a792520a5036af1ab02157b7df6 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Mon, 3 Mar 2025 23:35:48 +0100 Subject: [PATCH] Improve identity column reflection Add SQL typing to reflection query used to retrieve a the structure of IDENTITY columns, adding explicit JSON typing to the query to suit unusual PostgreSQL driver configurations that don't support JSON natively. Fixed issue affecting PostgreSQL 17.3 and greater where reflection of domains with "NOT NULL" as part of their definition would include an invalid constraint entry in the data returned by :meth:`_postgresql.PGInspector.get_domains` corresponding to an additional "NOT NULL" constraint that isn't a CHECK constraint; the existing ``"nullable"`` entry in the dictionary already indicates if the domain includes a "not null" constraint. Note that such domains also cannot be reflected on PostgreSQL 17.0 through 17.2 due to a bug on the PostgreSQL side; if encountering errors in reflection of domains which include NOT NULL, upgrade to PostgreSQL server 17.3 or greater. Fixes: #11751 Change-Id: I8e69de51601dca3257186e38c6f699fbfd9014c6 --- doc/build/changelog/unreleased_20/11751.rst | 21 +++++++++++++++++++++ lib/sqlalchemy/dialects/postgresql/base.py | 10 ++++++---- test/dialect/postgresql/test_reflection.py | 2 +- 3 files changed, 28 insertions(+), 5 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/11751.rst diff --git a/doc/build/changelog/unreleased_20/11751.rst b/doc/build/changelog/unreleased_20/11751.rst new file mode 100644 index 0000000000..3686f4fbe9 --- /dev/null +++ b/doc/build/changelog/unreleased_20/11751.rst @@ -0,0 +1,21 @@ +.. change:: + :tags: bug, postgresql + :tickets: 11751 + + Add SQL typing to reflection query used to retrieve a the structure + of IDENTITY columns, adding explicit JSON typing to the query to suit + unusual PostgreSQL driver configurations that don't support JSON natively. + +.. change:: + :tags: bug, postgresql + + Fixed issue affecting PostgreSQL 17.3 and greater where reflection of + domains with "NOT NULL" as part of their definition would include an + invalid constraint entry in the data returned by + :meth:`_postgresql.PGInspector.get_domains` corresponding to an additional + "NOT NULL" constraint that isn't a CHECK constraint; the existing + ``"nullable"`` entry in the dictionary already indicates if the domain + includes a "not null" constraint. Note that such domains also cannot be + reflected on PostgreSQL 17.0 through 17.2 due to a bug on the PostgreSQL + side; if encountering errors in reflection of domains which include NOT + NULL, upgrade to PostgreSQL server 17.3 or greater. diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 83bd99d7f0..133052ff0b 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -3601,6 +3601,7 @@ class PGDialect(default.DefaultDialect): pg_catalog.pg_sequence.c.seqcache, "cycle", pg_catalog.pg_sequence.c.seqcycle, + type_=sqltypes.JSON(), ) ) .select_from(pg_catalog.pg_sequence) @@ -5010,11 +5011,12 @@ class PGDialect(default.DefaultDialect): key=lambda t: t[0], ) for name, def_ in sorted_constraints: - # constraint is in the form "CHECK (expression)". + # constraint is in the form "CHECK (expression)" + # or "NOT NULL". Ignore the "NOT NULL" and # remove "CHECK (" and the tailing ")". - check = def_[7:-1] - constraints.append({"name": name, "check": check}) - + if def_.casefold().startswith("check"): + check = def_[7:-1] + constraints.append({"name": name, "check": check}) domain_rec: ReflectedDomain = { "name": domain["name"], "schema": domain["schema"], diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index 510c8aa33c..4d889c6775 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -432,7 +432,7 @@ class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults): @testing.fixture def testtable(self, connection, testdomain): connection.exec_driver_sql( - "CREATE TABLE testtable (question integer, answer " "testdomain)" + "CREATE TABLE testtable (question integer, answer testdomain)" ) yield connection.exec_driver_sql("DROP TABLE testtable") -- 2.47.2