无法在Rails测试环境中使用TimescaleDB [英] Unable to use TimescaleDB in Rails test environment
问题描述
我一直在Rails中使用TimescaleDB-开发中一切正常，但是在测试套件中我无法插入任何数据。
I'm stuck using TimescaleDB in Rails - everything works fine in development, but in my test suite I cannot insert any data.
这会导致我看到原始错误消息。它确实为TimescaleDB创建了部分架构，但并非全部。我有一个超表，但不能正常工作
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
这可以让我插入我的表，但它根本不是一个超表-ruby语法使与TimescaleDB和超表有关的所有内容丢失。
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.
我尝试避免使用以下内容进行schema.structure转储和加载：
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.
但是运行测试套件时，它与尝试A相同。
But when running the test suite it is the same as attempt A.
在实际打印此消息几次后运行测试，这使我认为Rails再次自动使用 structure.sql 来重新创建测试数据库：
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.
错误消息
Error message
$ 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
我有感觉这与Rails如何使用 schema.rb 创建测试数据库有关（默认情况下 config.active_record.schema_format =：ruby ）或 structure.sql （对于 config.active_record.schema_format =：sql 。
我已经尝试了该结构的Ruby和SQL设置，但都无法正常工作-开发数据库已正确迁移，但测试数据库未正确设置。
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.
在下面的两个数据库中（开发和测试），我们可以看到的唯一区别是缺少测试数据库：子表：_timescaledb_internal._hyper_1_1_chunk
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
测试数据库
Test DB
$ 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
具有SQL模式的ActiveRecord
ActiveRecord with SQL schema
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();
具有Ruby模式的ActiveRecord
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
注意：这会松动 ts_insert_blocker 触发器，让我插入 events 表，但它不再是超表了：
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
其他信息
相关问题：使用Rails 4.2在TimescaleDB数据库上运行RSpec测试套件-建议对我不起作用，因此不被接受答案。
Additional information
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.
版本信息：
- Rails 6.0.3
- Postgres 12.2
- TimescaleDB 1.7.0
我在 test / test_helper.rb 中添加了以下内容，类似于@cstabru
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
但是也许我们可以使用 SELECT create_hypertable（在初始化程序中使用'hypertable_name'，'time_field'，if_not_exists => TRUE 而不是在数据库迁移中创建超表？
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?
推荐答案
我也遇到了这种情况，无论我以哪种方式重新创建db模式（sql或ruby格式），都不会重新创建超级表，因为不会导出时标内部模式数据。
I ran into this as well, no matter which way i recreate the db schema (sql or ruby formats) the hyper table is not recreated as the timescale internal schema data is not exported.
请注意，当我使用 sql 格式还原时，它将跨 ts_insert_blocker 复制确实会因此错误而中断插入表的触发器（我相信是由于触发器功能不可用）
Noting that when I restore using the sql format, it copies across the ts_insert_blocker trigger which indeed break inserts on the table with this error (I believe is due to the trigger function not being available)
PG::FeatureNotSupported: ERROR: invalid INSERT on the root table of hypertable "hypertable_name"
HINT: Make sure the TimescaleDB extension has been preloaded.
要修复基本问题（sql或ruby格式），我们可以通过以下操作手动重新创建超表（并删除触发器）
To fix the underlying issue (either sql or ruby formats) we can recreate the hypertable (and removing the trigger) manually via the following
DROP TRIGGER IF EXISTS ts_insert_blocker ON events;
DROP TRIGGER
SELECT create_hypertable('hypertable_name', 'time_field', if_not_exists => TRUE);
....
(1 row)
现在手动检查自> https://github.com/timescale/timescaledb/pull/862
SELECT * FROM timescaledb_information.hypertable;
我已将这些DDL命令添加到我的 spec_helper.rb 以确保测试数据库使用实际的超表。我想确保测试数据库架构能够反映我的生产/暂存设置。
I've added these DDL commands to my spec_helper.rb to ensure the test db uses an actual hypertable. I want to ensure the test db schema mirrors my production / staging setups.
config.before(:suite) do
# ensure the hypertable_name hypertable is setup correctly
ActiveRecord::Base.connection.execute(
"DROP TRIGGER IF EXISTS ts_insert_blocker ON hypertable_name;"
)
ActiveRecord::Base.connection.execute(
"SELECT create_hypertable('hypertable_name', 'time_field', if_not_exists => TRUE);"
)
has_hypertables_sql = "SELECT * FROM timescaledb_information.hypertable WHERE table_name = 'hypertable_name';"
if ActiveRecord::Base.connection.execute(has_hypertables_sql).to_a.empty?
raise "TimescaleDB missing hypertable on 'hypertable_name' table"
end
end
如果人们觉得这很有用，我可以看一下提取到gem以帮助进行Rails环境的架构还原， https://github.com/timescale/timescaledb/issues/1916
If folks find this useful I can look at extracting to a gem to help with schema restores for rails environments, https://github.com/timescale/timescaledb/issues/1916
这篇关于无法在Rails测试环境中使用TimescaleDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
