create_hypertable(migrate_data => true) does not create indexes for chunks.

[Guzya]

Relevant system information:

• OS: Debian 10
• PostgreSQL version (output of postgres --version): PostgreSQL 11.7 (Debian 11.7-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
• TimescaleDB version (output of \dx in psql): 1.7.2
• Installation method: "apt install"

Describe the bug create_hypertable(migrate_data => true) does not create indexes for chunks

To Reproduce Steps to reproduce the behavior:

1. creating a table with test data like this: create table test4 (id integer, timest timestamp, value real, comment text); do language plpgsql $$ DECLARE r record; begin FOR i IN 1..100
   LOOP FOR r IN select ts as timest,random() as value from generate_series('2010-01-31', '2018-05-31', interval '1 day') as gs(ts) LOOP EXECUTE 'insert into test4 (id, timest, value, comment) values(' || i ||','''|| r.timest || ''',' || r.value ||',''text_2'')'; END LOOP; END LOOP; end $$;

2. creating a hypertable with migration: select create_hypertable('test4','timest',chunk_time_interval => interval '1 day',migrate_data => true, create_default_indexes => true);

3. check timescale_db=# \d test4 Таблица "public.test4" Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию ---------+-----------------------------+--------------------+-------------------+-------------- id | integer | | | timest | timestamp without time zone | | not null | value | real | | | comment | text | | | Индексы: "test4_timest_idx" btree (timest DESC) Триггеры: ts_insert_blocker BEFORE INSERT ON test4 FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker() Дочерних таблиц: 3043 (чтобы просмотреть и их, воспользуйтесь \d+)

select * from timescaledb_information.hypertable where table_name = 'test4' \gx -[ RECORD 1 ]--+--------- table_schema | public table_name | test4 table_owner | postgres num_dimensions | 1 num_chunks | 3043 table_size | 24 MB index_size | 0 bytes toast_size | 24 MB total_size | 48 MB

4. there is no index on chunk

select * from show_chunks('test4') limit 1; show_chunks

_timescaledb_internal._hyper_14_60405_chunk

timescale_db=# \d _timescaledb_internal._hyper_14_60405_chunk Таблица "_timescaledb_internal._hyper_14_60405_chunk" Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию ---------+-----------------------------+--------------------+-------------------+-------------- id | integer | | | timest | timestamp without time zone | | not null | value | real | | | comment | text | | | Ограничения-проверки: "constraint_60405" CHECK (timest >= '2010-01-31 00:00:00'::timestamp without time zone AND timest < '2010-02-01 00:00:00'::timestamp without time zone) Наследует: test4

Expected behavior index creation.

Actual behavior indexes are not created.

Screenshots

All tables are created from a single reference table. test2_for_insert created through the creation of empty hyperstability and insert rows. Additional context

[NunoFilipeSantos]

Hi @Guzya can you please try our latest TimescaleDB (2.4.2) with a supported PG version (12 or 13) and get back to us?

[Guzya]

Hello! We are using Postgresql 11.11. Checked with timescaledb 2.3.2, the problem is reproducible.

[NunoFilipeSantos]

Hi @Guzya can you please upgrade to PG12 or PG13 and use our latest 2.4.2?

[Guzya]

Use docker:

docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg13

postgres=# \d test4 Table "public.test4" Column | Type | Collation | Nullable | Default ---------+-----------------------------+-----------+----------+--------- id | integer | | | timest | timestamp without time zone | | not null | value | real | | | comment | text | | | Indexes: "test4_timest_idx" btree (timest DESC) Triggers: ts_insert_blocker BEFORE INSERT ON test4 FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker() Number of child tables: 3043 (Use \d+ to list them.)

postgres=# \d _timescaledb_internal._hyper_1_1000_chunk Table "_timescaledb_internal._hyper_1_1000_chunk" Column | Type | Collation | Nullable | Default ---------+-----------------------------+-----------+----------+--------- id | integer | | | timest | timestamp without time zone | | not null | value | real | | | comment | text | | | Check constraints: "constraint_1000" CHECK (timest >= '2012-10-26 00:00:00'::timestamp without time zone AND timest < '2012-10-27 00:00:00'::timestamp without time zone) Inherits: test4

postgres=# select version(); version

PostgreSQL 13.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit (1 row)

[Guzya]

bash-5.1# psql -U postgres psql (13.4) Type "help" for help.

postgres=# \dx List of installed extensions Name | Version | Schema | Description
-------------+---------+------------+------------------------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language timescaledb | 2.4.2 | public | Enables scalable inserts and complex queries for time-series data (2 rows)

postgres=# create table test4 (id integer, timest timestamp, value real, comment text); CREATE TABLE postgres=# do language plpgsql $$ postgres$# DECLARE r record; postgres$# begin postgres$# FOR i IN 1..100 postgres$# LOOP postgres$# FOR r IN select ts as timest,random() as value from generate_series('2010-01-31', '2018-05-31', interval '1 day') as gs(ts) postgres$# LOOP postgres$# EXECUTE 'insert into test4 (id, timest, value, comment) values(' || i ||','''|| r.timest || ''',' || r.value ||',''text_2'')'; postgres$# END LOOP; postgres$# END LOOP; postgres$# end postgres$# $$; DO postgres=# \d test4 Table "public.test4" Column | Type | Collation | Nullable | Default ---------+-----------------------------+-----------+----------+--------- id | integer | | | timest | timestamp without time zone | | | value | real | | | comment | text | | |

postgres=# select create_hypertable('test4','timest',chunk_time_interval => interval '1 day',migrate_data => true, create_default_indexes => true); NOTICE: adding not-null constraint to column "timest" DETAIL: Time dimensions cannot have NULL values. NOTICE: migrating data to chunks DETAIL: Migration might take a while depending on the amount of data. create_hypertable

(1,public,test4,t) (1 row)

postgres=# \d test4 Table "public.test4" Column | Type | Collation | Nullable | Default ---------+-----------------------------+-----------+----------+--------- id | integer | | | timest | timestamp without time zone | | not null | value | real | | | comment | text | | | Indexes: "test4_timest_idx" btree (timest DESC) Triggers: ts_insert_blocker BEFORE INSERT ON test4 FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker() Number of child tables: 3043 (Use \d+ to list them.)

postgres=# \d _timescaledb_internal._hyper_1_1000_chunk Table "_timescaledb_internal._hyper_1_1000_chunk" Column | Type | Collation | Nullable | Default ---------+-----------------------------+-----------+----------+--------- id | integer | | | timest | timestamp without time zone | | not null | value | real | | | comment | text | | | Check constraints: "constraint_1000" CHECK (timest >= '2012-10-26 00:00:00'::timestamp without time zone AND timest < '2012-10-27 00:00:00'::timestamp without time zone) Inherits: test4

[jflambert]

Still a problem in 2.10.2

[probstal]

Still a problem in 2.14.2