[extension/storage/dbstorage] pgx support not wokring because db queries are sqlite specific

[danelson]

Component(s)

extension/storage/dbstorage

What happened?

Description

The dbstorage extension says that both sqlite3 and pgx are supported.

driver: the name of the database driver to use. By default, the storage client supports "sqlite3" and "pgx".

https://github.com/open-telemetry/opentelemetry-collector-contrib/blob/v0.110.0/extension/storage/dbstorage/README.md?plain=1#L21

However when using the pgx driver, the create table statement uses syntax specific to sqlite3 which causes a failure

error graph/graph.go:425 Failed to start component {"error": "error connecting to storage: ERROR: type "blob" does not exist (SQLSTATE 42704)", "type": "Receiver", "id": "sqlquery"}

The queries in https://github.com/open-telemetry/opentelemetry-collector-contrib/blob/main/extension/storage/dbstorage/client.go#L20-L23 look specific to sqlite3.

Steps to Reproduce

See collector configuration below

Expected Result

I would expect there not to be an error and that the tracking table be created

Actual Result

error graph/graph.go:425 Failed to start component {"error": "error connecting to storage: ERROR: type "blob" does not exist (SQLSTATE 42704)", "type": "Receiver", "id": "sqlquery"}

Collector version

v0.110.0
Custom collector distro running on alpine compiled with go 1.22

OpenTelemetry Collector configuration

receivers:
  sqlquery:
    driver: postgres
    datasource: "host=localhost port=5432 user=postgres password=s3cr3t sslmode=disable"
    queries:
      - sql: "select * from my_logs where log_id > $$1"
        tracking_start_value: "1" # Not sure if this is correct yet but it should not matter for this issue
        tracking_column: log_id
        logs:
          - body_column: log_body

exporters:
  debug:
    verbosity: detailed

extensions:
  db_storage:
    driver: pgx
    datasource: "host=localhost port=5432 user=postgres password=s3cr3t sslmode=disable"

service:
  extensions: [db_storage]
  pipelines:
    logs:
      receivers: [sqlquery]
      exporters: [debug]

Log output

error   graph/graph.go:425      Failed to start component       {"error": "error connecting to storage: ERROR: type \"blob\" does not exist (SQLSTATE 42704)", "type": "Receiver", "id": "sqlquery"}

Additional context

Once this is fixed I think there may also be an issue with the tracking table name as I think it will include invalid characters (at least when using sqlqueryreceiver). This is inferring the table name from the code which I might not being doing right. I can't test with sqlite3 due to #35280

[github-actions]

Pinging code owners:

• extension/storage/dbstorage: @dmitryax @atoulme

See Adding Labels via Comments if you do not have permissions to add labels yourself.

[atoulme]

Thanks for the report. We can look into it.