Adapter triggers "CREATE TABLE metrics_labels" every start

[r0b2g1t]

If I start an adapter that should use an existing database, there is the following error message in the Postgres Log.

2019-09-06 12:44:31.230 UTC [16399] ERROR:  relation "metrics_labels" already exists
2019-09-06 12:44:31.230 UTC [16399] CONTEXT:  SQL statement "
                    CREATE TABLE metrics_labels (
                          id SERIAL PRIMARY KEY,
                          metric_name TEXT NOT NULL,
                          labels jsonb,
                          UNIQUE(metric_name, labels)
                    )
                    "
        PL/pgSQL function create_prometheus_table(name,name,name,name,name,boolean,boolean,interval) line 45 at EXECUTE
2019-09-06 12:44:31.230 UTC [16399] STATEMENT:  SELECT create_prometheus_table($1, normalized_tables => $2, chunk_time_interval => $3,  use_timescaledb=> $4)

This could be solved with a simple function that checks if the database already exists before setup.

[bboule]

@r0b2g1t thanks for reporting this, we will take a look and update this issue with what we find.

[r0b2g1t]

Is there an update for this issue?

[krisavi]

It does try to create the table, but if it exists it continues. However that is a problem how the check is performed. https://github.com/timescale/prometheus-postgresql-adapter/blob/7b6d24096ce522a72065c0cf1e0460e00c465a35/pkg/postgresql/client.go#L172 This is not the correct way. For example if your DB locale does not happen to be English the error will cause adapter to stop.

For me it is Swedish and I get to error: {"caller":"log.go:39","err":"pq: relationen "metrics_labels" finns redan","level":"error","ts":"2020-01-23T09:14:10.299Z"}

"pq: relationen "metrics_labels" finns redan" unfortunately does not contain "already exists". Well to be honest, in a way it does, but in translation only.

The check has to be language independent. Probably better way would be to check if table exists before trying to even create it.

For example you could do something like: SELECT * FROM pg_catalog.pg_tables WHERE tablename like '%labels';

or

SELECT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_schema = 'schema_name' AND table_name = 'table_name' );

Code snippet from 1 minute search: https://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema