1

I'm using Timescale and today I faced on a problem: I`m creating a simple table with any of bellow ways: 1-

> create table if not exists "NewTable" as (select * from "OldTable");
SELECT 6

2-

create table "NewTable" ("eventTime" timestamptz, name varchar);

after the table successfully created. I write \d and the result for both tables are same:

 Column   |           Type           | Collation | Nullable | Default 
-----------+--------------------------+-----------+----------+---------
eventTime | timestamp with time zone |           |          | 
name      | character varying        |           |          | 

but the problem starts here...

 > SELECT create_hypertable('"NewTable"', '"eventTime"' ,migrate_data => true);
 ERROR:  tried calling catalog_get when extension isn't loaded

so after I googled my issue I found nothing usefull insted of everyone told to create timescaledb extention that I had it before

> CREATE EXTENSION timescaledb CASCADE; //OR CREATE IF NOT EXISTS EXTENSION timescaledb CASCADE;
ERROR:  extension "timescaledb" has already been loaded with another version

and

> \dx
    Name     | Version |   Schema   |                            Description                            
-------------+---------+------------+-------------------------------------------------------- 
plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
timescaledb | 1.5.1   | public     | Enables scalable inserts and complex queries for time-series data

so what should I do?

Question : why this happened? how should I create a hyper table now?

before these operations, I tried to take a dump from my database and before that, I have 20 main_hyper_tables.

  • What OS do you use? How have you installed TimescaleDB? – k_rus Jan 23 '20 at 15:43
  • 1
    `apt` might auto-update your version of TimescaleDB, since 1.6 was issued a week ago. Can you see if you see binary version of 1.6 in the library folder? If so, you can run `ALTER EXTENSION timescaledb UPDATE`, see https://docs.timescale.com/latest/using-timescaledb/update-db – k_rus Jan 23 '20 at 15:47
  • I use UBUNTU 19.10 – kian jalali Jan 23 '20 at 17:09
  • More discussion: https://github.com/timescale/timescaledb/issues/1682 – Peter Becich Mar 08 '21 at 18:20

1 Answers1

3

why this happened?

I guess TimescaleDB was installed in the standard way through apt. Since new version of TimescaleDB (v.1.6) was released recently, apt automatically updated installation and copied the binary of 1.6 into the shared library of PostgreSQL installation. Thus new extension version was loaded, which is different from the extension used to create the database (v.1.5.1).

how should I create a hyper table now?

I see two options:

  1. Load the extension version used to create the database, by explicitly specifying in the postgres config.
  2. Update the extension to the latest version by
ALTER EXTENSION timescaledb UPDATE

See using ALTER EXTENSION section in Update TimescaleDB doc

k_rus
  • 2,959
  • 1
  • 19
  • 31