ERROR: invalid INSERT on the root table of hypertable "table" HINT: Make sure the TimescaleDB extension has been preloaded.

[FANHIDE]

I use the docker image : timescale/timescaledb:latest-pg10
but when i insert into table.the error is

[t0k4rt]

Same issue here, it was working yesterday and today, everything inserts started to fail.

[shubhamdipt]

Same issue with me as well.

[neowulf]

What's the recommended way to bootstrap the timescaldb with the hypertables within a docker container?

[nemmeviu]

+1

[ronnyek]

I started experiencing this as well after performing pg_dump/pg_restore per the instructionms on the site.

CREATE DATABASE tutorial;
\c tutorial --connect to the db where we'll perform the restore
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT timescaledb_pre_restore();

-- execute the restore (or from a shell)
\! pg_restore -Fc -d tutorial tutorial.bak


SELECT timescaledb_post_restore();

Yet no inserts can successfully write to that table.
I did get a fair number warnings in both executing the backup and executing the restore. About circular references, and that I should not be using --data-only (even though I was using Fc.

I've got concerns about timescaledb if I can't reliably backup/restore the db.

[DmitrySidorow]

I started experiencing this as well after performing pg_dump/pg_restore per the instructionms on the site.

CREATE DATABASE tutorial;
\c tutorial --connect to the db where we'll perform the restore
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT timescaledb_pre_restore();

-- execute the restore (or from a shell)
\! pg_restore -Fc -d tutorial tutorial.bak


SELECT timescaledb_post_restore();

Yet no inserts can successfully write to that table.
I did get a fair number warnings in both executing the backup and executing the restore. About circular references, and that I should not be using --data-only (even though I was using Fc.

I've got concerns about timescaledb if I can't reliably backup/restore the db.

In my case the reason was that the backup was version 1.7, and restored to 2.0

[akashagarwal7]

Same issue here with PG 11 and timescale 1.5.1. Source DB and target DB for backup and restore respectively have the same timescale extension on public schema. Restoring using psql instead of pg_restore as per the instructions here.

[svenklemm]

Closing this since those are very old versions. Problems with backup/restore should better be reported to the main repo as they will get more visibility there.

[wapmorgan]

This problem stil exists on postgres 14

[vuongphamaioz]

I encountered the same issue. The solution is https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/ as #86 (comment).

[th0mk]

This issue still happens on the current version. I had it yesterday after doing a database migration with Timescale their hypershift tool (which is basically a faster way of dumping and restoring)

Some notes on this:

• I made sure that both Postgres and Timescale were running matching versions on the source and target database:
  

• I performed a test run under the exact same conditions, and the issue did not occur

• No new data was being inserted during the migration process

• Timescale's hypershift tool reported no errors to me:
  

After I switched, this started happening:

As a result, I had to roll back the production migration and revert to the source database, which was quite painful. While I should probably have checked if inserts were working before fully switching over, I find it concerning that I'm unable to reliably migrate a production database even when utilizing Timescale's own tools. My only solution right now seems to move the data away from Timescale and try again.

[AndreMaz]

@svenklemm can you please re-open this ticket?

I have exactly the same issue as @th0mk

Migrated with hypershift from Timescale Managed to Timescale Cloud
Migration result:

Tables @ Timescale Cloud

and yet I'm getting

ERROR:  invalid INSERT on the root table of hypertable "_hyper_1_76_chunk"
HINT:  Make sure the TimescaleDB extension has been preloaded.

Chunk is here

Context:
PostgreSQL: v15.3
TimescaleDB: v2.11.1

[AndreMaz]

Just leaving here the steps that, apparently, solved my issue

1. psql "postgres://tsdbadmin:<TARGET-PASSWORD>@<TARGET-URL>:<TARGET-PORT>/tsdb?sslmode=require"
2. DROP SCHEMA public CASCADE;
3. CREATE SCHEMA public;
4. psql -X "postgres://tsdbadmin:<TARGET-PASSWORD>@<TARGET-URL>:<TARGET-PORT>/tsdb?sslmode=require" // Don't forget to add -X
5. CREATE EXTENSION IF NOT EXISTS timescaledb;
6. docker run -v $PWD/config.yml:/config.yml -ti timescale/hypershift:0.6 clone -s='host=<SOURCE-URL> dbname=<SOURCE-DB-NAME> port=<SOURCE-PORT> user=tsdbadmin password=<SOURCE-PASSWORD>' \ -t='host=<TARGET-URL> dbname=tsdb port=<TARGET-PORT> user=tsdbadmin password=<TARGET-PASSWORD>' -c='/config.yml'

YAML config for hypershift:

verify: true
create_db: false

Note: I'm not sure why I had to create the timescaledb extension. Running the \dx timescaledb showed that timescaledb v2.11.1 was enabled. It might be a bug in hypershift

[th0mk]

Thanks for the extra info. I had already installed the timescaledb extension in the target database prior to running the hypershift migration.

[AndreMaz]

That's the strange part because I also enabled timescaledb before migration.

I called CREATE EXTENSION IF NOT EXISTS timescaledb; and got this:

NOTICE: extension "timescaledb" already exists, skipping

then called \dx timescaledb and got this

                                              List of installed extensions
    Name     | Version | Schema |                                      Description                                      
-------------+---------+--------+---------------------------------------------------------------------------------------
 timescaledb | 2.11.1  | public | Enables scalable inserts and complex queries for time-series data (Community Edition)

I'm not sure what's going on but something isn't right

[pushpeepkmonroe]

psql (12.6 (Ubuntu 12.6-1.pgdg18.04+1), server 12.15 (Ubuntu 12.15-1.pgdg18.04+1))
You are now connected to database "pushnami_stats" as user "postgres".
pushnami_stats=# insert into public.hourly_delivered_new (select * from public.hourly_delivered);
ERROR: invalid INSERT on the root table of hypertable "_materialized_hypertable_600"
HINT: Make sure the TimescaleDB extension has been preloaded.
pushnami_stats=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-------------------------------------------------------------------
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.10.0 | public | Enables scalable inserts and complex queries for time-series data
(3 rows)

[d4munche3z]

I am having this issue also and pgadmin shows no inserts when I have been expecting many inserts.
Am getting the following:
(psycopg2.errors.FeatureNotSupported) invalid INSERT on the root table of hypertable "_hyper_4_228_chunk"
HINT: Make sure the TimescaleDB extension has been preloaded.

[AndreMaz]

Just in case someone needs this.
After migrating another DB I started to see the same error. Did some more reading and found this: timescale/timescaledb#1298 (comment)

Then checked my log messages again and found this

ERROR: invalid INSERT on the root table of hypertable "_hyper_1_88_chunk"

Then checked the constrains of the _timescaledb_internal._hyper_1_88_chunk and saw that it had the ts_insert_blocker, which is something that it's not supposed to have according to the comment above.

Solution: I manually removed the ts_insert_blocker and things started to work again.

[Vin0uz]

Just faced this, thanks for the notes even on closed issue 🤓

For those who wouldn't know, the query to execute was
DROP TRIGGER ts_insert_blocker ON <name_of_your_table>;

You can run it in rails db directly

(Remember to replace <name_of_your_table> with name of the concerned table 😇)

[paretl]

Just in case someone needs this. After migrating another DB I started to see the same error. Did some more reading and found this: timescale/timescaledb#1298 (comment)

Then checked my log messages again and found this

ERROR: invalid INSERT on the root table of hypertable "_hyper_1_88_chunk"

Then checked the constrains of the _timescaledb_internal._hyper_1_88_chunk and saw that it had the ts_insert_blocker, which is something that it's not supposed to have according to the comment above.

Solution: I manually removed the ts_insert_blocker and things started to work again.

Thanks @AndreMaz , that was the issue for me as well !