Correct way of deleting / dropping a hypertable

[njern]

Hey guys, filing an issue as I could not find this in your documentation:

What is the idiomatic way of dropping a table that has been turned into a hypertable?

If I create a hypertable like so:

CREATE TABLE stuff (
    id serial NOT NULL,
    ts timestamptz NOT NULL
);

SELECT create_hypertable('stuff', 'ts');

And then drop it

DROP TABLE stuff;

And try to recreate it using the initial commands, I end up with Error : ERROR: hypertable public.stuff already exists CONTEXT: PL/pgSQL function public.create_hypertable(regclass,name,name,integer,name,name,anyelement,boolean,boolean,regproc) line 51 at RAISE

[njern]

I solved the problem but I think there is some unintended behavior here.

• I created the table as a non-superuser.
• I also dropped it as a non-superuser.
• After deleting the row with the same table name from _timescale_catalog.hypertable (as a superuser, because my normal user does not have permissions to do it) I could re-create the hypertable just fine.
• If I use DROP TABLE stuff as a superuser it automatically deletes the hypertable as well.

[dhobbs]

Had a similar issue. I created and dropped a hypertable successfully a few times, then it started failing with this error:

[2018-01-25 15:26:39] [P0002] ERROR: query returned no rows
[2018-01-25 15:26:39] Where: PL/pgSQL function _timescaledb_internal.drop_chunk_constraint(integer,name,boolean) line 14 at SQL statement
[2018-01-25 15:26:39] SQL statement "SELECT _timescaledb_internal.drop_chunk_constraint(cc.chunk_id, cc.constraint_name, false)
[2018-01-25 15:26:39] FROM _timescaledb_catalog.chunk_constraint cc
[2018-01-25 15:26:39] WHERE cc.chunk_id = drop_chunk_metadata.chunk_id"
[2018-01-25 15:26:39] PL/pgSQL function _timescaledb_internal.drop_chunk_metadata(integer) line 11 at PERFORM
[2018-01-25 15:26:39] SQL statement "
[2018-01-25 15:26:39] DROP TABLE _timescaledb_internal._hyper_6_4488_chunk
[2018-01-25 15:26:39] "
[2018-01-25 15:26:39] PL/pgSQL function _timescaledb_internal.drop_hypertable(integer,boolean) line 14 at EXECUTE

Right now I have a table I can't delete!

[cevian]

@dhobbs It sounds like your error is actually related to #390 . I just posted a workaround for this problem in that issue and am working on a full fix. This does not apply to others in this thread.

[dhobbs]

Thanks! I'll give that a try.

On 26 Jan 2018 22:26, "Matvey Arye" notifications@github.com wrote:

@dhobbs https://github.com/dhobbs It sounds like your error is actually related to #390 https://github.com/timescale/timescaledb/issues/390 . I just posted a workaround for this problem in that issue and am working on a fix. This does not apply to others in this thread.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/timescale/timescaledb/issues/400#issuecomment-360921566, or mute the thread https://github.com/notifications/unsubscribe-auth/AAWbD0AptS7qPkHouQXi6N_OJYEfcJuPks5tOlD7gaJpZM4RrMnl .

[mfreed]

@dhobbs 0.9.0 was released yesterday, which should fix this. Please let me know if you continue to see this issue, or please otherwise close the issue? Thanks!

https://github.com/timescale/timescaledb/releases/tag/0.9.0

[saosebastiao]

I'm currently having this issue. Ubuntu 16.04LTS, postgres 10, timescale 0.9.0

CREATE TABLE ticker ( trade_id BIGINT, sequence BIGINT, product_id VARCHAR(7), price NUMERIC(14,8), open_24h NUMERIC(15,8), volume_24h NUMERIC(15,8), low_24h NUMERIC(15,8), high_24h NUMERIC(15,8), volume_30d NUMERIC(15,8), best_bid NUMERIC(15,8), best_ask NUMERIC(15,8), side VARCHAR(4), time TIMESTAMPTZ NOT NULL, last_size NUMERIC(15,8) ); SELECT create_hypertable('ticker','time','product_id'); DROP TABLE ticker; CREATE TABLE ticker ( trade_id BIGINT, sequence BIGINT, product_id VARCHAR(7), price NUMERIC(14,8), open_24h NUMERIC(15,8), volume_24h NUMERIC(15,8), low_24h NUMERIC(15,8), high_24h NUMERIC(15,8), volume_30d NUMERIC(15,8), best_bid NUMERIC(15,8), best_ask NUMERIC(15,8), side VARCHAR(4), time TIMESTAMPTZ NOT NULL, last_size NUMERIC(15,8) ); SELECT create_hypertable('ticker','time','product_id'); ERROR: table "ticker" is already a hypertable

[RobAtticus]

Hi @saosebastiao , was this hypertable created/dropped originally when you were on 0.8.0?

Also can you give the output of: SELECT * FROM _timescaledb_catalog.hypertable;?

[saosebastiao]

Sure, I'll try to give as much info as I can. I originally experienced the bug on 0.8.0, but have since fully recreated the bug from scratch via 0.9.0

Environment:

Ubuntu 16.04 LTS Postgres 10 via http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main pg cluster set up via method Alternative Server Setup in the Ubuntu Wiki with my user account as a super user. timescaledb via the official Ubuntu PPA

Steps to reproduce database:

createuser -lP tradebot_user
createdb -O tradebot_user tradebot_tmp
sudo -u postgres psql -c "CREATE EXTENSION timescaledb;" tradebot_tmp
psql -U tradebot_user -h localhost -p 5432 -d tradebot_tmp -W

Steps to reproduce bug:

Step 1: Log in, create table and hypertable

tradebot_tmp=> CREATE TABLE ticker (
  trade_id BIGINT,
  sequence BIGINT,
  product_id VARCHAR(7),
  price NUMERIC(14,8),
  open_24h NUMERIC(15,8),
  volume_24h NUMERIC(15,8),
  low_24h NUMERIC(15,8),
  high_24h NUMERIC(15,8),
  volume_30d NUMERIC(15,8),
  best_bid NUMERIC(15,8),
  best_ask NUMERIC(15,8),
  side VARCHAR(4),
  time TIMESTAMPTZ NOT NULL,
  last_size NUMERIC(15,8)
);
SELECT create_hypertable('ticker','time','product_id');
/* verify creation
tradebot_tmp=>  SELECT * FROM _timescaledb_catalog.hypertable;
 id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions 
----+-------------+------------+------------------------+-------------------------+----------------
  1 | public      | ticker     | _timescaledb_internal  | _hyper_1                |              1
*/

Step 2: Log out and log back in NOTE This bug is not reproducible without this step

tradebot_tmp=> \q
# log back in
psql -U tradebot_user -h localhost -p 5432 -d tradebot_tmp -w 

Step 3: Drop Table

DROP TABLE ticker;
/* VERIFY DROP FAILS
tradebot_tmp=>  SELECT * FROM _timescaledb_catalog.hypertable;
 id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions 
----+-------------+------------+------------------------+-------------------------+----------------
  1 | public      | ticker     | _timescaledb_internal  | _hyper_1                |              1
(1 row)
*/

Step 4: Recreate table

CREATE TABLE ticker (
  trade_id BIGINT,
  sequence BIGINT,
  product_id VARCHAR(7),
  price NUMERIC(14,8),
  open_24h NUMERIC(15,8),
  volume_24h NUMERIC(15,8),
  low_24h NUMERIC(15,8),
  high_24h NUMERIC(15,8),
  volume_30d NUMERIC(15,8),
  best_bid NUMERIC(15,8),
  best_ask NUMERIC(15,8),
  side VARCHAR(4),
  time TIMESTAMPTZ NOT NULL,
  last_size NUMERIC(15,8)
);
SELECT create_hypertable('ticker','time','product_id');
--ERROR:  table "ticker" is already a hypertable

Crucially, it is step 2 that seems to matter. If I do it all within the same session, I can't reproduce. However, there is something about ending your session by logging out and then logging back in again before dropping the table that causes it.

I hope this helps.

[RobAtticus]

Very strange, I just tried this on a Ubuntu 16.04 box running 10.3 and did not see any issues. I created the extension using the postgres user (like you did) then ran everything else as my own user and was able to delete it just fine. Is it possible it is still referring to 0.8.0 somewhere? Does \dx show timescaledb at 0.9.0?

I'm a bit perplexed otherwise.

[solugebefola]

Does the create extension command not having the 'CASCADE' parameter make a difference?

On Tue, Mar 13, 2018 at 3:35 PM, RobAtticus notifications@github.com wrote:

Very strange, I just tried this on a Ubuntu 16.04 box running 10.3 and did not see any issues. I created the extension using the postgres user (like you did) then ran everything else as my own user and was able to delete it just fine. Is it possible it is still referring to 0.8.0 somewhere? Does \dx show timescaledb at 0.9.0?

I'm a bit perplexed otherwise.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/timescale/timescaledb/issues/400#issuecomment-372791518, or mute the thread https://github.com/notifications/unsubscribe-auth/ALuBEa2dcFO4hmHN9_uNHDmIhI-NvcNWks5teB-SgaJpZM4RrMnl .

[saosebastiao]

@RobAtticus

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

Did you create the database with your personal user account as the owner? Because if you set yourself up as a superuser, that might affect it. In my case, a non-superuser account was created specifically to own and use the database, and all logins were done with that user.

[RobAtticus]

I just tried again making sure my user is the owner of the DB but not a super user and I still am able to delete the hypertable completely. So I did:

(As postgres user):

CREATE DATABASE rob OWNER rob;
\c rob
CREATE EXTENSION timescaledb;

Then I followed your steps, connecting as user rob, created the table, made it a hypertable, quit, reconnect, drop, check if table is still in catalog table (it isn't).

Not sure where that leaves us :\

[cevian]

@saosebastiao what is the value of show shared_preload_libraries; on your machine?

[cevian]

I was able to reproduce this problem if shared_preload_libraries did not include timescaledb (as it should)

[saosebastiao]

Okay it looks like that was my problem. I forgot to change that when I upgraded from 9.6 to 10. Thanks for your help!

[cevian]

Ok Great, closing. (Note: we are putting in better error messages for forgetting shared_preload_libraries in PR #469)

[scyber]

Hi Guys! It seems it's not really possible to drop/remove hypertable without dropping original table, please amend in case I'm mistaken.

[shayneoneill]

Note. I've had a number of cases where dropping the table (especially in bulk deletes of tables) does NOT delete the hypertable. I assume this is expected behavior, but I dont know why.

[lkshminarayanan]

Hi @shayneoneill, That is not the expected behaviour. Can you please file a bug with more details and the commands you used?