timescaledb hypertable causes migrate:fresh error

[boris-glumpler]

Hey Tobias,

I'm just now playing around with the timescaledb support and noticed that I can't seem to use the migrate:fresh command when there is a hypertable. This is the error I get:

ERROR:  cannot drop a hypertable along with other objects

In this particular project I already have a custom composer reset script, so just added a DROP TABLE command in there before calling migrate:fresh, which works fine.

Anyways, great work!

[tpetry]

I am not sure whether I can change some logic in the command. I‘ll have to look whether its using some Gramnar logic or similar to change the behavior.

[tpetry]

Its possible to fix this. The db:wipe command executes dropAllTables() on the SchemaBuilder. And that logic can be overwritten in this PG driver. So normal tables and hypertables would be dropped in different commands.

But its more complicated than this:

• Are foreign keys with hypertables supported? Don't really know. Never tried it.
• Continous aggregates would be dropped with dropAllViews() is that one working or does it also need special behaviour?
• Do we have to implement logic to calculate a dropping order? Argh. Topological sorting is great for that but a lot of code is needed to determine which things needs to be dropped before other things.

[tpetry]

Ok, foreign key support has been added with Timescale 2.16.0.

Maybe a first implementation could ignore that and only work on non foreign-key hypertables and continous aggregates. That raises the question which dropping logic needs to be implemented for continous aggregates:

1. Does the current logic work?
2. Can we drop all continous aggregates with one query or do we have to do drop them one after another? That would mean again some topological sorting because continous aggregates can be built on-top of continous aggregates.

[boris-glumpler]

Nice!

Can't you just do something like this: DROP TABLE hypertable CASCADE;? That deletes all the continuous aggregates as well before a call to dropAllViews() is made.

[tpetry]

Nice idea. Haven't tested whether it works on continous aggregates or hypertables.

[boris-glumpler]

I've been using it in my workaround and it works like a charm ;)

[tpetry]

Its now working as expected with release 2.3.1. But you have to use migrate:fresh --drop-views if you use continuous aggregates - somehow the cagg is not always deleted when deleting a hypertable with cascade.

[boris-glumpler]

Great! Thanks, mate!

[boris-glumpler]

In my case, I can't actually use --drop-views cause it also tries to drop any Postgis views, so causes an exception. Tried adding these views to the dont_drop config setting, but it only applies to tables, apparently. Never had any issues with cagg not being dropped when using cascade, so will just continue to use my workaround.

[boris-glumpler]

Just in case someone experiences the same issue, here are the contents of my composer script:

#!/bin/zsh

TABLES=(
  "hypertable1"
  "hypertable2"
  "..."
)

for table in "${TABLES[@]}"; do
  psql -d "database_name" -c "DROP TABLE public.${table} CASCADE;"
done

php artisan migrate:fresh

[tpetry]

Those PostGIS views should probably be on a whiteliste within Laravel - are there many? Maybe I can work on a fix within Laravel. And yeah, that views are not ignored is an oversight by Laravel. I'll add that to my package and will PR a change to Laravel.