fix(worker): null-safe embedding dedup queries #611

Merged
chris merged 3 commits from hotfix/worker-embedding-null-comparison into main 2026-07-14 17:09:53 +02:00
Owner

Hotfix: Embedding write failure

Root cause: Dedup SELECT in upsertOccurrenceEmbedding uses ((col IS NULL AND :param IS NULL) OR col = :param) pattern. PostgreSQL cannot type-check the parameter when it's null, causing PSQLException → JDBC connection leak → pool exhaustion → worker crash loop.

Fix: Replace with IS NOT DISTINCT FROM CAST(:param AS text) — null-safe, already used in PlaceLookupService/PlaceGazetteerImportService.

Scope: 2 SQL queries in WorkerResultService.java (class_occurrence_embeddings + pending_face_embeddings dedup checks). No migration. No other files affected.

Verified: Compiles clean. Version bumped to 1.08.

## Hotfix: Embedding write failure **Root cause:** Dedup SELECT in `upsertOccurrenceEmbedding` uses `((col IS NULL AND :param IS NULL) OR col = :param)` pattern. PostgreSQL cannot type-check the parameter when it's null, causing PSQLException → JDBC connection leak → pool exhaustion → worker crash loop. **Fix:** Replace with `IS NOT DISTINCT FROM CAST(:param AS text)` — null-safe, already used in PlaceLookupService/PlaceGazetteerImportService. **Scope:** 2 SQL queries in WorkerResultService.java (class_occurrence_embeddings + pending_face_embeddings dedup checks). No migration. No other files affected. **Verified:** Compiles clean. Version bumped to 1.08.
fix(worker): null-safe embedding dedup queries
All checks were successful
Build and Deploy / Detect changed services and test scopes (push) Successful in 3s
Build and Deploy / Run scoped PR tests (push) Has been skipped
Build and Deploy / Smoke API and media worker (push) Has been skipped
Build and Deploy / Smoke web (push) Has been skipped
Build and Deploy / Smoke ML worker (push) Has been skipped
Build and Deploy / Build plants worker image (push) Has been skipped
Build and Deploy / Build people worker image (push) Has been skipped
Build and Deploy / Build converter worker image (push) Has been skipped
Build and Deploy / Build API image (push) Has been skipped
Build and Deploy / Build media worker image (push) Has been skipped
Build and Deploy / Build web image (push) Has been skipped
Build and Deploy / Build ML worker image (push) Has been skipped
Build and Deploy / Deploy API (push) Has been skipped
Build and Deploy / Deploy web (push) Has been skipped
Build and Deploy / Deploy media worker (push) Has been skipped
Build and Deploy / Deploy ML worker (push) Has been skipped
Build and Deploy / Deploy plants worker (push) Has been skipped
Build and Deploy / Deploy people worker (push) Has been skipped
Build and Deploy / Deploy converter worker (push) Has been skipped
Build and Deploy / Clean Docker build artifacts (push) Has been skipped
443f4dbdf3
Replace broken null-comparison pattern with IS NOT DISTINCT FROM
to fix PSQLException when modelName/modelVersion params are null.

Fixes connection pool exhaustion and worker crash loops.
test(api): add regression tests for embedding null-parameter SQL bug
All checks were successful
Build and Deploy / Detect changed services and test scopes (push) Successful in 3s
Build and Deploy / Run scoped PR tests (push) Has been skipped
Build and Deploy / Smoke API and media worker (push) Has been skipped
Build and Deploy / Smoke web (push) Has been skipped
Build and Deploy / Smoke ML worker (push) Has been skipped
Build and Deploy / Build plants worker image (push) Has been skipped
Build and Deploy / Build people worker image (push) Has been skipped
Build and Deploy / Build converter worker image (push) Has been skipped
Build and Deploy / Build API image (push) Has been skipped
Build and Deploy / Build media worker image (push) Has been skipped
Build and Deploy / Build web image (push) Has been skipped
Build and Deploy / Build ML worker image (push) Has been skipped
Build and Deploy / Deploy API (push) Has been skipped
Build and Deploy / Deploy web (push) Has been skipped
Build and Deploy / Deploy media worker (push) Has been skipped
Build and Deploy / Deploy ML worker (push) Has been skipped
Build and Deploy / Deploy plants worker (push) Has been skipped
Build and Deploy / Deploy people worker (push) Has been skipped
Build and Deploy / Deploy converter worker (push) Has been skipped
Build and Deploy / Clean Docker build artifacts (push) Has been skipped
4c906d7c8c
- upsertOccurrenceEmbedding: null modelName/modelVersion creates row without PSQLException
- upsertOccurrenceEmbedding: second call with null params deduplicates correctly
- bufferPendingEmbedding: null modelName/modelVersion creates row without PSQLException
- bufferPendingEmbedding: second call with null params deduplicates correctly
- Fix pre-existing constructor mismatch in ClassInstanceDiscoveryServiceTest and MLResultAcceptanceTest
Merge pull request 'test(api): regression tests for embedding null-parameter SQL bug' (#612) from test/pr611-embedding-null-regression into hotfix/worker-embedding-null-comparison
Some checks failed
Build and Deploy / Detect changed services and test scopes (push) Successful in 2s
Build and Deploy / Smoke API and media worker (push) Has been skipped
Build and Deploy / Smoke web (push) Has been skipped
Build and Deploy / Smoke ML worker (push) Has been skipped
Build and Deploy / Build plants worker image (push) Has been skipped
Build and Deploy / Build people worker image (push) Has been skipped
Build and Deploy / Build converter worker image (push) Has been skipped
Build and Deploy / Build API image (push) Has been skipped
Build and Deploy / Build media worker image (push) Has been skipped
Build and Deploy / Build web image (push) Has been skipped
Build and Deploy / Build ML worker image (push) Has been skipped
Build and Deploy / Deploy API (push) Has been skipped
Build and Deploy / Deploy web (push) Has been skipped
Build and Deploy / Deploy media worker (push) Has been skipped
Build and Deploy / Deploy ML worker (push) Has been skipped
Build and Deploy / Deploy plants worker (push) Has been skipped
Build and Deploy / Deploy people worker (push) Has been skipped
Build and Deploy / Deploy converter worker (push) Has been skipped
Build and Deploy / Clean Docker build artifacts (push) Has been skipped
Build and Deploy / Run scoped PR tests (push) Failing after 3m43s
b2a2aa26af
chris merged commit b97194ae8a into main 2026-07-14 17:09:53 +02:00
Sign in to join this conversation.
No reviewers
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
chris/memories!611
No description provided.