Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Using pg_dumpall cause adding ts_insert_blocker trigger to chunk tables #2304

Open
kgonia opened this issue Sep 2, 2020 · 11 comments
Open

Comments

@kgonia
Copy link

kgonia commented Sep 2, 2020

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
Copy link

kodi commented Oct 11, 2020

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
Copy link
Author

kgonia commented Oct 21, 2020

@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
Copy link
Contributor

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

@kgonia
Copy link
Author

kgonia commented Oct 19, 2021

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

@NunoFilipeSantos
Copy link
Contributor

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
Copy link

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
Copy link

prathamesh0 commented Apr 25, 2022

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
Copy link
Member

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
Copy link
Contributor

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

@cristovao-trevisan
Copy link

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
Copy link

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();

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

9 participants