From f1ca155cea10e32fec3dfe9fdae6674ee81c0ef4 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 1 Oct 2018 16:23:33 -0400 Subject: [PATCH] Add reflection support for Postgresql partitioned tables Added rudimental support for reflection of Postgresql partitioned tables, e.g. that relkind='p' is added to reflection queries that return table information. Fixes: #4237 Change-Id: I66fd10b002e4ed21ea13b13a7e35a85f66bdea75 --- doc/build/changelog/migration_13.rst | 36 +++++++++++++++++ doc/build/changelog/unreleased_13/4237.rst | 11 ++++++ lib/sqlalchemy/dialects/postgresql/base.py | 5 ++- test/dialect/postgresql/test_reflection.py | 45 ++++++++++++++++++++++ 4 files changed, 95 insertions(+), 2 deletions(-) create mode 100644 doc/build/changelog/unreleased_13/4237.rst diff --git a/doc/build/changelog/migration_13.rst b/doc/build/changelog/migration_13.rst index 5000626869..b7e76b9557 100644 --- a/doc/build/changelog/migration_13.rst +++ b/doc/build/changelog/migration_13.rst @@ -409,6 +409,42 @@ Key Behavioral Changes - Core Dialect Improvements and Changes - PostgreSQL ============================================= +.. _change_4237: + +Added basic reflection support for Postgresql paritioned tables +--------------------------------------------------------------- + +SQLAlchemy can render the "PARTITION BY" sequnce within a Postgresql +CREATE TABLE statement using the flag ``postgresql_partition_by``, added in +version 1.2.6. However, the ``'p'`` type was not part of the reflection +queries used until now. + +Given a schema such as:: + + dv = Table( + 'data_values', metadata, + Column('modulus', Integer, nullable=False), + Column('data', String(30)), + postgresql_partition_by='range(modulus)') + + sa.event.listen( + dv, + "after_create", + sa.DDL( + "CREATE TABLE data_values_4_10 PARTITION OF data_values " + "FOR VALUES FROM (4) TO (10)") + ) + +The two table names ``'data_values'`` and ``'data_values_4_10'`` will come +back from :meth:`.Inspector.get_table_names` and additionally the columns +will come back from ``Inspector.get_columns('data_values')`` as well +as ``Inspector.get_columns('data_values_4_10')``. This also extends to the +use of ``Table(..., autoload=True)`` with these tables. + + +:ticket:`4237` + + Dialect Improvements and Changes - MySQL ============================================= diff --git a/doc/build/changelog/unreleased_13/4237.rst b/doc/build/changelog/unreleased_13/4237.rst new file mode 100644 index 0000000000..a023e59943 --- /dev/null +++ b/doc/build/changelog/unreleased_13/4237.rst @@ -0,0 +1,11 @@ +.. change:: + :tags: feature, postgresql + :tickets: 4237 + + Added rudimental support for reflection of Postgresql + partitioned tables, e.g. that relkind='p' is added to reflection + queries that return table information. + + .. seealso:: + + :ref:`change_4237` diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index ea2c518709..11fcc41d59 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -2460,7 +2460,8 @@ class PGDialect(default.DefaultDialect): FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE (%s) - AND c.relname = :table_name AND c.relkind in ('r', 'v', 'm', 'f') + AND c.relname = :table_name AND c.relkind in + ('r', 'v', 'm', 'f', 'p') """ % schema_where_clause # Since we're binding to unicode, table_name and schema_name must be # unicode. @@ -2491,7 +2492,7 @@ class PGDialect(default.DefaultDialect): result = connection.execute( sql.text("SELECT c.relname FROM pg_class c " "JOIN pg_namespace n ON n.oid = c.relnamespace " - "WHERE n.nspname = :schema AND c.relkind = 'r'" + "WHERE n.nspname = :schema AND c.relkind in ('r', 'p')" ).columns(relname=sqltypes.Unicode), schema=schema if schema is not None else self.default_schema_name) return [name for name, in result] diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index 70bc26e0b6..b30cb506bc 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -74,6 +74,51 @@ class ForeignTableReflectionTest(fixtures.TablesTest, AssertsExecutionResults): eq_(names, ['testtable']) +class PartitionedReflectionTest( + fixtures.TablesTest, AssertsExecutionResults): + # partitioned table reflection, issue #4237 + + __only_on__ = 'postgresql >= 10' + __backend__ = True + + @classmethod + def define_tables(cls, metadata): + # the actual function isn't reflected yet + dv = Table( + 'data_values', metadata, + Column('modulus', Integer, nullable=False), + Column('data', String(30)), + postgresql_partition_by='range(modulus)') + + # looks like this is reflected prior to #4237 + sa.event.listen( + dv, + "after_create", + sa.DDL( + "CREATE TABLE data_values_4_10 PARTITION OF data_values " + "FOR VALUES FROM (4) TO (10)") + ) + + def test_get_tablenames(self): + assert {'data_values', 'data_values_4_10'}.issubset( + inspect(testing.db).get_table_names() + ) + + def test_reflect_cols(self): + cols = inspect(testing.db).get_columns('data_values') + eq_( + [c['name'] for c in cols], + ['modulus', 'data'] + ) + + def test_reflect_cols_from_partition(self): + cols = inspect(testing.db).get_columns('data_values_4_10') + eq_( + [c['name'] for c in cols], + ['modulus', 'data'] + ) + + class MaterializedViewReflectionTest( fixtures.TablesTest, AssertsExecutionResults): """Test reflection on materialized views""" -- 2.47.2