Using pg_dumpall cause adding ts_insert_blocker trigger to chunk tables

[kgonia]

Relevant system information:

• OS: docker
• PostgreSQL version : 12.4
• TimescaleDB version : 1.7.3
• Installation method: using Docker

Describe the bug

I migrate from PG 11, timescale 1.7.1 to PG 12, timescale 1.7.3

I've done that by running two containers and run:

docker exec postgres-old pg_dumpall -U postgres | docker exec -i postgres-new psql -U postgres

All thing seems to work correctly. Inserts also worked. After few days I got error during inserts:

2020-09-02 13:44:03.564 UTC [164854] STATEMENT: INSERT INTO ****
2020-09-02 13:45:00.561 UTC [164907] ERROR: invalid INSERT on the root table of hypertable "_hyper_2_113_chunk"
2020-09-02 13:45:00.561 UTC [164907] HINT: Make sure the TimescaleDB extension has been preloaded.

I solve problem based on this issue and just deleted trigger on chunk tables.

Maybe is worth to look closer on this behavior?

[kodi]

We had a same issue, a we were able to remove it by using same solution.
(issue appeared after instance crash, and restoring from .sql dump)

But then, when I tried to create new view, this error happens:
ERROR: trigger "ts_cagg_invalidation_trigger" for relation "_hyper_3_1000_chunk" already exists

Does this sounds familiar? Googling for answers yields zero useful results.

[kgonia]

@kodi I didn't face this issue but after moving tables with pg dump I noticed bad performance. I moved all data to newly created table.

[NunoFilipeSantos]

@kgonia can you please try agains our latest TimescaleDB release (2.4.2) and get back to us?

[kgonia]

@NunoFilipeSantos Not really. I don't have environment to replicate.

[NunoFilipeSantos]

That's ok. 👍 Since this affects an old version we're closing the issue.
If anyone is able to reproduce it in our latest version, feel free to reopen it.

[treyenelson]

I ran into this same error:

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

The database was just migrated from PG 10 / Timescale 1.7.4 to PG 13 / Timescale 2.4.2 (multiple dumps/restores to get both postgres and timescale upgraded successfully)

During the final restore there were 3 errors, although I only have the last one:

pg_restore: error: could not execute query: ERROR: trigger "ts_insert_blocker" for relation "_hyper_1_10_chunk" already exists
Command was: CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON public.events FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker();

The solution was the same: drop the ts_insert_blocker trigger on all the chunks.

[prathamesh0]

We are facing the same issue on attempting to restore a schema dump generated using pg_dump:

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

Docker image used: timescale/timescaledb:latest-pg14
PostgreSQL version: 14.2
TimescaleDB version: 2.6.1

Command used for schema dump:
pg_dump -O -s $(DB_CONNECTION_STRING) > schema.sql

Trigger generated in the schema dump:

CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON public.blocks FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker();

Temporarily resolved the issue by manually removing the CREATE TRIGGER statements from the schema dump.

[svenklemm]

Are you following the procedure outlined in https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/#restore-your-entire-database-from-backup
Specifically are you using timescaledb_pre_restore() and timescaledb_post_restore()?

[NunoFilipeSantos]

Hi @prathamesh0 and @treyenelson ! 👋 Did you have a chance to check @svenklemm 's suggestion?

[cristovao-trevisan]

For those who have already restored the data and don't want to destroy the database and do it all again using timescaledb_pre_restore and timescaledb_post_restore correctly, you can do it by dropping ts_insert_blocker (for all tables), calling timescaledb_pre_restore, recreating ts_insert_blocker and then calling timescaledb_post_restore.

It should look like this:

DROP TRIGGER ts_insert_blocker ON device_battery;
DROP TRIGGER ts_insert_blocker ON device_temperature;
# ...


SELECT timescaledb_pre_restore();

CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON device_battery FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker();
CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON device_temperature FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker();
# ...

SELECT timescaledb_post_restore();

[MauroCL75]

And if you are running an even newer version change the trigger recreation by this:
CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON sensor_data FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker();