From 1dd96d274d78ed6ae57de631e8a326857d1b59fc Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Wed, 18 Jun 2025 16:22:59 -0400 Subject: [PATCH] postgresql dialect table options Support for storage parameters in ``CREATE TABLE`` using the ``WITH`` clause has been added. The ``postgresql_with`` dialect option of :class:`_schema.Table` accepts a mapping of key/value options. The PostgreSQL dialect now support reflection of table options, including the storage parameters, table access method and table spaces. These options are automatically reflected when autoloading a table, and are also available via the :meth:`_engine.Inspector.get_table_options` and :meth:`_engine.Inspector.get_multi_table_optionsmethod` methods. Fixes: #10909 Closes: #12584 Closes: #12684 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12584 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/12684 Pull-request-sha: a660459de9adcedb2d12eac72450747c04792bcd Change-Id: I33e3d8b8eb0c02530933cb124e3d375ca4af7db2 --- doc/build/changelog/unreleased_21/10909.rst | 22 +++ lib/sqlalchemy/dialects/postgresql/base.py | 185 +++++++++++++++++- .../dialects/postgresql/pg_catalog.py | 18 ++ test/dialect/postgresql/test_compiler.py | 37 ++++ test/dialect/postgresql/test_reflection.py | 103 ++++++++++ test/requirements.py | 2 +- 6 files changed, 364 insertions(+), 3 deletions(-) create mode 100644 doc/build/changelog/unreleased_21/10909.rst diff --git a/doc/build/changelog/unreleased_21/10909.rst b/doc/build/changelog/unreleased_21/10909.rst new file mode 100644 index 0000000000..fa5cff031c --- /dev/null +++ b/doc/build/changelog/unreleased_21/10909.rst @@ -0,0 +1,22 @@ +.. change:: + :tags: postgresql, feature + :tickets: 10909 + + Support for storage parameters in ``CREATE TABLE`` using the ``WITH`` + clause has been added. The ``postgresql_with`` dialect option of + :class:`_schema.Table` accepts a mapping of key/value options. + + .. seealso:: + + :ref:`postgresql_table_options_with` - in the PostgreSQL dialect + documentation + +.. change:: + :tags: postgresql, usecase + :tickets: 10909 + + The PostgreSQL dialect now support reflection of table options, including + the storage parameters, table access method and table spaces. These options + are automatically reflected when autoloading a table, and are also + available via the :meth:`_engine.Inspector.get_table_options` and + :meth:`_engine.Inspector.get_multi_table_optionsmethod` methods. diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index a11331a3df..08bff175a1 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1173,8 +1173,8 @@ PostgreSQL Table Options ------------------------ Several options for CREATE TABLE are supported directly by the PostgreSQL -dialect in conjunction with the :class:`_schema.Table` construct, listed in -the following sections. +dialect in conjunction with the :class:`_schema.Table` construct, detailed +in the following sections. .. seealso:: @@ -1243,6 +1243,56 @@ Specifies the table access method to use for storing table data, such as .. versionadded:: 2.0.26 +.. _postgresql_table_options_with: + +``WITH`` +^^^^^^^^ + +:: + + Table("some_table", metadata, ..., postgresql_with={"fillfactor": 100}) + +The ``postgresql_with`` parameter accepts a dictionary of storage parameters +that will be applied to the table using the ``WITH`` clause in the +``CREATE TABLE`` statement. Storage parameters control various aspects of +table behavior such as the fill factor for pages, autovacuum settings, and +toast table parameters. + +When the table is created, the parameters are rendered as a comma-separated +list within the ``WITH`` clause. For example:: + + Table( + "mytable", + metadata, + Column("id", Integer, primary_key=True), + postgresql_with={ + "fillfactor": 70, + "autovacuum_enabled": "false", + "toast.vacuum_truncate": True, + }, + ) + +This will generate DDL similar to: + +.. sourcecode:: sql + + CREATE TABLE mytable ( + id INTEGER NOT NULL, + PRIMARY KEY (id) + ) WITH (fillfactor = 70, autovacuum_enabled = false, toast.vacuum_truncate = True) + +The values in the dictionary can be integers, strings, or boolean values. +Parameter names can include dots to specify parameters for associated objects +like toast tables (e.g., ``toast.vacuum_truncate``). + +.. seealso:: + + `PostgreSQL Storage Parameters + `_ - + documentation on available storage parameters. + +.. versionadded:: 2.1 + ``WITH OIDS`` ^^^^^^^^^^^^^ @@ -1253,6 +1303,8 @@ assigns a unique identifier to each row. Table("some_table", metadata, ..., postgresql_with_oids=True) +.. note:: Support for tables "with OIDs" was removed in postgresql 12. + ``WITHOUT OIDS`` ^^^^^^^^^^^^^^^^ @@ -2662,6 +2714,10 @@ class PGDDLCompiler(compiler.DDLCompiler): if pg_opts["using"]: table_opts.append("\n USING %s" % pg_opts["using"]) + if pg_opts["with"]: + storage_params = (f"{k} = {v}" for k, v in pg_opts["with"].items()) + table_opts.append(f" WITH ({', '.join(storage_params)})") + if pg_opts["with_oids"] is True: table_opts.append("\n WITH OIDS") elif pg_opts["with_oids"] is False: @@ -3293,6 +3349,7 @@ class PGDialect(default.DefaultDialect): "tablespace": None, "partition_by": None, "with_oids": None, + "with": None, "on_commit": None, "inherits": None, "using": None, @@ -3770,6 +3827,130 @@ class PGDialect(default.DefaultDialect): relkinds += pg_catalog.RELKINDS_MAT_VIEW return relkinds + @reflection.cache + def get_table_options(self, connection, table_name, schema=None, **kw): + data = self.get_multi_table_options( + connection, + schema=schema, + filter_names=[table_name], + scope=ObjectScope.ANY, + kind=ObjectKind.ANY, + **kw, + ) + return self._value_or_raise(data, table_name, schema) + + @lru_cache() + def _table_options_query(self, schema, has_filter_names, scope, kind): + inherits_sq = ( + select( + pg_catalog.pg_inherits.c.inhrelid, + sql.func.array_agg( + aggregate_order_by( + pg_catalog.pg_class.c.relname, + pg_catalog.pg_inherits.c.inhseqno, + ) + ).label("parent_table_names"), + ) + .select_from(pg_catalog.pg_inherits) + .join( + pg_catalog.pg_class, + pg_catalog.pg_inherits.c.inhparent + == pg_catalog.pg_class.c.oid, + ) + .group_by(pg_catalog.pg_inherits.c.inhrelid) + .subquery("inherits") + ) + + if self.server_version_info < (12,): + # this is not in the pg_catalog.pg_class since it was + # removed in PostgreSQL version 12 + has_oids = sql.column("relhasoids", BOOLEAN) + else: + has_oids = sql.null().label("relhasoids") + + relkinds = self._kind_to_relkinds(kind) + query = ( + select( + pg_catalog.pg_class.c.oid, + pg_catalog.pg_class.c.relname, + pg_catalog.pg_class.c.reloptions, + has_oids, + sql.case( + ( + sql.and_( + pg_catalog.pg_am.c.amname.is_not(None), + pg_catalog.pg_am.c.amname + != sql.func.current_setting( + "default_table_access_method" + ), + ), + pg_catalog.pg_am.c.amname, + ), + else_=sql.null(), + ).label("access_method_name"), + pg_catalog.pg_tablespace.c.spcname.label("tablespace_name"), + inherits_sq.c.parent_table_names, + ) + .select_from(pg_catalog.pg_class) + .outerjoin( + # NOTE: on postgresql < 12, this could be avoided + # since relam is always 0 so nothing is joined. + pg_catalog.pg_am, + pg_catalog.pg_class.c.relam == pg_catalog.pg_am.c.oid, + ) + .outerjoin( + inherits_sq, + pg_catalog.pg_class.c.oid == inherits_sq.c.inhrelid, + ) + .outerjoin( + pg_catalog.pg_tablespace, + pg_catalog.pg_tablespace.c.oid + == pg_catalog.pg_class.c.reltablespace, + ) + .where(self._pg_class_relkind_condition(relkinds)) + ) + query = self._pg_class_filter_scope_schema(query, schema, scope=scope) + if has_filter_names: + query = query.where( + pg_catalog.pg_class.c.relname.in_(bindparam("filter_names")) + ) + return query + + def get_multi_table_options( + self, connection, schema, filter_names, scope, kind, **kw + ): + has_filter_names, params = self._prepare_filter_names(filter_names) + query = self._table_options_query( + schema, has_filter_names, scope, kind + ) + rows = connection.execute(query, params).mappings() + table_options = {} + + for row in rows: + current: dict[str, Any] = {} + if row["access_method_name"] is not None: + current["postgresql_using"] = row["access_method_name"] + + if row["parent_table_names"]: + current["postgresql_inherits"] = tuple( + row["parent_table_names"] + ) + + if row["reloptions"]: + current["postgresql_with"] = dict( + option.split("=", 1) for option in row["reloptions"] + ) + + if row["relhasoids"]: + current["postgresql_with_oids"] = True + + if row["tablespace_name"] is not None: + current["postgresql_tablespace"] = row["tablespace_name"] + + table_options[(schema, row["relname"])] = current + + return table_options.items() + @reflection.cache def get_columns(self, connection, table_name, schema=None, **kw): data = self.get_multi_columns( diff --git a/lib/sqlalchemy/dialects/postgresql/pg_catalog.py b/lib/sqlalchemy/dialects/postgresql/pg_catalog.py index 9625ccf334..1c3ae1dec2 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg_catalog.py +++ b/lib/sqlalchemy/dialects/postgresql/pg_catalog.py @@ -324,3 +324,21 @@ pg_opclass = Table( Column("opcdefault", Boolean), Column("opckeytype", OID), ) + +pg_inherits = Table( + "pg_inherits", + pg_catalog_meta, + Column("inhrelid", OID), + Column("inhparent", OID), + Column("inhseqno", Integer), + Column("inhdetachpending", Boolean, info={"server_version": (14,)}), +) + +pg_tablespace = Table( + "pg_tablespace", + pg_catalog_meta, + Column("oid", OID), + Column("spcname", NAME), + Column("spcowner", OID), + Column("spcoptions", ARRAY(Text)), +) diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index f91453c3f4..be767b690f 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -584,6 +584,43 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "CREATE TABLE anothertable (id INTEGER) WITHOUT OIDS", ) + def test_create_table_with_storage_parameters(self): + m = MetaData() + + tbl = Table("atable1", m, postgresql_with={"fillfactor": 100}) + + self.assert_compile( + schema.CreateTable(tbl), + "CREATE TABLE atable1 () WITH (fillfactor = 100)", + ) + + tbl2 = Table( + "atable2", + m, + postgresql_with={"toast.autovacuum_insert_scale_factor": 1.25}, + ) + + self.assert_compile( + schema.CreateTable(tbl2), + "CREATE TABLE atable2 () " + "WITH (toast.autovacuum_insert_scale_factor = 1.25)", + ) + + tbl3 = Table( + "atable3", + m, + postgresql_with={ + "user_catalog_table": False, + "parallel_workers": 15, + }, + ) + + self.assert_compile( + schema.CreateTable(tbl3), + "CREATE TABLE atable3 () " + "WITH (user_catalog_table = False, parallel_workers = 15)", + ) + def test_create_table_with_oncommit_option(self): m = MetaData() tbl = Table( diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index a8b933f74d..4dfd0e4fd0 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -2999,3 +2999,106 @@ class TestReflectDifficultColTypes(fixtures.TablesTest): is_true(len(rows) > 0) for row in rows: self.check_int_list(row, "conkey") + + +class TestTableOptionsReflection(fixtures.TestBase): + __only_on__ = "postgresql" + __backend__ = True + + def test_table_inherits(self, metadata, connection): + def assert_inherits_from(table_name, expect_base_tables): + table_options = inspect(connection).get_table_options(table_name) + eq_( + table_options.get("postgresql_inherits", ()), + expect_base_tables, + ) + + def assert_column_names(table_name, expect_columns): + columns = inspect(connection).get_columns(table_name) + eq_([c["name"] for c in columns], expect_columns) + + Table("base", metadata, Column("id", INTEGER, primary_key=True)) + Table("name_mixin", metadata, Column("name", String(16))) + Table("single_inherits", metadata, postgresql_inherits="base") + Table( + "single_inherits_tuple_arg", + metadata, + postgresql_inherits=("base",), + ) + Table( + "inherits_mixin", + metadata, + postgresql_inherits=("base", "name_mixin"), + ) + + metadata.create_all(connection) + + assert_inherits_from("base", ()) + assert_inherits_from("name_mixin", ()) + + assert_inherits_from("single_inherits", ("base",)) + assert_column_names("single_inherits", ["id"]) + + assert_inherits_from("single_inherits_tuple_arg", ("base",)) + + assert_inherits_from("inherits_mixin", ("base", "name_mixin")) + assert_column_names("inherits_mixin", ["id", "name"]) + + def test_table_storage_params(self, metadata, connection): + def assert_has_storage_param(table_name, option_key, option_value): + table_options = inspect(connection).get_table_options(table_name) + storage_params = table_options["postgresql_with"] + assert isinstance(storage_params, dict) + eq_(storage_params[option_key], option_value) + + Table("table_no_storage_params", metadata) + Table( + "table_with_fillfactor", + metadata, + postgresql_with={"fillfactor": 10}, + ) + Table( + "table_with_parallel_workers", + metadata, + postgresql_with={"parallel_workers": 15}, + ) + + metadata.create_all(connection) + + no_params_options = inspect(connection).get_table_options( + "table_no_storage_params" + ) + assert "postgresql_with" not in no_params_options + + assert_has_storage_param("table_with_fillfactor", "fillfactor", "10") + assert_has_storage_param( + "table_with_parallel_workers", "parallel_workers", "15" + ) + + def test_table_using_default(self, metadata: MetaData, connection): + Table("table_using_heap", metadata, postgresql_using="heap").create( + connection + ) + options = inspect(connection).get_table_options("table_using_heap") + is_false("postgresql_using" in options) + + def test_table_using_custom(self, metadata: MetaData, connection): + if not connection.exec_driver_sql( + "SELECT rolsuper FROM pg_roles WHERE rolname = current_user" + ).scalar(): + config.skip_test("superuser required for CREATE ACCESS METHOD") + connection.exec_driver_sql( + "CREATE ACCESS METHOD myaccessmethod " + "TYPE TABLE " + "HANDLER heap_tableam_handler" + ) + Table( + "table_using_myaccessmethod", + metadata, + postgresql_using="myaccessmethod", + ).create(connection) + + options = inspect(connection).get_table_options( + "table_using_myaccessmethod" + ) + eq_(options["postgresql_using"], "myaccessmethod") diff --git a/test/requirements.py b/test/requirements.py index 3b5ea9e43d..58c9dd8107 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -2136,7 +2136,7 @@ class DefaultRequirements(SuiteRequirements): @property def reflect_table_options(self): - return only_on(["mysql", "mariadb", "oracle"]) + return only_on(["mysql", "mariadb", "oracle", "postgresql"]) @property def materialized_views(self): -- 2.47.3