timescale / timescaledb Goto Github PK

Is it possible to add this postgres extension to pipelinedb and use the table/hyper to process continuous streams in pipelinedb thereby making the event data scalable in it and also being able to query it continously at a very large scale.
https://github.com/pipelinedb/pipelinedb

Can you release benchmarks against:

• https://kx.com/
• https://www.onetick.com/
• https://clickhouse.yandex/
• http://www.scylladb.com/
• http://www.aerospike.com/
• http://greenplum.org/
• https://www.pipelinedb.com/
• https://crate.io/
• https://rethinkdb.com/
• http://druid.io/
• etc.

As part of you testing and CI so regressions are know and addressed sooner.

For example, if the server has a setting of cpu_tuple_cost=0.6 the regression test will break. Perhaps the regression tests should explicitly set planner settings.

OS: MacOS Sierra
Install method: docker, tag: latest (should be 0.0.7-beta)

I tried with the weather_small, weather_big, devices_small and got no errors. But there is no data in conditions table, has some data in locations table.

Can you provide some guidance on the use of partitions and chunk_time_interval in terms of implications for layout and performance? There was no documentation that I could find on the chunk_time_interval. Found it in one of the sample sets provided.

Also are Postgres / your extensions able to run parallel scans across chunks (if the query is across chunks or requires a join with a different time series table)? Have recent versions of Postgres carried over some of the capabilities of green plum (which is now open sourced)?

I am testing the database on data where have 1 sample (with 110 measures) per second 24x7. Given the large # of samples and that most queries I would do would be within a given day historically it seemed to make sense to chunk on the day. I also have a categorical variable that divides the data set into 10 distinct sets. Hence I initially created the hyper table with:

SELECT create_hypertable('measures', 'stamp', 'instrument', 10, chunk_time_interval => 86400000000); 

Based on a comment in one of the example sets have been led to believe that the chunk_time_interval is expressed in microseconds.

I also created another table, which would be joined with this for specific queries with the same attributes. The resulting performance of these tables (querying within 1 historical day of data) was very poor. I had noticed that the performance using:

SELECT create_hypertable('measures', 'stamp', 'instrument', 2); 

with default chunk size and 2 partitions was an order or two of magnitude faster. I don't know enough about the data structures as to posit why there is such a significant performance difference. One table contained 300 million rows (on 2 x 7 years of data) and the other 20 million rows (on 1 x 1 year of data). Ultimately both tables would have 10 (instruments) x 7 (years) or possibly more instruments in the future.

Finally I should mention that I was testing this on an OS X 10.12.4 Mac with reasonably fast CPU (a higher end core i7), 32GB of memory with 4G dedicated to postgres, single disk for data, no stripping. Eventually would move to a linux box with a stripped disk array and with higher memory allocation.

