I'm stuck using TimescaleDB in Rails - everything works fine in development, but in my test suite I cannot insert any data.
This causes the original error message I saw. It does create parts of the schema for TimescaleDB but not all of it. I have a hypertable but it's not working properly
This lets me insert into my table but it's not a hypertable at all - the ruby syntax looses everything related to TimescaleDB and hypertables.
I tried avoiding the schema.structure dump and load with the following:
$ rails db:drop
Dropped database 'my_app_development'
Dropped database 'my_app_test'
$ RAILS_ENV=test rails db:create
Created database 'my_app_test'
$ RAILS_ENV=test rails db:migrate
== 20200517164444 EnableTimescaledbExtension: migrating =======================
-- enable_extension("timescaledb")
WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.7.0
For more information on TimescaleDB, please visit the following links:
1. Getting started: https://docs.timescale.com/getting-started
2. API reference documentation: https://docs.timescale.com/api
3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
-> 0.2315s
== 20200517164444 EnableTimescaledbExtension: migrated (0.2316s) ==============
== 20200517165027 CreateAccounts: migrating ===================================
-- create_table(:accounts)
-> 0.0095s
== 20200517165027 CreateAccounts: migrated (0.0095s) ==========================
== 20200517165103 CreateMetrics: migrating ====================================
-- create_table(:metrics)
-> 0.0116s
== 20200517165103 CreateMetrics: migrated (0.0117s) ===========================
== 20200517170842 CreateEvents: migrating =====================================
-- create_table(:events)
-> 0.0072s
-- remove_column(:events, :id)
-> 0.0020s
-- execute("SELECT create_hypertable('events', 'time');\n")
-> 0.0047s
== 20200517170842 CreateEvents: migrated (0.0142s) ============================
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: hypertable
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: chunk
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
But when running the test suite it is the same as attempt A.
Running the tests after actually prints this message a few times which makes me think that Rails auto-magically uses the structure.sql again to recreate the test DB:
psql:/home/axel/src/my_app/db/structure.sql:16: WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.7.0
For more information on TimescaleDB, please visit the following links:
1. Getting started: https://docs.timescale.com/getting-started
2. API reference documentation: https://docs.timescale.com/api
3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
$ rails test
Running via Spring preloader in process 107937
Run options: --seed 29840
# Running:
E
Error:
Api::EventsControllerTest#test_POST_event_data_-_new_metric:
DRb::DRbRemoteError: PG::FeatureNotSupported: ERROR: invalid INSERT on the root table of hypertable "events"
HINT: Make sure the TimescaleDB extension has been preloaded.
(ActiveRecord::StatementInvalid)
app/controllers/api/events_controller.rb:5:in `create'
test/controllers/api/events_controller_test.rb:9:in `block in <class:EventsControllerTest>'
rails test test/controllers/api/events_controller_test.rb:8
Finished in 0.215286s, 4.6450 runs/s, 0.0000 assertions/s.
1 runs, 0 assertions, 0 failures, 1 errors, 0 skips
I have the feeling it's related to how Rails creates the test database using the schema.rb (for default config.active_record.schema_format = :ruby) or structure.sql (for config.active_record.schema_format = :sql.
I already tried both, the Ruby and SQL setting of the structure and neither works - development DB gets migrated correctly but test DB is not set up correctly.
In the two databases below (development and test) we can see the only difference is that the test DB is missing: Child tables: _timescaledb_internal._hyper_1_1_chunk
$ psql -d my_app_development
psql (12.2)
Type "help" for help.
my_app_development=# SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
timescaledb
(1 row)
my_app_development=# insert into events (metric_id, time, value) VALUES (1, NOW(), 22);
INSERT 0 1
my_app_development=# \d+ events
Table "public.events"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
metric_id | bigint | | | | plain | |
time | timestamp without time zone | | not null | | plain | |
value | numeric | | | | main | |
Indexes:
"events_time_idx" btree ("time" DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON events FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_1_1_chunk
Access method: heap
$ psql -d my_app_test
psql (12.2)
Type "help" for help.
my_app_test=# SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
timescaledb
(1 row)
my_app_test=# insert into events (metric_id, time, value) VALUES (1, NOW(), 22);
ERROR: invalid INSERT on the root table of hypertable "events"
HINT: Make sure the TimescaleDB extension has been preloaded.
my_app_test=# \d+ events
Table "public.events"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
metric_id | bigint | | | | plain | |
time | timestamp without time zone | | not null | | plain | |
value | numeric | | | | main | |
Indexes:
"events_time_idx" btree ("time" DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON events FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Access method: heap
CREATE EXTENSION IF NOT EXISTS timescaledb WITH SCHEMA public;
SET default_tablespace = '';
SET default_table_access_method = heap;
CREATE TABLE public.events (
metric_id bigint,
"time" timestamp without time zone NOT NULL,
value numeric
);
CREATE INDEX events_time_idx ON public.events USING btree ("time" DESC);
CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON public.events FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker();
ActiveRecord with Ruby schema
ActiveRecord::Schema.define(version: 2020_05_17_170842) do
# These are extensions that must be enabled in order to support this database
enable_extension "plpgsql"
enable_extension "timescaledb"
create_table "events", id: false, force: :cascade do |t|
t.bigint "metric_id"
t.datetime "time", null: false
t.decimal "value"
t.index ["time"], name: "events_time_idx", order: :desc
end
end
Note: this looses the ts_insert_blocker trigger and lets me insert into the events table but it is not a hypertable anymore:
my_app_test=# \d+ events
Table "public.events"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
metric_id | bigint | | | | plain | |
time | timestamp without time zone | | not null | | plain | |
value | numeric | | | | main | |
Indexes:
"events_time_idx" btree ("time" DESC)
Access method: heap
Related question: Running an RSpec test suite against a TimescaleDB database with Rails 4.2 - The suggestions did not work for me and there is no accepted answer.
Version information:
I added the following to my test/test_helper.rb similar to the workaround mentioned by @cstabru
def execute_create_hypertable(sql)
ActiveRecord::Base.connection.execute(sql)
rescue ActiveRecord::StatementInvalid => e
raise e unless e.message.include? 'is already a hypertable'
end
execute_create_hypertable <<~SQL
SELECT create_hypertable('events', 'time');
SQL
But maybe we can use something like SELECT create_hypertable('hypertable_name', 'time_field', if_not_exists => TRUE in an initializer instead of creating hypertables in DB migrations?
Also, if you're looking for more modern solution, you can just use the timescaledb gem.
And the SchemaDumper behavior will also introduce it properly in the db/schema.rb.