crate / crate/dlt-cratedb

MERGE job: `psycopg2.errors.UndefinedTable: Relation '"testdrive".d' unknown`

Open
#6 4 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
0
Forks
1
PR merge metrics
No merged PRs in 30d

Description

## Problem

Originating at dlt's `SqlJobClientBase. _create_merge_followup_jobs` and descendants, the usual merge job emits an SQL statement which fails on CrateDB.

```text
RelationUnknown[Relation 'schema.table' unknown]
```

## Details

We probed two different dialects.

### PostgreSQL dialect
```sql
DELETE
FROM "testdrive"."exchange_latest" AS d
WHERE EXISTS
(SELECT 1
FROM "testdrive_staging"."exchange_latest" AS s
WHERE d."date" = s."date"
AND d."currency_code" = s."currency_code"
AND d."base_currency" = s."base_currency");
```
```text
RelationUnknown[Relation 'doc.d' unknown]
```

### Redshift dialect
```sql
DELETE
FROM "testdrive"."exchange_latest"
WHERE EXISTS
(SELECT 1
FROM "testdrive_staging"."exchange_latest"
WHERE "testdrive"."exchange_latest"."date" = "testdrive_staging"."exchange_latest"."date"
AND "testdrive"."exchange_latest"."currency_code" = "testdrive_staging"."exchange_latest"."currency_code"
AND "testdrive"."exchange_latest"."base_currency" = "testdrive_staging"."exchange_latest"."base_currency");
```
```text
RelationUnknown[Relation 'testdrive.exchange_latest' unknown]
```

Contributor guide

No contributing guide indexed for this repository

Research direction

Start at dlt's _create_merge_followup_jobs and its descendants, then compare the PostgreSQL and Redshift SQL shown in the report with CrateDB's supported DELETE syntax. Trace how the merge follow-up statement is generated and reproduce the failing merge against CrateDB. Done means the generated merge job no longer reports an unknown relation.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sql
Domain
data-engineering, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.