From 0e46359cb00b453448e37ec16fce744f73c98581 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 12 Jul 2021 18:19:08 -0400 Subject: [PATCH] Extract format_constraint truncation rules to ON CONFLICT Fixed issue where a too-long constraint name rendered as part of the "ON CONFLICT ON CONSTRAINT" element of the :class:`_postgresql.Insert` construct due to naming convention generation would not correctly truncate the name in the same way that it normally renders within a CREATE TABLE statement, thus producing a non-matching and too-long constraint name. Fixes: #6755 Change-Id: Ib27014a5ecbc9cd5861a396f8bb49fbc60bf49fe --- doc/build/changelog/unreleased_14/6755.rst | 9 +++++ lib/sqlalchemy/dialects/postgresql/base.py | 12 +++++- lib/sqlalchemy/sql/compiler.py | 47 ++++++++++++++++------ test/dialect/postgresql/test_compiler.py | 26 ++++++++++++ 4 files changed, 79 insertions(+), 15 deletions(-) create mode 100644 doc/build/changelog/unreleased_14/6755.rst diff --git a/doc/build/changelog/unreleased_14/6755.rst b/doc/build/changelog/unreleased_14/6755.rst new file mode 100644 index 0000000000..958ab0d5f7 --- /dev/null +++ b/doc/build/changelog/unreleased_14/6755.rst @@ -0,0 +1,9 @@ +.. change:: + :tags: bug, postgresql + :tickets: 6755 + + Fixed issue where a too-long constraint name rendered as part of the "ON + CONFLICT ON CONSTRAINT" element of the :class:`_postgresql.Insert` + construct due to naming convention generation would not correctly truncate + the name in the same way that it normally renders within a CREATE TABLE + statement, thus producing a non-matching and too-long constraint name. diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ea2eda902f..48fc4fd711 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2322,8 +2322,16 @@ class PGCompiler(compiler.SQLCompiler): def _on_conflict_target(self, clause, **kw): if clause.constraint_target is not None: - target_text = "ON CONSTRAINT %s" % self.preparer.quote( - clause.constraint_target + # target may be a name of an Index, UniqueConstraint or + # ExcludeConstraint. While there is a separate + # "max_identifier_length" for indexes, PostgreSQL uses the same + # length for all objects so we can use + # truncate_and_render_constraint_name + target_text = ( + "ON CONSTRAINT %s" + % self.preparer.truncate_and_render_constraint_name( + clause.constraint_target + ) ) elif clause.inferred_target_elements is not None: target_text = "(%s)" % ", ".join( diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 4b3b2c293c..9ecd68412b 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -4961,20 +4961,41 @@ class IdentifierPreparer(object): else: name = constraint.name + if constraint.__visit_name__ == "index": + return self.truncate_and_render_index_name( + name, _alembic_quote=_alembic_quote + ) + else: + return self.truncate_and_render_constraint_name( + name, _alembic_quote=_alembic_quote + ) + + def truncate_and_render_index_name(self, name, _alembic_quote=True): + # calculate these at format time so that ad-hoc changes + # to dialect.max_identifier_length etc. can be reflected + # as IdentifierPreparer is long lived + max_ = ( + self.dialect.max_index_name_length + or self.dialect.max_identifier_length + ) + return self._truncate_and_render_maxlen_name( + name, max_, _alembic_quote + ) + + def truncate_and_render_constraint_name(self, name, _alembic_quote=True): + # calculate these at format time so that ad-hoc changes + # to dialect.max_identifier_length etc. can be reflected + # as IdentifierPreparer is long lived + max_ = ( + self.dialect.max_constraint_name_length + or self.dialect.max_identifier_length + ) + return self._truncate_and_render_maxlen_name( + name, max_, _alembic_quote + ) + + def _truncate_and_render_maxlen_name(self, name, max_, _alembic_quote): if isinstance(name, elements._truncated_label): - # calculate these at format time so that ad-hoc changes - # to dialect.max_identifier_length etc. can be reflected - # as IdentifierPreparer is long lived - if constraint.__visit_name__ == "index": - max_ = ( - self.dialect.max_index_name_length - or self.dialect.max_identifier_length - ) - else: - max_ = ( - self.dialect.max_constraint_name_length - or self.dialect.max_identifier_length - ) if len(name) > max_: name = name[0 : max_ - 8] + "_" + util.md5_hex(name)[-4:] else: diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index e48de9d21d..73b3051f9d 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -2351,6 +2351,32 @@ class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL): "DO NOTHING", ) + def test_do_nothing_super_long_name_constraint_target(self): + """test #6755""" + + m = MetaData( + naming_convention={"uq": "%(table_name)s_%(column_0_N_name)s_key"} + ) + + uq = UniqueConstraint("some_column_name_thats_really_really_long_too") + Table( + "some_table_name_thats_really_really", + m, + Column("some_column_name_thats_really_really_long_too", Integer), + uq, + ) + + i = insert(self.table1, values=dict(name="foo")) + + i = i.on_conflict_do_nothing(constraint=uq) + self.assert_compile( + i, + "INSERT INTO mytable (name) VALUES (%(name)s) ON CONFLICT " + "ON CONSTRAINT " + "some_table_name_thats_really_really_some_column_name_th_f7ab " + "DO NOTHING", + ) + def test_do_nothing_quoted_named_constraint_target(self): """test #6696""" i = insert(self.table1, values=dict(name="foo")) -- 2.47.2