TimescaleListener - Failed to write user count to Postgresql: FeatureNotSupported
StasGrishaev opened this issue · 25 comments
Hi there @cyberw !
I spent quite a bit of time trying to add your plugin - TimescaleListener to display graphs in Grafana. and I am tightly stuck on an INSERT table
what i tried:
- creating a new PG database via pgAdmin and from the command line via psql (9 and 11 versions with the corresponding timescale version of the plugin) from your dump listeners_timescale_table.sql, after restarting the PG server (judging by the logs everything was created successfully)
screenshot error stack, selected text - values for INSERT operation
What am I missing and what can I try?
Did you install the timescale extension correctly? Try googling the message you got: "make sure the timescaledb extension has been preloaded"
Also, I notice you are on windows. While it could work, you are pretty much on your own there :-/
@cyberw thanks for quick response !
Did you install the timescale extension correctly?
yeah, i believe so.
how do you suggest apply dump from listeners_timescale_table.sql ? by psql CLI or by pgAdmin web interface ? i suspect that may be the root of the problem in my case
CLI or web interface shouldnt really matter. Have you done these steps: timescale/timescaledb#550 (comment) ?
I run into this issue as well.
my postgres.conf contains shared_preload_libraries= 'timescaledb' entry
and select * from pg_extension returns timescaledb.
Hmm.. Lots of people seem to be having this issue with timescale (not just users of locust-plugins). Have you checked: https://docs.timescale.com/latest/using-timescaledb/troubleshooting
CLI or web interface shouldnt really matter. Have you done these steps: timescale/timescaledb#550 (comment) ?
yep.
@ludeknovy are you also on Windows ?
maybe @StasGrishaev was on to something about which tool should be used?
Error updating TimescaleDB when using a third-party PostgreSQL admin tool.
The update command ALTER EXTENSION timescaledb UPDATE must be the first command executed upon connection to a database. Some admin tools execute command before this, which can disrupt the process. It may be necessary for you to manually update the database with psql. See our update docs for details.
@StasGrishaev no, I was running timescale in docker
alright guys, i made it ! the thing is a triggers - timescale/timescaledb#1381
after i manually disabled these triggers through pgAdmin in request, testrun and user_count tables ( example on screenshot ) everything started to work. @cyberw thanks again for great listener !
@cyberw it would be nice to add this info to documentation or maybe directly to sql dump file as a comment, how do you think ?
@cyberw it would be nice to add this info to documentation or maybe directly to sql dump file as a comment, how do you think ?
I think that is a great idea. I think the documentation is the right place to put it (as the dump might need to be regenerated at some point). Maybe we should also reference this ticket.
Can you do it and file a PR? It would be really appreciated :) Or I can do it.
Can you do it and file a PR? It would be really appreciated :) Or I can do it.
sure, i will do it. also close the issue
The trigger can be removed by running this query:
DROP TRIGGER ts_insert_blocker ON public.request
@cyberw may I ask whats the purpose of using timescaleDB if you dont use hypertable(s)?
When I import timescale_schema.sql and run select * from _timescaledb_catalog.hypertable it returns 0 records. Do I miss something?
requests is meant to be a hypertable. Maybe the fix suggested by Stas is wrong?
@cyberw Well, I did not find any other way to fix the FeatureNotSupported issue other than removing the triggers as suggested by Stas. Actually the insert block triggers are expected to happen as described in one of the issue in timescale repo. As per timescale docs, after importing the backup you are supposed to recreate the hypertables. But again when I try to run SELECT create_hypertable('request', 'time') it fails because of No function matches the given name and argument types.
I have the timescale db extension installed according to SELECT * FROM pg_extension.
Sorry, I'm not very good at timescale setup so I'm not sure how to help. I guess either there is something wrong in your setup, or my dump is somehow incomplete.
All the tables are supposed to be hypertables though.
postgres=# select * from _timescaledb_catalog.hypertable;
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size
----+-------------+------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------
2 | public | request | _timescaledb_internal | _hyper_2 | 1 | _timescaledb_internal | calculate_chunk_interval | 0
3 | public | testrun | _timescaledb_internal | _hyper_3 | 1 | _timescaledb_internal | calculate_chunk_interval | 0
4 | public | user_count | _timescaledb_internal | _hyper_4 | 1 | _timescaledb_internal | calculate_chunk_interval | 0
This is the outcome on your local db after importing the dump? Or is this the state of your db where from which you made the actual dump?
this is the state of my db from which I made the dump. Gimme a sec and I'll try making a new dump just to try that...
Thats what I thought! Try to make it please according to https://docs.timescale.com/latest/using-timescaledb/backup#pg_dump-pg_restore
This file is not actually zipped I just renamed it for github to accept it.
I created it using pg_dump -s -Fc -f locust.bak postgres
When I tried to restore the locust.bak I got: pg_restore: warning: errors ignored on restore: 580 :/ Which results in the same behavior as described above.
Could you please try pg_dump -s -d old_db --table conditions -N _timescaledb_internal | \ grep -v _timescaledb_internal > schema.sql for each table?
tables.zip (an actual zip file)
timescale documentation mentioned that restoring on a different version might cause issues. I'm on version 1.3.1.
The restore did not work for me :/. I've created new schema.sql and it works out of the box in docker.
and then in your docker file:
FROM timescale/timescaledb:latest-pg11
COPY schema.sql /docker-entrypoint-initdb.d/
Looks like that docker image is using timescale version 2.0.0-rc4, which is probably not compatible with my version (1.3.1)