C:\Program Files\PostgreSQL\timescaledb-master>"C:\Program Files (x86)\GnuWin32\bin\make.exe"
process_begin: CreateProcess(NULL, pg_config --pgxs, ...) failed.
'cat' is not recognized as an internal or external command,
operable program or batch file.
'cat' is not recognized as an internal or external command,
operable program or batch file.
process_begin: CreateProcess(NULL, cat sql/load_order.txt, ...) failed.
'cat' is not recognized as an internal or external command,
operable program or batch file.
'cat' is not recognized as an internal or external command,
operable program or batch file.
make: Nothing to be done for `all'.

Hey guys,

just thought of letting you know about your Docker instructions.

The first step for me was to pull the Docker image (https://github.com/timescale/timescaledb/#option-2---docker-hub). There is no information on how to run the docker image and what ports need to be open, and environmental parameters provided. I think the Readme should be updated.

Knowing Postgres, I opened 5432 port, and continued with your “getting started” installation guide:

• Setting up an empty database
• Creating a hypertable
• Creating an index
• Inserting data
• Querying data

All done with the psql tool. When I closed the psql session and reconnected again, the data was not there, and inserts were failing on missing “dblink” extension. I recommend updating the documentation here, either instructing people to "CREATE EXTENSION IF NOT EXISTS dblink CASCADE;” or having the docker image already instantiated with the necessary extensions.

Later I found the docker-run.sh script (https://github.com/timescale/timescaledb/blob/master/scripts/docker-run.sh) which helps tremendously in setting up the container. It survives closing the psql connection, as well as Docker container restarts (as it mounts the volume). I would highly recommend referencing to this script in your Readme and Getting Started documentation.

I hope this feedback helps a bit.

RDS comes with pre-packaged extensions... are there any talks with AWS to include timescale in their supported extensions, given that timescaledb uses the Apache 2.0 license?

Reported on the forum

Having some trouble when I grant insert on a table to a user other than the superuser that created the table, getting errors when I try to insert into the table. No problem if I do the inserts as the superuser who created the table etc. error is must be owner of relation _hyper_1_1_0_partition.

When copying in data from the tutorial, the result of the COPY is incorrect, showing ~4M less rows than there should be:

$ psql -d nyc_data -c '\COPY rides FROM nyc_data_rides.csv CSV;'
COPY 6333482

$ wc -l nyc_data_rides.csv
 10906858 nyc_data_rides.csv

$ psql -d nyc_data -c 'SELECT COUNT(*) FROM rides;'
  count
----------
 10906858
(1 row)

If create_hypertable() is run on an existing hypertable, the hypertable serial ID is still incremented. Although not strictly a bug, it is something we might want to avoid in order to not eat a lot of IDs. For instance, one could first check for an existing hypertable before incrementing the ID.

Similar to issue #28, \COPY into a hypertable always shows 0 rows copied

E.g., this query does not work:

INSERT INTO rides_test (SELECT * from rides LIMIT 1);

What are the supported platforms for timescaledb? I've tried compiling on MacOS and FreeBSD, without success.

Hi,
gcc version:4.4.7

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -DINCLUDE_PACKAGE_SUPPORT=0 -MMD -I. -I./ -I/usr/local/postgresql-9.6.2/include/server -I/usr/local/postgresql-9.6.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o src/metadata_queries.o src/metadata_queries.c
In file included from src/metadata_queries.c:14:
src/chunk.h:23: error: redefinition of typedef ‘Chunk’
src/metadata_queries.h:6: note: previous declaration of ‘Chunk’ was here
make: *** [src/metadata_queries.o] Error 1

Now you can run "CREATE TABLE IF NOT EXISTS mytable" but you need to manually check whether it has been converted to an hypertable.

Hi there.

I'm attempting to create a hypertable with a table generated by Django but when I try to call the create_hypertable function I get an error:

postgres=# SELECT create_hypertable('example', 'time');
ERROR:  Cannot create a unique index without the time column
CONTEXT:  PL/pgSQL function _timescaledb_internal.get_general_index_definition(regclass,regclass,_timescaledb_catalog.hypertable) line 27 at RAISE
SQL statement "SELECT 1
    FROM pg_index,
    LATERAL _timescaledb_meta_api.add_index(
        hypertable_row.id,
        hypertable_row.schema_name,
        (SELECT relname FROM pg_class WHERE oid = indexrelid::regclass),
        _timescaledb_internal.get_general_index_definition(indexrelid, indrelid, hypertable_row)
    )
    WHERE indrelid = main_table"
PL/pgSQL function create_hypertable(regclass,name,name,integer,smallint,name,name,_timescaledb_catalog.chunk_placement_type,bigint,boolean,boolean) line 97 at PERFORM

I think it's something to do with the auto PK that Django tries to create.
It may well be the correct approach for me to separate out my timescaledb database and my Django managed ones, but I thought it was worth posting here first incase there was some obvious way round this or if you could let me know what specifically it doesn't like about the Django table.

If it's of any use I've created a small example Django project and pasted below a pg_dump to show what Django's created tables look like.

CREATE TABLE example (
    id integer NOT NULL,
    "time" timestamp with time zone NOT NULL
);


CREATE SEQUENCE example_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE example_id_seq OWNED BY example.id;
ALTER TABLE ONLY example ALTER COLUMN id SET DEFAULT nextval('example_id_seq'::regclass);
SELECT pg_catalog.setval('example_id_seq', 1, false);
ALTER TABLE ONLY example
    ADD CONSTRAINT example_pkey PRIMARY KEY (id);

Thanks very much for any assistance you can give me on this. Please let me know if I can add anything else to help.

Using today's head :

tutotsdb=# select setup_db();
ERREUR: n'a pas pu charger la bibliothèque « /usr/lib/postgresql/9.6/lib/timescaledb.so » : /usr/lib/postgresql/9.6/lib/timescaledb.so: undefined symbol: dblink_disconnect

(ERROR: cannot load library /usr/lib/...)

Is it possible to have functionality where you can directly do machine learning and Big Data, on changing TS data, in TSDB?

My system often inserts records that are duplicated (data with the same timestamp and same value in partitioning column). This leads to duplication in data (waste storage and require data cleaning after queries)

My database setup:

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);
SELECT create_hypertable('conditions', 'time', 'location', 2);

When I insert 2 rows with same values:

INSERT INTO conditions(location, time, temperature, humidity)
  VALUES ('office', '2017-06-25 03:34:16+00', 70.0, 50.0);
INSERT INTO conditions(location, time, temperature, humidity)
  VALUES ('office', '2017-06-25 03:34:16+00', 70.0, 50.0);

=> The conditions table will have 2 records with exactly same data.

Is there any way to make timescaledb silently ignore the second insert? If not, can you please suggest me an efficient way to remove duplicated rows regularly?

Many thanks!

Hi,

I'm trying out timescaledb. I was trying out some queries similar to those on the home page, such as

SELECT date_trunc(time, '15 min') AS 15min, COUNT(device_id)
FROM logs
WHERE cpu_level > 0.9 AND free_mem < 1024
  AND time > now() - interval '24 hours'
GROUP BY 15min
ORDER BY COUNT(device_id) DESC LIMIT 25;

However, this gives an error on the date_trunc function: No function matches the given name and argument types. You might need to add explicit type casts.

Indeed, I cannot find a usage like the one above in the postgres docs. Am I missing something or are the examples wrong?

1.CentOS 6.5 X64,
2.postgresql96:
[root@localhost soft]# rpm -qa|grep postgres
postgresql96-9.6.3-1PGDG.rhel6.x86_64
postgresql96-contrib-9.6.3-1PGDG.rhel6.x86_64
postgresql96-devel-9.6.3-1PGDG.rhel6.x86_64
postgresql96-libs-9.6.3-1PGDG.rhel6.x86_64
postgresql96-server-9.6.3-1PGDG.rhel6.x86_64
[root@localhost soft]#
-bash-4.1$ psql --version
psql (PostgreSQL) 9.6.3

3.make error
-bash-4.1$ cd timescaledb-master
-bash-4.1$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -fpic -DINCLUDE_PACKAGE_SUPPORT=0 -MMD -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o src/init.o src/init.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -fpic -DINCLUDE_PACKAGE_SUPPORT=0 -MMD -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o src/extension.o src/extension.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -fpic -DINCLUDE_PACKAGE_SUPPORT=0 -MMD -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o src/utils.o src/utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -fpic -DINCLUDE_PACKAGE_SUPPORT=0 -MMD -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o src/catalog.o src/catalog.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -fpic -DINCLUDE_PACKAGE_SUPPORT=0 -MMD -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o src/metadata_queries.o src/metadata_queries.c
In file included from src/metadata_queries.c:14:
src/chunk.h:23: error: redefinition of typedef ‘Chunk’
src/metadata_queries.h:6: note: previous declaration of ‘Chunk’ was here
make: *** [src/metadata_queries.o] Error 1
-bash-4.1$

There seems to be many mentions of scaling horizontally with no clear description on how to make a system do that.

We need some docs on how this is done.

I am facing an issue with the pg_dump command. I dump the data of my tables with the command:

pg_dump -a -U user -d database-t table> /my/path/table.bak

It works perfectly fine with standard tables but not with hypertable.
Is it possible to dump hypertables that way or do we have to use the COPY FROM in the doc. We have a specific usecase where we need to use INSERT INTO statements, too much complicated to explain in few lines.

I have "wide" tables, where for some analytical queries would greatly benefit from columnar storage & other efficiencies. Can timescaledb work with the columnar extension? For example:

https://github.com/citusdata/cstore_fdw

Running:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE TABLE cpu (time timestamptz, tags_id integer, hostname TEXT,usage_user DOUBLE PRECISION,usage_system DOUBLE PRECISION,usage_idle DOUBLE PRECISION,usage_nice DOUBLE PRECISION,usage_iowait DOUBLE PRECISION,usage_irq DOUBLE PRECISION,usage_softirq DOUBLE PRECISION,usage_steal DOUBLE PRECISION,usage_guest DOUBLE PRECISION,usage_guest_nice DOUBLE PRECISION);
CREATE INDEX ON cpu(tags_id, "time" DESC);
CREATE INDEX ON cpu (usage_user,time DESC);
SELECT create_hypertable('cpu'::regclass, 'time'::name, partitioning_column => 'tags_id'::name, number_partitions => 1::smallint, chunk_time_interval => 28800000000);

Results in two indexes on (tags_id, "time" DESC) (creating default indexes should not re-create this one).

Is it possible to add optimisations where this could outperform DBs like https://www.onetick.com/, https://kx.com/, https://www.pipelinedb.com/

As header says..., When I consider consider horizontal partitioning via citus extension ("sharding"), is this compatible with current timescaledb.

Otherwise good stuff guys!

Reproduce:

CREATE TABLE tt(c timestamp with time zone);
SELECT create_hypertable('tt', 'c');
INSERT INTO tt VALUES('2016-11-15 17:30:00+05:30');
INSERT 0 0

Result should be
INSERT 0 1

This is the error log when I tried to restore from the backup of my timescaledb:

postgres=# CREATE DATABASE coin;
CREATE DATABASE
postgres=# \c coin
You are now connected to database "coin" as user "postgres".
coin=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE EXTENSION
coin=# ALTER DATABASE coin  SET timescaledb.restoring='on';
ALTER DATABASE
coin=# \! pg_restore -d coin -U postgres coin.bak
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 204; 1259 17702 TABLE _hyper_4_103_chunk postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  function get_partition_for_key(text, integer) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
    Command was: CREATE TABLE _hyper_4_103_chunk (
    CONSTRAINT partition CHECK (((get_partition_for_key(symbol, 32768) >= '0'::smallint) A...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "_hyper_4_103_chunk" does not exist
    Command was: ALTER TABLE _hyper_4_103_chunk OWNER TO postgres;


pg_restore: [archiver (db)] Error from TOC entry 205; 1259 17712 TABLE _hyper_4_104_chunk postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  function get_partition_for_key(text, integer) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
    Command was: CREATE TABLE _hyper_4_104_chunk (
    CONSTRAINT partition CHECK (((get_partition_for_key(symbol, 32768) >= '0'::smallint) A...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "_hyper_4_104_chunk" does not exist
    Command was: ALTER TABLE _hyper_4_104_chunk OWNER TO postgres;

....
(many many more similar lines)

My first attempt I didn't enter CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; but the log was exactly same as above.

Internal code review/cleanup for performance: some node copies in planner.c may be unnecessary. Especially in create_partition_func_equals_const.

Environment: running latest Docker image (0.0.9-beta) on Mac.

Running the SQL below causes an error on hypertable creation.
Creating a non-UNIQUE index works.
Using a PRIMARY KEY(ID) also fails.

DROP TABLE IF EXISTS number_facts ;
CREATE TABLE IF NOT EXISTS number_facts (
id BIGINT NOT NULL,
ts TIMESTAMP WITHOUT TIME ZONE,
value DOUBLE PRECISION NOT NULL
);
CREATE UNIQUE INDEX number_facts_u ON number_facts (id, ts) ;
SELECT create_hypertable('number_facts', 'ts', 'id', 2);

ERROR: Cannot process index with definition (no index name match) CREATE UNIQUE INDEX number_facts_u ON /TABLE_NAME/ USING btree (id, ts)
Where: PL/pgSQL function _timescaledb_internal.get_general_index_definition(regclass,regclass) line 28 at RAISE
SQL statement "SELECT 1
FROM pg_index,
LATERAL _timescaledb_meta_api.add_index(
hypertable_row.id,
hypertable_row.schema_name,
(SELECT relname FROM pg_class WHERE oid = indexrelid::regclass),
_timescaledb_internal.get_general_index_definition(indexrelid, indrelid)
)
WHERE indrelid = main_table"
PL/pgSQL function create_hypertable(regclass,name,name,integer,smallint,name,name,_timescaledb_catalog.chunk_placement_type,bigint) line 93 at PERFORM

OS: MacOS Sierra
Install method: homebrew

I was trying to use the sample data. I followed the setup instructions on http://docs-new.timescaledb.com/other-sample-datasets for the device_small dataset and had a problem with the command

psql -U postgres -d devices_small -h localhost < devices_small.bak

because I did not have a postgres superuser.

I created a postgres user, did a DROP DATABASE devices_small and tried again. Most of it went fine, but there was one error.

ERROR:  function _timescaledb_internal.on_modify_main_table() does not exist

and there doesn't appear to be any data in the database readings table, but there is data in the device_info table.

Update: I tried with the weather_small dataset and got the same error and again, no data in the conditions table, but the locations table has data

When ci fails with a regression test error we should probably print the diff as part of the output.

In https://github.com/timescale/timescaledb/blob/master/sql/common/tables.sql there is a comment on the _timescaledb_catalog.cluster_user table.

CREATE TABLE IF NOT EXISTS _timescaledb_catalog.cluster_user (
    username TEXT NOT NULL PRIMARY KEY,
    password TEXT NULL --not any more of a security hole than usual since stored in  pg_user_mapping anyway
);

That comment is only partially accurate. The pg_catalog.pg_user_mapping table is viewable only by super users, whereas the cluster_user table appears to be viewable by non-superusers.
Are there plans to restrict access to that table, or more specifically, to the password column?

I did some code for a PG extension, and I needed a good and fast hash function; so I included murmur3 source files in the extension to get that good hash function. Later I realized that all the hash* functions accessible via #include "access/hash.h" were perfectly good hash functions for my needs, and I didn't have to include all that murmur3 source code in the extension. What's more, the PG hash functions already deal with CPU-architecture differences.

Do you have a special reason that requires murmur3 to be your hash function to create partition keys? The PG hash functions are based on Bob Jenkins's hash function (called lookup3 I think).

https://doxygen.postgresql.org/hashfunc_8c_source.html#l00177

If these hash functions are appropriate for your needs, you could use hashvarlena to get the int4 hash, then do the modulo arithmetic.

I've been testing timescaledb on a few hundred million rows of data and happy with performance so far. One area where the feature set is lacking is with some basic functions often present on timeseries and/or analytic databases:

1. median
2. histogram
3. percentile
4. first / last
5. cumsum

While there are (more complex) analogs for cumsum and first/last in Postgres, would be useful to have these available as efficient operations on a group, as applicable.

Aside from Spark and a couple of other "big data" platforms, was previously using Netezza which has a fairly large library of analytical functions.

SELECT failed if condition contains filter over integer column, wich was used as additional partitioning column.

CREATE TABLE history (
  timestamp TIMESTAMPTZ,
  object_id int,
  value int
);
SELECT create_hypertable('history', 'timestamp', 'object_id', 10);
SELECT * FROM history WHERE object_id = 1;

resulting:

ERROR:  function _timescaledb_catalog.get_partition_for_key(integer, integer) does not exist

Version 0.0.11-beta from dockerhub

Hey guys,

I am trying to bring up TimescaleDB via Docker Compose (as opposed to Docker run) and it seems to me that the extension is not being loaded.

Dockerfile

FROM timescale/timescaledb

ENV PGDATA=/var/lib/postgresql/data/timescaledb
VOLUME /var/lib/postgresql
EXPOSE 5432

ADD initialize.sh /docker-entrypoint-initdb.d/init-user-db.sh

initialize.sh

#!/bin/bash

psql --username "$POSTGRES_USER" <<EOF
CREATE DATABASE my_db WITH OWNER $POSTGRES_USER;
GRANT ALL PRIVILEGES ON DATABASE my_db TO $POSTGRES_USER;

\c my_db
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

CREATE TABLE IF NOT EXISTS  my_table (
  time      TIMESTAMP         NOT NULL,
  metric    TEXT              NOT NULL,
  usage     DOUBLE PRECISION  NOT NULL
);
SELECT create_hypertable('my_table', 'time');
CREATE INDEX IF NOT EXISTS usage_metric ON my_table (metric, time DESC);
EOF

Compose.yml

version: '3'

services:
  timescale:
    image: my_timescale
    command: postgres -c shared_preload_libraries=timescaledb
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=my_user
      - POSTGRES_PASSWORD=my_password
    volumes:
      - ./timescale:/var/lib/postgresql

As you can see I am trying to load timescaledb extension via command: postgres -c shared_preload_libraries=timescaledb but it looks like nothing is happening. This is what I get as an output:

NOTICE:  installing required extension "postgres_fdw"
ERROR:  The timescaledb library is not preloaded
HINT:  Please preload the timescaledb library via shared_preload_libraries.

  This can be done by editing the config file at: /var/lib/postgresql/data/timescaledb/postgresql.conf
  and adding 'timescaledb' to the list in the shared_preload_libraries config.

    # Modify postgresql.conf:
      shared_preload_libraries = 'timescaledb'

  Another way to do this, if not preloading other libraries, is with the command:
  echo "shared_preload_libraries = 'timescaledb'" >> /var/lib/postgresql/data/timescaledb/postgresql.conf 

  (Will require a database restart.)

  If you REALLY know what you are doing and would like to load the library without preloading, you can disable   this check with: 
    SET timescaledb.allow_install_without_preload = 'on';

STATEMENT:  CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

And consequently that's why create_hypertable fails.

Starting TimescaleDB via Docker run works for me without any issues. I also tried to put aftermentioned postgres -c shared_preload_libraries=timescaledb command to Dockerfile, but it did not help.

Am I missing something? How are you doing your Docker compose?

Thanks in advance.

Best wishes,
Martin

It is mentioned in the FAQ that the clustered version is in active development. Can you share more details on the design, status, or roadmap? The technical paper doesn't seem to shed much light on this to me. Am I missing anything?

Anyway, great job TimescaleDB!

Is it possible to modify timescaledb to use an expression for the time column?

pg10 not support

I'm observing an error when trying to create a new hypertable from an existing table - following the instructions at http://docs.timescale.com/getting-started/setup/migrate-from-postgresql#same-db

CREATE TABLE my_new_table (LIKE my_old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

SELECT create_hypertable('my_new_table', 'my_timestamptz');

ERROR:  Cannot create a unique index without the column: my_timestamptz (used in partitioning)
CONTEXT:  PL/pgSQL function _timescaledb_internal.get_general_index_definition(regclass,regclass,_timescaledb_catalog.hypertable) line 33 at RAISE
SQL statement "SELECT 1
    FROM pg_index,
    LATERAL _timescaledb_internal.add_index(
        hypertable_row.id,
        hypertable_row.schema_name,
        (SELECT relname FROM pg_class WHERE oid = indexrelid::regclass),
        _timescaledb_internal.get_general_index_definition(indexrelid, indrelid, hypertable_row)
    )
    WHERE indrelid = main_table"
PL/pgSQL function public.create_hypertable(regclass,name,name,integer,name,name,bigint,boolean,boolean) line 87 at PERFORM

However the hypertable creation succeeds if I omit the constraints and indexes in the create table like statement:

CREATE TABLE my_new_table (LIKE my_old_table INCLUDING DEFAULTS EXCLUDING CONSTRAINTS EXCLUDING INDEXES);

Is this expected behaviour ? The docs suggest it is possible to convert an empty table with constraints and indexes.

I know I can do a backup with timescale db (here), but my use case is different. I want to export data from a table, with the COPY FROM instruction in sql :

COPY (SELECT * FROM sensor_climate) TO '/data_export/sensor_climate.bak' With CSV quote

and then insert those data in another database:

COPY sensor_climate FROM '/data_export/sensor_climate.bak' DELIMITER ',' CSV;

But it does not work with an hypertable ! There is only a generic error message, and it works perfectly with other tables (wich are non-hypertables).

[Edit] The error message I get:

fuldatabase=# copy sensor_climate from '/shared_volume/sensor_climate.bak' csv quote;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Also, the server tells me:

Server process (PID xx) was terminated by signal 11

If you see a better way to do what I want, please tell me. However, if timescaledb is built for IOT, it is a really important feature being able to export data easily.

For example I have some sensors in a location A with a timescaledb A, and other sensors in a different location B with timescaledb B. I need to export data periodically on a timescaledb C. Actually, it seems that I can't do that.

Currently, a DROP command on a hypertable may require the CASCADE modifier because of inheritance. Hypertable drops should not require CASCADE.

tutotsdb=# select setup_db();
ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « port »
DETAIL:  La ligne en échec contient (tutotsdb, pierre-vbox, null, tutotsdb)
CONTEXT:  instruction SQL « INSERT INTO _timescaledb_catalog.meta (database_name, hostname, port, server_name)
        VALUES (database_name, hostname, port, database_name) »
fonction PL/pgsql set_meta(text,integer,name,text,text), ligne 13 à instruction SQL
instruction SQL « SELECT set_meta(hostname, port, database, username, password) »
fonction PL/pgsql setup_db(name,text,text,text,integer), ligne 11 à PERFORM

(Hand made) translation :

ERRROR: a NULL value violates the NOT NULL constraint of the column "port"
DETAIL: Failure line contains (tutotsdb, pierre-vbox, null, tutotsdb)
...

Description:
Hitting installation issue (see below) against:

1. ubuntu: 14.04
2. Homebrew: 1.2.0

ubuntu@ip-172-31-16-101:~$ brew install timescaledb ==> Installing timescaledb from timescale/tap ==> Downloading https://timescalereleases.blob.core.windows.net/homebrew/timescaledb-0.0.11-beta.tar.gz Already downloaded: /home/ubuntu/.cache/Homebrew/timescaledb-0.0.11-beta.tar.gz ==> make ==> make install DESTDIR=/tmp/timescaledb-20170605-5821-1spkl5u/timescaledb/stage Warning: tried to install empty array to /home/ubuntu/.linuxbrew/Cellar/timescaledb/0.0.11-beta/lib/postgresql Warning: tried to install empty array to /home/ubuntu/.linuxbrew/Cellar/timescaledb/0.0.11-beta/share/postgresql/extension Error: Empty installation

When adding a column with if not exists below error is received .

postgres=# CREATE TABLE my_ht (time bigint, val integer);
CREATE TABLE
postgres=# SELECT * FROM create_hypertable('my_ht', 'time');
 create_hypertable 
-------------------
 
(1 row)

postgres=# ALTER TABLE my_ht ADD COLUMN val2 integer;
ALTER TABLE
postgres=# ALTER TABLE my_ht ADD COLUMN val2 integer;
ERROR:  column "val2" of relation "my_ht" already exists
postgres=# ALTER TABLE my_ht ADD COLUMN IF NOT EXISTS val3 integer;
ALTER TABLE
postgres=# ALTER TABLE my_ht ADD COLUMN IF NOT EXISTS val3 integer;
NOTICE:  column "val3" of relation "my_ht" already exists, skipping
ERROR:  Unknown alter table action on my_ht
CONTEXT:  PL/pgSQL function _timescaledb_internal.ddl_process_alter_table() line 101 at RAISE