{{ message }}
Hide MySQL and PostgreSQL databases from system.tables by default#104416
Merged
pamarcos merged 29 commits intoMay 28, 2026
Merged
Hide MySQL and PostgreSQL databases from system.tables by default#104416pamarcos merged 29 commits into
pamarcos merged 29 commits into
Conversation
…bases_in_system_tables Broaden the setting to also hide MySQL and PostgreSQL databases from system.tables / system.columns / system.completions when disabled, since they too require remote calls to enumerate tables. The old name is preserved as an alias. Internal renames: GetDatabasesOptions::with_datalake_catalogs -> with_external_databases; DatabaseCatalog::isDatalakeCatalog -> isExternalDatabase; hasDatalakeCatalogs -> hasExternalDatabases; databases_without_datalake_catalogs -> databases_without_external.
Each assertion in the test now prints a short marker describing what it checks, so a failure in the reference diff is self-explanatory.
Contributor
clickhouse-gh
Bot
added
the
pr-backward-incompatible
![clickhouse-gh[bot]](https://avatars.githubusercontent.com/in/928874?s=60&v=4){kind=small}
Contributor
There was a problem hiding this comment.
Pull request overview
This PR changes how ClickHouse enumerates databases for system.tables, system.columns, and system.completions to avoid expensive per-table round trips when external database engines (PostgreSQL/MySQL and data lake catalogs) are present but slow/unavailable. It introduces a new setting name with an alias to preserve backwards compatibility while broadening the default filtering behavior.
Changes:
- Renames
show_data_lake_catalogs_in_system_tablestoshow_external_databases_in_system_tables(keeps the old name as an alias) and extends filtering to PostgreSQL/MySQL database engines. - Updates
DatabaseCatalogand multiple system-table implementations to useGetDatabasesOptions{.with_external_databases = ...}consistently. - Adds a stateless regression test ensuring external DBs are hidden from
system.tables/system.columnsby default and that the alias is visible insystem.settings.
Reviewed changes
Copilot reviewed 36 out of 36 changed files in this pull request and generated 3 comments.
Show a summary per file
The test only exercised the PostgreSQL engine path. MySQL takes the same code path in DatabaseCatalog::isExternalDatabase, but a regression that special-cases PostgreSQL would have slipped through. Add a MySQL case using ATTACH (CREATE DATABASE ... ENGINE = MySQL connects eagerly, ATTACH does not) with a short connect_timeout so the tolerated failure is fast.
- DatabaseCatalog.h: fix grammar in the renamed comment ("are implement"
-> "are implemented", "protect ourself" -> "protect ourselves").
- DatabaseCatalog.cpp:2244: update outdated TableNameHints comment that
still said "Skip datalake catalogs" to reflect the broader scope.
- 04210: add an assertion that system.completions also does not list
external databases by default.
The new stateless test runs in about 5 seconds and remains stable across repeated runs, so it should be eligible for fasttest to improve CI coverage.\n\nRef: ClickHouse#104416
Per review feedback from @nikitamikhaylov on PR ClickHouse#104416: replace the free helper with engine-name string compare in DatabaseCatalog.cpp by a virtual method on IDatabase. DataLake / MySQL / PostgreSQL override to return true; the default is false, so other engines (Atomic, Replicated, MaterializedPostgreSQL, ...) keep the right behavior without needing to be aware of the new classification. The naming is intentionally distinct from the existing isExternal() ("engine does not support ClickHouse internal tables") and isDatalakeCatalog() (narrower). See the doc comment in IDatabase.h.
Fast test does not build libpq or libmysql, so CREATE DATABASE ENGINE = PostgreSQL/MySQL silently does nothing in that environment, and the test's reference no longer matches the actual output. This is the same reason 01114_mysql_database_engine_segfault.sql and 03790_materialized_postgresql_nullptr_dereference.sql carry the tag. Reverts removal in 527e58e. CI fast-test failure: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=104416&sha=527e58ed25888cbc3061c7c1de91e18777c227ae&name_0=PR
kssenii
reviewed
May 9, 2026
Co-authored-by: Kseniia Sumarokova <sumarokovakseniia@gmail.com>
Rename the database-level helper to avoid confusion with IDatabase::isExternal and include MaterializedPostgreSQL in remote database filtering.
Pass through the test client API for integration tests that intentionally inspect MySQL and PostgreSQL metadata in and . PR: ClickHouse#104416 CI: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=104416&sha=158f2327e19aebbbfd2e588f15754e44210183a7&name_0=PR
kssenii
approved these changes
May 12, 2026
Rename the new setting and `DatabaseCatalog` filtering option to use remote database terminology consistently, while keeping `show_data_lake_catalogs_in_system_tables` as the compatibility alias. Convert the stateless coverage from `.sh` to `.sql` now that the SQL test runner can provide unique database names. PR: ClickHouse#104416
Use remote database terminology consistently in comments changed by the PR and avoid the old external database wording near `isRemoteDatabase`. PR: ClickHouse#104416
Explain `isExternal` in terms of database engines that do not own ClickHouse table metadata instead of using vague wording. PR: ClickHouse#104416
…l-databases-setting
Merged
via the queue into
ClickHouse:master
with commit May 28, 2026
faa0e55
163 of 165 checks passed
robot-ch-test-poll4
added
the
pr-synced-to-cloud
robot-ch-test-poll1
added
the
pr-must-backport-synced
This was referenced May 28, 2026
robot-ch-test-poll
added a commit
that referenced
this pull request
May 28, 2026
Cherry pick #104416 to 26.5: Hide MySQL and PostgreSQL databases from system.tables by default
robot-clickhouse
added a commit
that referenced
this pull request
May 28, 2026
…stem.tables by default
robot-ch-test-poll2
added a commit
that referenced
this pull request
May 28, 2026
Cherry pick #104416 to 26.3: Hide MySQL and PostgreSQL databases from system.tables by default
robot-clickhouse
added a commit
that referenced
this pull request
May 28, 2026
…stem.tables by default
robot-ch-test-poll2
added a commit
that referenced
this pull request
May 28, 2026
Cherry pick #104416 to 26.4: Hide MySQL and PostgreSQL databases from system.tables by default
robot-clickhouse
added a commit
that referenced
this pull request
May 28, 2026
…stem.tables by default
robot-ch-test-poll2
added
the
pr-backports-created
pamarcos
added a commit
that referenced
this pull request
May 29, 2026
Backport #104416 to 26.5: Hide MySQL and PostgreSQL databases from system.tables by default
pamarcos
added a commit
that referenced
this pull request
May 29, 2026
Remove accidentally backported `allow_experimental_geo_types_in_iceberg` so `02995_new_settings_history` only sees the setting from PR #104416. Update `test_database_glue/test_system_tables` to match the intended `system.databases` behavior: remote database names are local metadata and stay visible while table and column enumeration stays hidden by default. PR: #106046
pamarcos
added a commit
that referenced
this pull request
Jun 1, 2026
Backport #104416 to 26.4: Hide MySQL and PostgreSQL databases from system.tables by default
pamarcos
added a commit
that referenced
this pull request
Jun 1, 2026
Backport #104416 to 26.3: Hide MySQL and PostgreSQL databases from system.tables by default
1 task
pamarcos
added a commit
that referenced
this pull request
Jul 2, 2026
Revert commit e79fd5f and return to the layout from commit 9c7c5ab and commit 1fc129c: `show_remote_databases_in_system_tables` is recorded as `{true, true}` in both the `26.2` and the `26.7` settings-history blocks. The `26.2` entry covers the planned backports to 26.2+ release branches, and the `26.7` entry satisfies the Upgrade check, which requires an entry in a block newer than the 26.6 baseline release. Keeping both values `true` means `compatibility` mode never rolls the restored default back to `false`, matching the pre-#104416 behavior where `MySQL` and `PostgreSQL` databases were always visible.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Avoid implicit remote-table enumeration for remote database engines in
system.tables,system.columns, andsystem.completions.This prevents slow or unavailable
MySQL/PostgreSQLupstreams from blocking startup dictionaries, backup metadata queries, and interactive system-table reads.system.databasesstill shows database names unconditionally, andSHOW TABLES FROM <db>still opts in for the requested remote database.The old
show_data_lake_catalogs_in_system_tablessetting is kept as an alias. The new setting isshow_remote_databases_in_system_tables.Closes https://github.com/ClickHouse/clickhouse-private/issues/53621
Changelog category (leave one):
Changelog entry:
The setting
show_data_lake_catalogs_in_system_tableshas been renamed toshow_remote_databases_in_system_tablesand broadened: when its value is 0 (the default),MySQLandPostgreSQLdatabases are also hidden fromsystem.tables,system.columns, andsystem.completions, in addition to data lake catalogs. The old setting name is kept as an alias.Documentation entry for user-facing changes
Note
Medium Risk
Changes default visibility of MySQL/PostgreSQL/DataLakeCatalog databases in
system.tables,system.columns, andsystem.completions, which can affect tooling and queries that previously relied on implicit enumeration. Behavior is gated by a renamed setting with an alias, reducing but not eliminating upgrade risk.Overview
Prevents implicit enumeration of remote database engines by excluding data lake catalogs,
MySQL, andPostgreSQLdatabases fromsystem.tables,system.columns, andsystem.completionsunless the newshow_remote_databases_in_system_tablessetting is enabled.Renames and broadens
show_data_lake_catalogs_in_system_tablestoshow_remote_databases_in_system_tables(old name kept as an alias), and introducesIDatabase::isRemoteDatabase()with implementations for DataLake/MySQL/PostgreSQL plusDatabaseCatalogfiltering support.Ensures explicit
SHOW TABLES/SHOW COLUMNS/SHOW INDEXagainst a remote database still works by forcing the setting on for those queries, and updates backups and tests to use the new setting and verify the new default behavior.Reviewed by Cursor Bugbot for commit 980b830. Bugbot is set up for automated code reviews on this repo. Configure here.
Version info
26.6.1.21226.5.2.16,26.4.4.24,26.3.13.22