https://linen.dev logo
Join Slack
Powered by
Heya! Seems the `target-postgres` by MeltanoLabs i...
# troubleshooting
j
Heya! Seems the 
target-postgres
by MeltanoLabs is broken for taps that use non-lowercase stream names. For example a stream from tap named 
MY_ORACLE_SCHEMA-MY_TABLE
and with target's
Copy code
default_target_schema: source # created beforehand
the target will create a table 
<http://source.MY|source.MY>_TABLE
Then immediately will fail because it is not able to find it anymore as it expects 
<http://source.my|source.my>_table
present in DB instead. There are no currently open Github issues on https://github.com/MeltanoLabs/target-postgres/ I did find a reference to something similar in Slack thread, which seems to be merged in master and is part of v0.0.5 (released back in April). Logs and more info in thread edit: Is not reproducible with transferwise target
```❯ meltano run tap-oracle target-postgres 2023-06-25T153853.225090Z [info ] Environment 'dev' is active 2023-06-25T153858.650157Z [warning ] No state was found, complete import. 2023-06-25T153858.807022Z [info ] time=2023-06-25 173858 name=singer level=INFO message=Selected streams: ['MY_ORACLE_SCHEMA-MY_TABLE'] cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.807341Z [info ] time=2023-06-25 173858 name=singer level=INFO message=No currently_syncing found cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.807488Z [info ] time=2023-06-25 173858 name=singer level=INFO message=Beginning sync of stream(MY_ORACLE_SCHEMA-MY_TABLE) with sync method(full) cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.807680Z [info ] time=2023-06-25 173858 name=singer level=INFO message=Stream MY_ORACLE_SCHEMA-MY_TABLE is using full_table replication cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.807773Z [info ] time=2023-06-25 173858 name=singer level=INFO message=dsn: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redacted)(PORT=redacted))(CONNECT_DATA=(SERVICE_NAME=redacted))) cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.903584Z [info ] time=2023-06-25 173858 name=singer level=INFO message=select SELECT "REDACTED_1" , "REDACTED_2" ORA_ROWSCN cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.903761Z [info ] FROM MY_ORACLE_SCHEMA.MY_TABLE cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.903854Z [info ] ORDER BY ORA_ROWSCN ASC cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.917362Z [info ] time=2023-06-25 173858 name=singer level=INFO message=METRIC: b'{"type":"counter","metric":"record_count","value":150,"tags":{}}' cmd_type=elb consumer=False name=tap-oracle producer=True stdio=stderr string_id=tap-oracle 2023-06-25T153858.932686Z [info ] time=2023-06-25 173858 name=target_postgres level=INFO message=Table '"my_table"' exists cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres 2023-06-25T153858.954964Z [info ] time=2023-06-25 173858 name=target_postgres level=INFO message=Versioning column: ALTER TABLE source."my_table" RENAME COLUMN "redacted" TO "redacted_20230625_1738" cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres 2023-06-25T153858.964216Z [info ] Traceback (most recent call last): cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres 2023-06-25T153858.964346Z [info ] File "/radacted/project/.meltano/loaders/target-postgres/venv/bin/target-postgres", line 8, in <module> cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres 2023-06-25T153858.964597Z [info ] sys.exit(main()) cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres 2023-06-25T153858.964690Z [info ] File "/radacted/project/.meltano/loaders/target-postgres/venv/lib/python3.10/site-packages/target_postgres/__init__.py", line 373, in main cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres 2023-06-25T153858.964755Z [info ] persist_lines(config, singer_messages) cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres 2023-06-25T153858.964840Z [info ] …
meltano.yml
Copy code
version: 1
default_environment: dev
project_id: b1d562d0-d114-42a7-bad1-957891a7285b

environments:
  - name: dev
  - name: uat
  - name: prod

plugins:
  extractors:
    - name: tap-oracle
      pip_url: git+<https://github.com/christianelliott/pipelinewise-tap-oracle.git>
      config:
        filter_schemas: MY_ORACLE_SCHEMA
        default_replication_method: FULL_TABLE
        port: redacted
        host: redacted
        user: redacted
        service_name: redacted
        use_ora_rowscn: true
        add_metadata_columns: true
      select:
        - MY_ORACLE_SCHEMA-MY_TABLE.*
  loaders:
    - name: target-postgres
      variant: meltanolabs
      pip_url: git+<https://github.com/MeltanoLabs/target-postgres.git>
      config:
        host: localhost
        port: 5432
        dbname: postgres
        user: redacted
        default_target_schema: source
        add_record_metadata: false
        hard_delete: false
        batch_size_rows: 100000
u
@janis_puris do you have the latest version of target-postgres installed? I know there were some bug fixes related to this type of stuff recently 
meltano install loader target-postgres --clean
. cc @visch any ideas? I was dealing with some of this recently in target-snowflake. I'm not seeing where this rename logic is actually being called but it looks like https://github.com/meltano/sdk/blob/c6a893372d036a99da81ee9131b8b6e17f50e670/singer_sdk/connectors/sql.py#L1001 is where the invalid sql is generated.
j
The project was created last night, and all I did was
Copy code
meltano add loader target-postgres --variant=meltanolabs
I can spin up a new one and try to reproduce it, if needed. Is there some way to check the installed plugin version? thinkspin
v
I think I see it
Copy code
17:38:58 name=target_postgres level=INFO message=Versioning column: ALTER TABLE source."my_table" RENAME COLUMN "redacted" TO "redacted_20230625_1738" cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres
Yeah I didn't migrate the Alter Table logic... I'll add a test and get this fixed. Give me 15 minutes
@janis_puris Are you sure you're using the meltanolabs variant? That alter column call isn't a thing in the meltanolabs variant. Looking at the stack trace closer
Copy code
2023-06-25T15:38:58.965794Z [info     ]   File "/radacted/project/.meltano/loaders/target-postgres/venv/lib/python3.10/site-packages/target_postgres/db_sync.py", line 311, in query cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres
2023-06-25T15:38:58.965881Z [info     ]
db_sync.py
isn't a file in the meltano labs variant. Maybe you need to re-run a fresh install ie 
meltano install --clean
I did find some other issues but it's not exactly this one, I'll still address it but it's not quite this issue!
Double checking my assumptions here! Just wanted to share what I thought so far
j
I'm able to reproduce it with a fresh meltano project 😕
Copy code
plugins:
  extractors:
    - name: tap-oracle
      pip_url: git+<https://github.com/christianelliott/pipelinewise-tap-oracle.git>
...
  loaders:
    - name: target-postgres
      variant: meltanolabs
      pip_url: git+<https://github.com/MeltanoLabs/target-postgres.git>
      config:
...
then
Copy code
meltano install --clean 
meltano run tap-oracle target-postgres
v
Can you share the logs again?
I think those logs you're sharing aren't from the same project maybe they are old? The stack trace doesn't match. There is an issue in the current target-postgres with altering columns and stream name issues but it's not exactly that logs you posted 😕 I'll still work to fix the issue I see but I"d really like to see your logs to make sure I"m fixing the same thing!
j
Sure, need a moment to clean them up.. they are little too verbose 😄
v
thank you!
j
Copy code
❯ meltano run tap-oracle target-postgres
the only thing I've redacted are the 
dsn
args, select query and the local fs path to meltano project. On PostrgeSQL running on localhost I see it has recreated the table from scratch (I had removed it prior to running this) and it is named 
CHANNEL
while it expects to see 
channel
edit: error
Copy code
2023-06-26T16:54:26.764388Z [info     ] sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "source__icx.channel" does not exist cmd_type=elb consumer=True name=target-postgres producer=False stdio=stderr string_id=target-postgres
Untitled
If it helps, I can spend some time on an MRE, but may be tricky to do that with an Oracle source :3
v
Very helpful, those are the correct logs from the meltanolabs variant now! I'll work on the fix, I was hoping I wouldn't have to do this part 😕 shouldn't take too long
j
On PostgreSQL
Copy code
SELECT
    name
FROM information_schema.tables
WHERE
    table_schema = 'source__icx';
gets me
Copy code
[
  {
    "table_name": "CHANNEL"
  }
]
v
just so you're updated, I have this fixed I"m just trying to get another bug fixed that isn't related to this but it's tangential that's why it's taking a bit
I'll let you know when you can run a new install sorry you have to deal with this 😕
Open in Slack
PreviousNext
Powered by