How to bring an offline table back online and or delete it

,

In case of a failed retry, one of my large tables has gone offline. Am unable to delete the table because it’s offline and there’s literally nothing about offline tables that I can find. Am now unable to delete the offline table or the database as I cannot manipulate it in any way. I’ve been reading up on the descriptors in the system table but am not sure about what am doing. I hope someone here knows something

Hi @lionaruc !

Could you provide some more information? I’d like to know:

  • What is the query that failed to retry?
  • What is the schema of the table you are trying to delete?
  • What is the error message you get when you try to delete the table?
  • I’m not familiar with the terminology you are using – what does it been for a table to go offline?

Thanks!

Hi @rafiss, the query that failed was a result of a failed rollback for a CSV IMPORT INTO operation. The table am trying to delete is called ‘fingerprints’ and was about ~50GB. The error I get when trying to delete says ‘relation “fingerprints” does not exist’. This also means I can’t delete the table or the database without bringing the fingerprints table back online. When I try to delete the database I get the error

ERROR: cannot drop a database with OFFLINE tables, ensure acdc.afriziki.fingerprints is dropped or made public before dropping database acdc

Any help on this would be greatly appreciated.

Hi @lionaruc, could you post the row from the output of SHOW JOBS corresponding to the failed import job?

Generally tables are offline only for the duration of an import job (or similar) and should be cleaned up if the import job fails. For that reason, we disallow dropping databases with offline tables to protect tables in the progress of having data imported. But it seems like this table hasn’t been cleaned up, which suggests a bug.

Hi Lucy, thanks for reaching out. This was the error
‘job 625727622823706625: cannot be reverted, manual cleanup may be required: invalid pre-IMPORT time to rollback’
Basically an import job failed and it failed to revert resulting in the table going offline. Also because I have a big demo in two days yet I’ve just realized that I need the partitioning feature which isn’t in the community edition, I’ve had to migrate my data to CitusDB. I’ll probably use Cockroach for the second Phase of the app once I’m assigned budget to purchase an Enterprise License. I hope by then there will be some more information on this. Thanks again

Regards,

Lionel