You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@polaris.apache.org by ad...@apache.org on 2026/08/24 11:52:27 UTC
(polaris) branch main updated: Fix relational JDBC location-overlap index to match the optimized sibling-check query (#5301)
This is an automated email from the ASF dual-hosted git repository.
adutra pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/polaris.git
The following commit(s) were added to refs/heads/main by this push:
new 75f97a7e08 Fix relational JDBC location-overlap index to match the optimized sibling-check query (#5301)
75f97a7e08 is described below
commit 75f97a7e08e630821f68ae6649c4af9aa35359c2
Author: Prithvi S <pr...@gmail.com>
AuthorDate: Mon Aug 24 17:22:21 2026 +0530
Fix relational JDBC location-overlap index to match the optimized sibling-check query (#5301)
The optimized sibling check reads location_without_scheme via
QueryGenerator.generateOverlapQuery, which filters `realm_id` and
`catalog_id`. On Postgres and CockroachDB the idx_locations index led
with `parent_id` instead of `catalog_id`, so the predicate could only
probe the realm_id prefix and every CREATE TABLE / CREATE NAMESPACE with
OPTIMIZED_SIBLING_CHECK enabled fell back to a scan instead of the
intended indexed lookup. H2 already used the correct columns.
Schema v5 has already shipped in 1.7.0, so rather than editing a released
script this adds schema v6 for all backends with the corrected index and
bumps the latest schema version. Fresh installs bootstrap at v6 and get
the right index automatically. Existing deployments need a one-time
manual index recreation, documented in the changelog upgrade notes,
because Polaris has no automated schema migrations.
---
CHANGELOG.md | 17 ++
.../persistence/relational/jdbc/DatabaseType.java | 6 +-
.../src/main/resources/cockroachdb/schema-v6.sql | 281 +++++++++++++++++++++
.../src/main/resources/h2/schema-v6.sql | 274 ++++++++++++++++++++
.../src/main/resources/postgres/schema-v6.sql | 275 ++++++++++++++++++++
.../relational/jdbc/JdbcBootstrapUtilsTest.java | 12 +-
6 files changed, 857 insertions(+), 8 deletions(-)
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 5d656520a9..6b9d6fcc26 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -31,6 +31,17 @@ request adding CHANGELOG notes for breaking (!) changes and possibly other secti
### Upgrade notes
+- Relational JDBC: schema version 6 corrects the `idx_locations` index on Postgres and CockroachDB
+ (see Fixes). Fresh bootstraps use schema v6 automatically and get the right index. Because Polaris
+ has no automated schema migrations, existing Postgres/CockroachDB deployments keep the old,
+ ineffective index until an operator recreates it manually:
+ ```sql
+ DROP INDEX polaris_schema.idx_locations;
+ CREATE INDEX idx_locations ON polaris_schema.entities USING btree (realm_id, catalog_id, location_without_scheme)
+ WHERE location_without_scheme IS NOT NULL;
+ ```
+ H2 is unaffected.
+
### Breaking changes
- Concurrent table commits that hit a stale sequence number now return a retryable `409` instead of a fatal `400`, for both single-table commits and `commitTransaction`.
@@ -71,6 +82,12 @@ request adding CHANGELOG notes for breaking (!) changes and possibly other secti
- Fixed a false-negative in the JDBC optimized location-overlap check (`OPTIMIZED_SIBLING_CHECK`). Ancestor locations stored in `location_without_scheme` without a trailing slash were not matched by the generated ancestor equality terms, allowing nested table/namespace locations to be created under existing prefixes. The query now emits both slash-terminated and non-slash-terminated prefix terms and uses a slash-terminated `LIKE` pattern for descendant matching.
- Python CLI `setup` now preserves the Azure `hierarchical` storage flag during apply and export
- Fixed policy detach on the relational JDBC backend silently doing nothing when the mapping's `parameters` changed in between. The delete's `WHERE` clause included the non-key `parameters` column, so a re-attach landing between the detach's lookup and its delete made the delete match zero rows while detach still reported success, leaving the policy attached. The delete is now keyed on the mapping's identity columns, matching the table's primary key and the transactional backend's behavior.
+- Relational JDBC: the `idx_locations` index used by the optimized sibling check now matches the
+ query that reads it. On Postgres and CockroachDB the index led with `parent_id`, while the overlap
+ query filters `catalog_id`, so with `OPTIMIZED_SIBLING_CHECK` enabled every `CREATE TABLE` /
+ `CREATE NAMESPACE` fell back to a realm-wide scan instead of the intended indexed lookup. A new
+ schema version 6 creates the index on `(realm_id, catalog_id, location_without_scheme)`; H2 was
+ already correct. Existing deployments need a manual index recreation — see Upgrade notes.
### Commits
diff --git a/persistence/relational-jdbc/src/main/java/org/apache/polaris/persistence/relational/jdbc/DatabaseType.java b/persistence/relational-jdbc/src/main/java/org/apache/polaris/persistence/relational/jdbc/DatabaseType.java
index aa8aab5b12..3918f2f8ad 100644
--- a/persistence/relational-jdbc/src/main/java/org/apache/polaris/persistence/relational/jdbc/DatabaseType.java
+++ b/persistence/relational-jdbc/src/main/java/org/apache/polaris/persistence/relational/jdbc/DatabaseType.java
@@ -53,9 +53,9 @@ public enum DatabaseType {
*/
public int getLatestSchemaVersion() {
return switch (this) {
- case POSTGRES -> 5; // PostgreSQL has schemas v1, v2, v3, v4, v5
- case COCKROACHDB -> 5; // CockroachDB schema version kept in sync with PostgreSQL
- case H2 -> 5; // H2 uses same schemas as PostgreSQL
+ case POSTGRES -> 6; // PostgreSQL has schemas v1, v2, v3, v4, v5, v6
+ case COCKROACHDB -> 6; // CockroachDB schema version kept in sync with PostgreSQL
+ case H2 -> 6; // H2 uses same schemas as PostgreSQL
};
}
diff --git a/persistence/relational-jdbc/src/main/resources/cockroachdb/schema-v6.sql b/persistence/relational-jdbc/src/main/resources/cockroachdb/schema-v6.sql
new file mode 100644
index 0000000000..a8abb99f6a
--- /dev/null
+++ b/persistence/relational-jdbc/src/main/resources/cockroachdb/schema-v6.sql
@@ -0,0 +1,281 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file--
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"). You may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- CockroachDB schema v6 (matching PostgreSQL schema v6)
+-- Schema version is kept in sync with PostgreSQL to ensure correct column selection in ModelEntity.
+-- Changes from v5:
+-- * `idx_locations` now leads with `catalog_id` instead of `parent_id`, matching the predicate in
+-- QueryGenerator.generateOverlapQuery (WHERE realm_id = ? AND catalog_id = ?). The previous
+-- definition let the optimized sibling check (OPTIMIZED_SIBLING_CHECK) use only the realm_id
+-- prefix, degrading CREATE TABLE / CREATE NAMESPACE to a realm-wide scan.
+-- Features:
+-- * Uses INT4 explicitly for all integer columns (required for CockroachDB JDBC driver)
+-- * Includes all tables: version, entities, grant_records, principal_authentication_data,
+-- policy_mapping_record, events, scan_metrics_report, commit_metrics_report
+-- * Compatible with PostgreSQL wire protocol
+
+CREATE SCHEMA IF NOT EXISTS POLARIS_SCHEMA;
+SET search_path TO POLARIS_SCHEMA;
+
+CREATE TABLE IF NOT EXISTS version (
+ version_key TEXT PRIMARY KEY,
+ version_value INT4 NOT NULL
+);
+INSERT INTO version (version_key, version_value)
+VALUES ('version', 6)
+ON CONFLICT (version_key) DO UPDATE
+SET version_value = EXCLUDED.version_value;
+COMMENT ON TABLE version IS 'the version of the JDBC schema in use';
+
+CREATE TABLE IF NOT EXISTS entities (
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ id BIGINT NOT NULL,
+ parent_id BIGINT NOT NULL,
+ name TEXT NOT NULL,
+ entity_version INT4 NOT NULL,
+ type_code INT4 NOT NULL,
+ sub_type_code INT4 NOT NULL,
+ create_timestamp BIGINT NOT NULL,
+ drop_timestamp BIGINT NOT NULL,
+ purge_timestamp BIGINT NOT NULL,
+ to_purge_timestamp BIGINT NOT NULL,
+ last_update_timestamp BIGINT NOT NULL,
+ properties JSONB not null default '{}'::JSONB,
+ internal_properties JSONB not null default '{}'::JSONB,
+ grant_records_version INT4 NOT NULL,
+ location_without_scheme TEXT,
+ PRIMARY KEY (realm_id, id),
+ CONSTRAINT constraint_name UNIQUE (realm_id, catalog_id, parent_id, type_code, name)
+);
+
+-- TODO: create indexes based on all query pattern.
+CREATE INDEX IF NOT EXISTS idx_entities ON entities (realm_id, catalog_id, id);
+CREATE INDEX IF NOT EXISTS idx_locations
+ ON entities USING btree (realm_id, catalog_id, location_without_scheme)
+ WHERE location_without_scheme IS NOT NULL;
+
+COMMENT ON TABLE entities IS 'all the entities';
+
+COMMENT ON COLUMN entities.realm_id IS 'realm_id used for multi-tenancy';
+COMMENT ON COLUMN entities.catalog_id IS 'catalog id';
+COMMENT ON COLUMN entities.id IS 'entity id';
+COMMENT ON COLUMN entities.parent_id IS 'entity id of parent';
+COMMENT ON COLUMN entities.name IS 'entity name';
+COMMENT ON COLUMN entities.entity_version IS 'version of the entity';
+COMMENT ON COLUMN entities.type_code IS 'type code';
+COMMENT ON COLUMN entities.sub_type_code IS 'sub type of entity';
+COMMENT ON COLUMN entities.create_timestamp IS 'creation time of entity';
+COMMENT ON COLUMN entities.drop_timestamp IS 'time of drop of entity';
+COMMENT ON COLUMN entities.purge_timestamp IS 'time to start purging entity';
+COMMENT ON COLUMN entities.last_update_timestamp IS 'last time the entity is touched';
+COMMENT ON COLUMN entities.properties IS 'entities properties json';
+COMMENT ON COLUMN entities.internal_properties IS 'entities internal properties json';
+COMMENT ON COLUMN entities.grant_records_version IS 'the version of grant records change on the entity';
+
+CREATE TABLE IF NOT EXISTS grant_records (
+ realm_id TEXT NOT NULL,
+ securable_catalog_id BIGINT NOT NULL,
+ securable_id BIGINT NOT NULL,
+ grantee_catalog_id BIGINT NOT NULL,
+ grantee_id BIGINT NOT NULL,
+ privilege_code INT4,
+ PRIMARY KEY (realm_id, securable_catalog_id, securable_id, grantee_catalog_id, grantee_id, privilege_code)
+);
+
+COMMENT ON TABLE grant_records IS 'grant records for entities';
+
+COMMENT ON COLUMN grant_records.securable_catalog_id IS 'catalog id of the securable';
+COMMENT ON COLUMN grant_records.securable_id IS 'entity id of the securable';
+COMMENT ON COLUMN grant_records.grantee_catalog_id IS 'catalog id of the grantee';
+COMMENT ON COLUMN grant_records.grantee_id IS 'id of the grantee';
+COMMENT ON COLUMN grant_records.privilege_code IS 'privilege code';
+
+CREATE TABLE IF NOT EXISTS principal_authentication_data (
+ realm_id TEXT NOT NULL,
+ principal_id BIGINT NOT NULL,
+ principal_client_id VARCHAR(255) NOT NULL,
+ main_secret_hash VARCHAR(255) NOT NULL,
+ secondary_secret_hash VARCHAR(255) NOT NULL,
+ secret_salt VARCHAR(255) NOT NULL,
+ PRIMARY KEY (realm_id, principal_client_id)
+);
+
+COMMENT ON TABLE principal_authentication_data IS 'authentication data for client';
+
+CREATE TABLE IF NOT EXISTS policy_mapping_record (
+ realm_id TEXT NOT NULL,
+ target_catalog_id BIGINT NOT NULL,
+ target_id BIGINT NOT NULL,
+ policy_type_code INT4 NOT NULL,
+ policy_catalog_id BIGINT NOT NULL,
+ policy_id BIGINT NOT NULL,
+ parameters JSONB NOT NULL DEFAULT '{}'::JSONB,
+ PRIMARY KEY (realm_id, target_catalog_id, target_id, policy_type_code, policy_catalog_id, policy_id)
+);
+
+CREATE INDEX IF NOT EXISTS idx_policy_mapping_record ON policy_mapping_record (realm_id, policy_type_code, policy_catalog_id, policy_id, target_catalog_id, target_id);
+
+CREATE TABLE IF NOT EXISTS events (
+ realm_id TEXT NOT NULL,
+ catalog_id TEXT,
+ event_id TEXT NOT NULL,
+ request_id TEXT,
+ event_type TEXT NOT NULL,
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ resource_type TEXT NOT NULL,
+ resource_identifier TEXT NOT NULL,
+ additional_properties JSONB NOT NULL DEFAULT '{}'::JSONB,
+ PRIMARY KEY (event_id)
+);
+
+-- ============================================================================
+-- SCAN METRICS REPORT TABLE
+-- ============================================================================
+
+CREATE TABLE IF NOT EXISTS scan_metrics_report (
+ report_id TEXT NOT NULL,
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ table_id BIGINT NOT NULL,
+
+ -- Report metadata
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ request_id TEXT,
+
+ -- Trace correlation
+ otel_trace_id TEXT,
+ otel_span_id TEXT,
+ report_trace_id TEXT,
+
+ -- Scan context
+ snapshot_id BIGINT,
+ schema_id INT4,
+ filter_expression TEXT,
+ projected_field_ids TEXT,
+ projected_field_names TEXT,
+
+ -- Scan metrics
+ result_data_files BIGINT DEFAULT 0,
+ result_delete_files BIGINT DEFAULT 0,
+ total_file_size_bytes BIGINT DEFAULT 0,
+ total_data_manifests BIGINT DEFAULT 0,
+ total_delete_manifests BIGINT DEFAULT 0,
+ scanned_data_manifests BIGINT DEFAULT 0,
+ scanned_delete_manifests BIGINT DEFAULT 0,
+ skipped_data_manifests BIGINT DEFAULT 0,
+ skipped_delete_manifests BIGINT DEFAULT 0,
+ skipped_data_files BIGINT DEFAULT 0,
+ skipped_delete_files BIGINT DEFAULT 0,
+ total_planning_duration_ms BIGINT DEFAULT 0,
+
+ -- Equality/positional delete metrics
+ equality_delete_files BIGINT DEFAULT 0,
+ positional_delete_files BIGINT DEFAULT 0,
+ indexed_delete_files BIGINT DEFAULT 0,
+ total_delete_file_size_bytes BIGINT DEFAULT 0,
+
+ -- Additional metadata (for extensibility)
+ metadata JSONB DEFAULT '{}'::JSONB,
+
+ PRIMARY KEY (realm_id, report_id)
+);
+
+COMMENT ON TABLE scan_metrics_report IS 'Scan metrics reports as first-class entities';
+
+-- Index for retention cleanup by timestamp
+CREATE INDEX IF NOT EXISTS idx_scan_report_timestamp ON scan_metrics_report(realm_id, timestamp_ms);
+
+-- Index for query lookups by catalog_id and table_id
+CREATE INDEX IF NOT EXISTS idx_scan_report_lookup ON scan_metrics_report(realm_id, catalog_id, table_id, timestamp_ms);
+
+-- ============================================================================
+-- COMMIT METRICS REPORT TABLE
+-- ============================================================================
+
+CREATE TABLE IF NOT EXISTS commit_metrics_report (
+ report_id TEXT NOT NULL,
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ table_id BIGINT NOT NULL,
+
+ -- Report metadata
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ request_id TEXT,
+
+ -- Trace correlation
+ otel_trace_id TEXT,
+ otel_span_id TEXT,
+ report_trace_id TEXT,
+
+ -- Commit context
+ snapshot_id BIGINT NOT NULL,
+ sequence_number BIGINT,
+ operation TEXT NOT NULL,
+
+ -- File metrics
+ added_data_files BIGINT DEFAULT 0,
+ removed_data_files BIGINT DEFAULT 0,
+ total_data_files BIGINT DEFAULT 0,
+ added_delete_files BIGINT DEFAULT 0,
+ removed_delete_files BIGINT DEFAULT 0,
+ total_delete_files BIGINT DEFAULT 0,
+
+ -- Equality delete files
+ added_equality_delete_files BIGINT DEFAULT 0,
+ removed_equality_delete_files BIGINT DEFAULT 0,
+
+ -- Positional delete files
+ added_positional_delete_files BIGINT DEFAULT 0,
+ removed_positional_delete_files BIGINT DEFAULT 0,
+
+ -- Record metrics
+ added_records BIGINT DEFAULT 0,
+ removed_records BIGINT DEFAULT 0,
+ total_records BIGINT DEFAULT 0,
+
+ -- Size metrics
+ added_file_size_bytes BIGINT DEFAULT 0,
+ removed_file_size_bytes BIGINT DEFAULT 0,
+ total_file_size_bytes BIGINT DEFAULT 0,
+
+ -- Duration and attempts
+ total_duration_ms BIGINT DEFAULT 0,
+ attempts INT4 DEFAULT 1,
+
+ -- Additional metadata (for extensibility)
+ metadata JSONB DEFAULT '{}'::JSONB,
+
+ PRIMARY KEY (realm_id, report_id)
+);
+
+COMMENT ON TABLE commit_metrics_report IS 'Commit metrics reports as first-class entities';
+
+-- Index for retention cleanup by timestamp
+CREATE INDEX IF NOT EXISTS idx_commit_report_timestamp ON commit_metrics_report(realm_id, timestamp_ms);
+
+-- Index for query lookups by catalog_id and table_id
+CREATE INDEX IF NOT EXISTS idx_commit_report_lookup ON commit_metrics_report(realm_id, catalog_id, table_id, timestamp_ms);
+
+-- INT4 type used directly in table definitions for CockroachDB JDBC compatibility
+-- CockroachDB requires explicit INT4 type declarations to correctly map columns to Java's Integer type.
+-- Using generic INTEGER or INT types causes type mapping failures in CockroachDB's JDBC driver.
+-- INT4 is equivalent to INTEGER in PostgreSQL, ensuring compatibility with both databases.
diff --git a/persistence/relational-jdbc/src/main/resources/h2/schema-v6.sql b/persistence/relational-jdbc/src/main/resources/h2/schema-v6.sql
new file mode 100644
index 0000000000..9dc48de033
--- /dev/null
+++ b/persistence/relational-jdbc/src/main/resources/h2/schema-v6.sql
@@ -0,0 +1,274 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file--
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"). You may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+
+-- Changes from v5:
+-- * Schema version bumped to keep H2 in lockstep with Postgres/CockroachDB v6, which correct the
+-- `idx_locations` index. H2 already indexes (realm_id, catalog_id, location_without_scheme), so
+-- the index definition here is unchanged.
+
+CREATE SCHEMA IF NOT EXISTS POLARIS_SCHEMA;
+SET SCHEMA POLARIS_SCHEMA;
+
+CREATE TABLE IF NOT EXISTS version (
+ version_key VARCHAR PRIMARY KEY,
+ version_value INTEGER NOT NULL
+);
+
+MERGE INTO version (version_key, version_value)
+ KEY (version_key)
+ VALUES ('version', 6);
+
+-- H2 supports COMMENT, but some modes may ignore it
+COMMENT ON TABLE version IS 'the version of the JDBC schema in use';
+
+CREATE TABLE IF NOT EXISTS entities (
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ id BIGINT NOT NULL,
+ parent_id BIGINT NOT NULL,
+ name TEXT NOT NULL,
+ entity_version INT NOT NULL,
+ type_code INT NOT NULL,
+ sub_type_code INT NOT NULL,
+ create_timestamp BIGINT NOT NULL,
+ drop_timestamp BIGINT NOT NULL,
+ purge_timestamp BIGINT NOT NULL,
+ to_purge_timestamp BIGINT NOT NULL,
+ last_update_timestamp BIGINT NOT NULL,
+ properties TEXT NOT NULL DEFAULT '{}',
+ internal_properties TEXT NOT NULL DEFAULT '{}',
+ grant_records_version INT NOT NULL,
+ location_without_scheme TEXT,
+ PRIMARY KEY (realm_id, id),
+ CONSTRAINT constraint_name UNIQUE (realm_id, catalog_id, parent_id, type_code, name)
+);
+
+CREATE INDEX IF NOT EXISTS idx_locations ON entities(realm_id, catalog_id, location_without_scheme);
+
+-- TODO: create indexes based on all query pattern.
+CREATE INDEX IF NOT EXISTS idx_entities ON entities (realm_id, catalog_id, id);
+CREATE INDEX IF NOT EXISTS idx_entities_catalog_id_id ON entities (catalog_id, id);
+
+COMMENT ON TABLE entities IS 'all the entities';
+
+COMMENT ON COLUMN entities.catalog_id IS 'catalog id';
+COMMENT ON COLUMN entities.id IS 'entity id';
+COMMENT ON COLUMN entities.parent_id IS 'entity id of parent';
+COMMENT ON COLUMN entities.name IS 'entity name';
+COMMENT ON COLUMN entities.entity_version IS 'version of the entity';
+COMMENT ON COLUMN entities.type_code IS 'type code';
+COMMENT ON COLUMN entities.sub_type_code IS 'sub type of entity';
+COMMENT ON COLUMN entities.create_timestamp IS 'creation time of entity';
+COMMENT ON COLUMN entities.drop_timestamp IS 'time of drop of entity';
+COMMENT ON COLUMN entities.purge_timestamp IS 'time to start purging entity';
+COMMENT ON COLUMN entities.last_update_timestamp IS 'last time the entity is touched';
+COMMENT ON COLUMN entities.properties IS 'entities properties json';
+COMMENT ON COLUMN entities.internal_properties IS 'entities internal properties json';
+COMMENT ON COLUMN entities.grant_records_version IS 'the version of grant records change on the entity';
+
+CREATE TABLE IF NOT EXISTS grant_records (
+ realm_id TEXT NOT NULL,
+ securable_catalog_id BIGINT NOT NULL,
+ securable_id BIGINT NOT NULL,
+ grantee_catalog_id BIGINT NOT NULL,
+ grantee_id BIGINT NOT NULL,
+ privilege_code INTEGER,
+ PRIMARY KEY (realm_id, securable_catalog_id, securable_id, grantee_catalog_id, grantee_id, privilege_code)
+);
+
+COMMENT ON TABLE grant_records IS 'grant records for entities';
+COMMENT ON COLUMN grant_records.securable_catalog_id IS 'catalog id of the securable';
+COMMENT ON COLUMN grant_records.securable_id IS 'entity id of the securable';
+COMMENT ON COLUMN grant_records.grantee_catalog_id IS 'catalog id of the grantee';
+COMMENT ON COLUMN grant_records.grantee_id IS 'id of the grantee';
+COMMENT ON COLUMN grant_records.privilege_code IS 'privilege code';
+
+CREATE INDEX IF NOT EXISTS idx_grants_realm_grantee
+ ON grant_records (realm_id, grantee_id);
+CREATE INDEX IF NOT EXISTS idx_grants_realm_securable
+ ON grant_records (realm_id, securable_id);
+
+CREATE TABLE IF NOT EXISTS principal_authentication_data (
+ realm_id TEXT NOT NULL,
+ principal_id BIGINT NOT NULL,
+ principal_client_id VARCHAR(255) NOT NULL,
+ main_secret_hash VARCHAR(255) NOT NULL,
+ secondary_secret_hash VARCHAR(255) NOT NULL,
+ secret_salt VARCHAR(255) NOT NULL,
+ PRIMARY KEY (realm_id, principal_client_id)
+);
+
+COMMENT ON TABLE principal_authentication_data IS 'authentication data for client';
+
+CREATE TABLE IF NOT EXISTS policy_mapping_record (
+ realm_id TEXT NOT NULL,
+ target_catalog_id BIGINT NOT NULL,
+ target_id BIGINT NOT NULL,
+ policy_type_code INTEGER NOT NULL,
+ policy_catalog_id BIGINT NOT NULL,
+ policy_id BIGINT NOT NULL,
+ parameters TEXT NOT NULL DEFAULT '{}',
+ PRIMARY KEY (realm_id, target_catalog_id, target_id, policy_type_code, policy_catalog_id, policy_id)
+);
+
+CREATE INDEX IF NOT EXISTS idx_policy_mapping_record ON policy_mapping_record (realm_id, policy_type_code, policy_catalog_id, policy_id, target_catalog_id, target_id);
+
+CREATE TABLE IF NOT EXISTS events (
+ realm_id TEXT NOT NULL,
+ catalog_id TEXT,
+ event_id TEXT NOT NULL,
+ request_id TEXT,
+ event_type TEXT NOT NULL,
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ resource_type TEXT NOT NULL,
+ resource_identifier TEXT NOT NULL,
+ additional_properties TEXT NOT NULL,
+ PRIMARY KEY (event_id)
+);
+
+-- ============================================================================
+-- SCAN METRICS REPORT TABLE
+-- ============================================================================
+
+CREATE TABLE IF NOT EXISTS scan_metrics_report (
+ report_id TEXT NOT NULL,
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ table_id BIGINT NOT NULL,
+
+ -- Report metadata
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ request_id TEXT,
+
+ -- Trace correlation
+ otel_trace_id TEXT,
+ otel_span_id TEXT,
+ report_trace_id TEXT,
+
+ -- Scan context
+ snapshot_id BIGINT,
+ schema_id INTEGER,
+ filter_expression TEXT,
+ projected_field_ids TEXT,
+ projected_field_names TEXT,
+
+ -- Scan metrics
+ result_data_files BIGINT DEFAULT 0,
+ result_delete_files BIGINT DEFAULT 0,
+ total_file_size_bytes BIGINT DEFAULT 0,
+ total_data_manifests BIGINT DEFAULT 0,
+ total_delete_manifests BIGINT DEFAULT 0,
+ scanned_data_manifests BIGINT DEFAULT 0,
+ scanned_delete_manifests BIGINT DEFAULT 0,
+ skipped_data_manifests BIGINT DEFAULT 0,
+ skipped_delete_manifests BIGINT DEFAULT 0,
+ skipped_data_files BIGINT DEFAULT 0,
+ skipped_delete_files BIGINT DEFAULT 0,
+ total_planning_duration_ms BIGINT DEFAULT 0,
+
+ -- Equality/positional delete metrics
+ equality_delete_files BIGINT DEFAULT 0,
+ positional_delete_files BIGINT DEFAULT 0,
+ indexed_delete_files BIGINT DEFAULT 0,
+ total_delete_file_size_bytes BIGINT DEFAULT 0,
+
+ -- Additional metadata (for extensibility)
+ metadata TEXT DEFAULT '{}',
+
+ PRIMARY KEY (realm_id, report_id)
+);
+
+COMMENT ON TABLE scan_metrics_report IS 'Scan metrics reports as first-class entities';
+
+-- Index for retention cleanup by timestamp
+CREATE INDEX IF NOT EXISTS idx_scan_report_timestamp ON scan_metrics_report(realm_id, timestamp_ms);
+
+-- Index for query lookups by catalog_id and table_id
+CREATE INDEX IF NOT EXISTS idx_scan_report_lookup ON scan_metrics_report(realm_id, catalog_id, table_id, timestamp_ms);
+
+-- ============================================================================
+-- COMMIT METRICS REPORT TABLE
+-- ============================================================================
+
+CREATE TABLE IF NOT EXISTS commit_metrics_report (
+ report_id TEXT NOT NULL,
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ table_id BIGINT NOT NULL,
+
+ -- Report metadata
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ request_id TEXT,
+
+ -- Trace correlation
+ otel_trace_id TEXT,
+ otel_span_id TEXT,
+ report_trace_id TEXT,
+
+ -- Commit context
+ snapshot_id BIGINT NOT NULL,
+ sequence_number BIGINT,
+ operation TEXT NOT NULL,
+
+ -- File metrics
+ added_data_files BIGINT DEFAULT 0,
+ removed_data_files BIGINT DEFAULT 0,
+ total_data_files BIGINT DEFAULT 0,
+ added_delete_files BIGINT DEFAULT 0,
+ removed_delete_files BIGINT DEFAULT 0,
+ total_delete_files BIGINT DEFAULT 0,
+
+ -- Equality delete files
+ added_equality_delete_files BIGINT DEFAULT 0,
+ removed_equality_delete_files BIGINT DEFAULT 0,
+
+ -- Positional delete files
+ added_positional_delete_files BIGINT DEFAULT 0,
+ removed_positional_delete_files BIGINT DEFAULT 0,
+
+ -- Record metrics
+ added_records BIGINT DEFAULT 0,
+ removed_records BIGINT DEFAULT 0,
+ total_records BIGINT DEFAULT 0,
+
+ -- Size metrics
+ added_file_size_bytes BIGINT DEFAULT 0,
+ removed_file_size_bytes BIGINT DEFAULT 0,
+ total_file_size_bytes BIGINT DEFAULT 0,
+
+ -- Duration and attempts
+ total_duration_ms BIGINT DEFAULT 0,
+ attempts INTEGER DEFAULT 1,
+
+ -- Additional metadata (for extensibility)
+ metadata TEXT DEFAULT '{}',
+
+ PRIMARY KEY (realm_id, report_id)
+);
+
+COMMENT ON TABLE commit_metrics_report IS 'Commit metrics reports as first-class entities';
+
+-- Index for retention cleanup by timestamp
+CREATE INDEX IF NOT EXISTS idx_commit_report_timestamp ON commit_metrics_report(realm_id, timestamp_ms);
+
+-- Index for query lookups by catalog_id and table_id
+CREATE INDEX IF NOT EXISTS idx_commit_report_lookup ON commit_metrics_report(realm_id, catalog_id, table_id, timestamp_ms);
diff --git a/persistence/relational-jdbc/src/main/resources/postgres/schema-v6.sql b/persistence/relational-jdbc/src/main/resources/postgres/schema-v6.sql
new file mode 100644
index 0000000000..f60868818a
--- /dev/null
+++ b/persistence/relational-jdbc/src/main/resources/postgres/schema-v6.sql
@@ -0,0 +1,275 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file--
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"). You may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- Changes from v5:
+-- * `idx_locations` now leads with `catalog_id` instead of `parent_id`, matching the predicate in
+-- QueryGenerator.generateOverlapQuery (WHERE realm_id = ? AND catalog_id = ?). The previous
+-- definition let the optimized sibling check (OPTIMIZED_SIBLING_CHECK) use only the realm_id
+-- prefix, degrading CREATE TABLE / CREATE NAMESPACE to a realm-wide scan.
+
+CREATE SCHEMA IF NOT EXISTS POLARIS_SCHEMA;
+SET search_path TO POLARIS_SCHEMA;
+
+CREATE TABLE IF NOT EXISTS version (
+ version_key TEXT PRIMARY KEY,
+ version_value INTEGER NOT NULL
+);
+INSERT INTO version (version_key, version_value)
+VALUES ('version', 6)
+ON CONFLICT (version_key) DO UPDATE
+SET version_value = EXCLUDED.version_value;
+COMMENT ON TABLE version IS 'the version of the JDBC schema in use';
+
+CREATE TABLE IF NOT EXISTS entities (
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ id BIGINT NOT NULL,
+ parent_id BIGINT NOT NULL,
+ name TEXT NOT NULL,
+ entity_version INT NOT NULL,
+ type_code INT NOT NULL,
+ sub_type_code INT NOT NULL,
+ create_timestamp BIGINT NOT NULL,
+ drop_timestamp BIGINT NOT NULL,
+ purge_timestamp BIGINT NOT NULL,
+ to_purge_timestamp BIGINT NOT NULL,
+ last_update_timestamp BIGINT NOT NULL,
+ properties JSONB not null default '{}'::JSONB,
+ internal_properties JSONB not null default '{}'::JSONB,
+ grant_records_version INT NOT NULL,
+ location_without_scheme TEXT,
+ PRIMARY KEY (realm_id, id),
+ CONSTRAINT constraint_name UNIQUE (realm_id, catalog_id, parent_id, type_code, name)
+);
+
+-- TODO: create indexes based on all query pattern.
+CREATE INDEX IF NOT EXISTS idx_entities ON entities (realm_id, catalog_id, id);
+CREATE INDEX IF NOT EXISTS idx_entities_catalog_id_id ON entities (catalog_id, id);
+CREATE INDEX IF NOT EXISTS idx_locations
+ ON entities USING btree (realm_id, catalog_id, location_without_scheme)
+ WHERE location_without_scheme IS NOT NULL;
+
+COMMENT ON TABLE entities IS 'all the entities';
+
+COMMENT ON COLUMN entities.realm_id IS 'realm_id used for multi-tenancy';
+COMMENT ON COLUMN entities.catalog_id IS 'catalog id';
+COMMENT ON COLUMN entities.id IS 'entity id';
+COMMENT ON COLUMN entities.parent_id IS 'entity id of parent';
+COMMENT ON COLUMN entities.name IS 'entity name';
+COMMENT ON COLUMN entities.entity_version IS 'version of the entity';
+COMMENT ON COLUMN entities.type_code IS 'type code';
+COMMENT ON COLUMN entities.sub_type_code IS 'sub type of entity';
+COMMENT ON COLUMN entities.create_timestamp IS 'creation time of entity';
+COMMENT ON COLUMN entities.drop_timestamp IS 'time of drop of entity';
+COMMENT ON COLUMN entities.purge_timestamp IS 'time to start purging entity';
+COMMENT ON COLUMN entities.last_update_timestamp IS 'last time the entity is touched';
+COMMENT ON COLUMN entities.properties IS 'entities properties json';
+COMMENT ON COLUMN entities.internal_properties IS 'entities internal properties json';
+COMMENT ON COLUMN entities.grant_records_version IS 'the version of grant records change on the entity';
+
+CREATE TABLE IF NOT EXISTS grant_records (
+ realm_id TEXT NOT NULL,
+ securable_catalog_id BIGINT NOT NULL,
+ securable_id BIGINT NOT NULL,
+ grantee_catalog_id BIGINT NOT NULL,
+ grantee_id BIGINT NOT NULL,
+ privilege_code INTEGER,
+ PRIMARY KEY (realm_id, securable_catalog_id, securable_id, grantee_catalog_id, grantee_id, privilege_code)
+);
+
+COMMENT ON TABLE grant_records IS 'grant records for entities';
+
+COMMENT ON COLUMN grant_records.securable_catalog_id IS 'catalog id of the securable';
+COMMENT ON COLUMN grant_records.securable_id IS 'entity id of the securable';
+COMMENT ON COLUMN grant_records.grantee_catalog_id IS 'catalog id of the grantee';
+COMMENT ON COLUMN grant_records.grantee_id IS 'id of the grantee';
+COMMENT ON COLUMN grant_records.privilege_code IS 'privilege code';
+
+CREATE INDEX IF NOT EXISTS idx_grants_realm_grantee
+ ON grant_records (realm_id, grantee_id);
+CREATE INDEX IF NOT EXISTS idx_grants_realm_securable
+ ON grant_records (realm_id, securable_id);
+
+CREATE TABLE IF NOT EXISTS principal_authentication_data (
+ realm_id TEXT NOT NULL,
+ principal_id BIGINT NOT NULL,
+ principal_client_id VARCHAR(255) NOT NULL,
+ main_secret_hash VARCHAR(255) NOT NULL,
+ secondary_secret_hash VARCHAR(255) NOT NULL,
+ secret_salt VARCHAR(255) NOT NULL,
+ PRIMARY KEY (realm_id, principal_client_id)
+);
+
+COMMENT ON TABLE principal_authentication_data IS 'authentication data for client';
+
+CREATE TABLE IF NOT EXISTS policy_mapping_record (
+ realm_id TEXT NOT NULL,
+ target_catalog_id BIGINT NOT NULL,
+ target_id BIGINT NOT NULL,
+ policy_type_code INTEGER NOT NULL,
+ policy_catalog_id BIGINT NOT NULL,
+ policy_id BIGINT NOT NULL,
+ parameters JSONB NOT NULL DEFAULT '{}'::JSONB,
+ PRIMARY KEY (realm_id, target_catalog_id, target_id, policy_type_code, policy_catalog_id, policy_id)
+);
+
+CREATE INDEX IF NOT EXISTS idx_policy_mapping_record ON policy_mapping_record (realm_id, policy_type_code, policy_catalog_id, policy_id, target_catalog_id, target_id);
+
+CREATE TABLE IF NOT EXISTS events (
+ realm_id TEXT NOT NULL,
+ catalog_id TEXT,
+ event_id TEXT NOT NULL,
+ request_id TEXT,
+ event_type TEXT NOT NULL,
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ resource_type TEXT NOT NULL,
+ resource_identifier TEXT NOT NULL,
+ additional_properties JSONB NOT NULL DEFAULT '{}'::JSONB,
+ PRIMARY KEY (event_id)
+);
+
+-- ============================================================================
+-- SCAN METRICS REPORT TABLE
+-- ============================================================================
+
+CREATE TABLE IF NOT EXISTS scan_metrics_report (
+ report_id TEXT NOT NULL,
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ table_id BIGINT NOT NULL,
+
+ -- Report metadata
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ request_id TEXT,
+
+ -- Trace correlation
+ otel_trace_id TEXT,
+ otel_span_id TEXT,
+ report_trace_id TEXT,
+
+ -- Scan context
+ snapshot_id BIGINT,
+ schema_id INTEGER,
+ filter_expression TEXT,
+ projected_field_ids TEXT,
+ projected_field_names TEXT,
+
+ -- Scan metrics
+ result_data_files BIGINT DEFAULT 0,
+ result_delete_files BIGINT DEFAULT 0,
+ total_file_size_bytes BIGINT DEFAULT 0,
+ total_data_manifests BIGINT DEFAULT 0,
+ total_delete_manifests BIGINT DEFAULT 0,
+ scanned_data_manifests BIGINT DEFAULT 0,
+ scanned_delete_manifests BIGINT DEFAULT 0,
+ skipped_data_manifests BIGINT DEFAULT 0,
+ skipped_delete_manifests BIGINT DEFAULT 0,
+ skipped_data_files BIGINT DEFAULT 0,
+ skipped_delete_files BIGINT DEFAULT 0,
+ total_planning_duration_ms BIGINT DEFAULT 0,
+
+ -- Equality/positional delete metrics
+ equality_delete_files BIGINT DEFAULT 0,
+ positional_delete_files BIGINT DEFAULT 0,
+ indexed_delete_files BIGINT DEFAULT 0,
+ total_delete_file_size_bytes BIGINT DEFAULT 0,
+
+ -- Additional metadata (for extensibility)
+ metadata JSONB DEFAULT '{}'::JSONB,
+
+ PRIMARY KEY (realm_id, report_id)
+);
+
+COMMENT ON TABLE scan_metrics_report IS 'Scan metrics reports as first-class entities';
+
+-- Index for retention cleanup by timestamp
+CREATE INDEX IF NOT EXISTS idx_scan_report_timestamp ON scan_metrics_report(realm_id, timestamp_ms);
+
+-- Index for query lookups by catalog_id and table_id
+CREATE INDEX IF NOT EXISTS idx_scan_report_lookup ON scan_metrics_report(realm_id, catalog_id, table_id, timestamp_ms);
+
+-- ============================================================================
+-- COMMIT METRICS REPORT TABLE
+-- ============================================================================
+
+CREATE TABLE IF NOT EXISTS commit_metrics_report (
+ report_id TEXT NOT NULL,
+ realm_id TEXT NOT NULL,
+ catalog_id BIGINT NOT NULL,
+ table_id BIGINT NOT NULL,
+
+ -- Report metadata
+ timestamp_ms BIGINT NOT NULL,
+ principal_name TEXT,
+ request_id TEXT,
+
+ -- Trace correlation
+ otel_trace_id TEXT,
+ otel_span_id TEXT,
+ report_trace_id TEXT,
+
+ -- Commit context
+ snapshot_id BIGINT NOT NULL,
+ sequence_number BIGINT,
+ operation TEXT NOT NULL,
+
+ -- File metrics
+ added_data_files BIGINT DEFAULT 0,
+ removed_data_files BIGINT DEFAULT 0,
+ total_data_files BIGINT DEFAULT 0,
+ added_delete_files BIGINT DEFAULT 0,
+ removed_delete_files BIGINT DEFAULT 0,
+ total_delete_files BIGINT DEFAULT 0,
+
+ -- Equality delete files
+ added_equality_delete_files BIGINT DEFAULT 0,
+ removed_equality_delete_files BIGINT DEFAULT 0,
+
+ -- Positional delete files
+ added_positional_delete_files BIGINT DEFAULT 0,
+ removed_positional_delete_files BIGINT DEFAULT 0,
+
+ -- Record metrics
+ added_records BIGINT DEFAULT 0,
+ removed_records BIGINT DEFAULT 0,
+ total_records BIGINT DEFAULT 0,
+
+ -- Size metrics
+ added_file_size_bytes BIGINT DEFAULT 0,
+ removed_file_size_bytes BIGINT DEFAULT 0,
+ total_file_size_bytes BIGINT DEFAULT 0,
+
+ -- Duration and attempts
+ total_duration_ms BIGINT DEFAULT 0,
+ attempts INTEGER DEFAULT 1,
+
+ -- Additional metadata (for extensibility)
+ metadata JSONB DEFAULT '{}'::JSONB,
+
+ PRIMARY KEY (realm_id, report_id)
+);
+
+COMMENT ON TABLE commit_metrics_report IS 'Commit metrics reports as first-class entities';
+
+-- Index for retention cleanup by timestamp
+CREATE INDEX IF NOT EXISTS idx_commit_report_timestamp ON commit_metrics_report(realm_id, timestamp_ms);
+
+-- Index for query lookups by catalog_id and table_id
+CREATE INDEX IF NOT EXISTS idx_commit_report_lookup ON commit_metrics_report(realm_id, catalog_id, table_id, timestamp_ms);
diff --git a/persistence/relational-jdbc/src/test/java/org/apache/polaris/persistence/relational/jdbc/JdbcBootstrapUtilsTest.java b/persistence/relational-jdbc/src/test/java/org/apache/polaris/persistence/relational/jdbc/JdbcBootstrapUtilsTest.java
index b4f7a2fe65..a2ba435129 100644
--- a/persistence/relational-jdbc/src/test/java/org/apache/polaris/persistence/relational/jdbc/JdbcBootstrapUtilsTest.java
+++ b/persistence/relational-jdbc/src/test/java/org/apache/polaris/persistence/relational/jdbc/JdbcBootstrapUtilsTest.java
@@ -61,7 +61,7 @@ class JdbcBootstrapUtilsTest {
// Act & Assert
assertEquals(
- 5,
+ 6,
JdbcBootstrapUtils.getRealmBootstrapSchemaVersion(
DatabaseType.POSTGRES, currentVersion, -1, hasRealms));
assertEquals(
@@ -97,10 +97,12 @@ class JdbcBootstrapUtilsTest {
"3, true, 3",
"4, true, 4",
"5, true, 5",
- "2, false, 5",
- "3, false, 5",
- "4, false, 5",
- "5, false, 5"
+ "6, true, 6",
+ "2, false, 6",
+ "3, false, 6",
+ "4, false, 6",
+ "5, false, 6",
+ "6, false, 6"
})
void getVersion_whenExistingDbAndAutoDetect(
int currentVersion, boolean hasRealms, int expectedVersion) {