mirror of
https://github.com/open-metadata/OpenMetadata
synced 2026-05-24 09:39:11 +00:00
0a07caefc2
* fix(migration): preempt PDTS duplicates and recover invalid index in 1.12.9 CREATE UNIQUE INDEX CONCURRENTLY aborts when it hits existing duplicate keys but leaves an invalid index behind. On migration retry, IF NOT EXISTS no-ops successfully and gets checksum-logged, after which ADD CONSTRAINT USING INDEX fails permanently with "index ... is not valid". Hit at a customer with two duplicate table.systemProfile rows on a 10M-row PDTS. Adds two idempotent statements before the existing constraint build: - DO block: drops the invalid index and clears its migration-log entry when indisvalid=false. No-op on fresh DBs and on already-migrated environments (where the index is valid and owned by the constraint). - DELETE: collapses duplicate rows via single hash aggregate on the 4-column key + targeted self-join. Reads only key columns (no json scan), only touches rows in actual duplicate groups, no-op on clean DBs. Efficient on multi-million-row PDTS tables. Existing CREATE INDEX / ALTER TABLE / ANALYZE statements byte-identical so checksum-matched skips for already-migrated environments still apply. * fix(migration): self-heal in one pass + schema-scoped invalid-index probe Addresses Copilot review on #28238: 1. One-pass self-heal. MigrationFile.parseSQLFiles filters already-logged statements at parse time (MigrationFile.java:83). Clearing the CREATE log entry from inside a DO block doesn't bring CREATE back into the current pass's execution list — it would only re-run on the next migration cycle, leaving the same-pass ALTER to fail again. Replace the "DROP + clear log" pattern with "DROP + rebuild inline" so a valid index exists before ALTER runs in the same pass. Inline rebuild uses non-concurrent CREATE UNIQUE INDEX, which takes a brief ACCESS EXCLUSIVE lock on the table. Acceptable because this path fires only when the environment is already in a degraded state. Normal-path customers go through the CONCURRENTLY build below. 2. Schema-scoped invalid-index probe. pg_class.relname is not schema-unique. Anchor the lookup via i.indrelid = 'profiler_data_time_series'::regclass and DROP by index OID (invalid_idx::regclass), so an invalid index with the same name in another schema cannot accidentally trigger this branch. Existing CREATE INDEX / ALTER TABLE / ANALYZE statements byte-identical to before this PR, so checksum-matched skips still apply for already-migrated environments. Test gap (Copilot's third comment) for the recovery scenario tracked as follow-up — existing migration tests in MigrationWorkflowReprocessingTest are mock-based; verifying recovery end-to-end needs Postgres integration infrastructure. * chore(migration): trim verbose comments in 1.12.9/postgres/schemaChanges.sql Statement bodies unchanged — checksums identical. Detailed mechanism write-ups live in the commit log / PR description; the file keeps just the load-bearing intent comment above each statement. * fix(migration): scope PDTS dedup to operation IS NOT NULL Addresses Copilot review on PR #28238 discussion r3264066840. Postgres UNIQUE treats NULLs as DISTINCT by default, so the constraint on (entityFQNHash, extension, operation, timestamp) permits multiple rows where operation IS NULL — i.e. table.tableProfile and table.columnProfile rows that share the other key columns. The previous dedup used GROUP BY which treats NULLs as equal, so it would have collapsed retry-induced tableProfile / columnProfile pairs that the restored constraint never actually blocked. Restricting the subquery to operation IS NOT NULL (plus a defensive entityFQNHash IS NOT NULL) aligns dedup with constraint semantics. DMG's customer rows were all table.systemProfile (operation = INSERT), so this still removes the customer dupes correctly. tableProfile / columnProfile retry duplicates — if they exist — stay as-is, which is the same outcome the unique constraint would produce on its own. * perf(migration): boost work_mem / maintenance_work_mem for PDTS dedup at scale Mirrors the tuning pattern from 1.9.9/postgres/postDataMigrationSQLScript.sql (same table, same operation class). On 50M-row PDTS the dedup DELETE's hash aggregate spills to disk with default work_mem=4MB, adding ~30-60s of disk I/O. Bumping work_mem to 256MB keeps the aggregate in memory; maintenance_work_mem=512MB lets CREATE UNIQUE INDEX CONCURRENTLY sort in memory too. Session-level (not SET LOCAL) because schemaChanges runs in autocommit (CREATE INDEX CONCURRENTLY requires it) — SET LOCAL would reset between statements. RESET at the end of the file restores defaults before the connection returns to the Hikari pool. Expected runtime impact at customer scale: 20M rows: ~30s tuned vs ~40s default 50M rows: ~40s tuned vs ~90s default (avoids spill) * chore(migration): trim comments on PDTS dedup additions * chore(migration): drop 1.9.9 reference from mem comment |
||
|---|---|---|
| .. | ||
| sql | ||
| MIGRATION_SYSTEM.md | ||
| openmetadata-ops.sh | ||