DROP TABLE not synchronizing with user_tables

[Rikaelus]

Coming here after a lack of responses on StackExchange

Summary: While creating an environment test script we've discovered that dropping a table via the SQL API seems to leave a reference to that table alive in carto_db_production.user_tables. Documentation here seems to suggest a delayed, periodic check might be responsible for cleaning up loose ends when certain table changes are made via query, but this doesn't seem to be the case. The entry remains for days, at least, and I think is responsible for the dataset being listed in the UI, though the actual table missing obviously prevents full viewing of the dataset.

Side note: We're initially creating the dataset via the Imports API.

[mondeja]

I have encountered the same problem. I've solved connecting with the machine that is running Carto through SSH and executing queries directly with the PostgreSQL database. First I'm executing a query through SQL API:

DROP TABLE IF EXISTS <dataset_name> CASCADE;COMMIT;

And next, I'm executing two queries though SSH:

psql -U postgres -d carto_db_development -c "DELETE FROM user_tables WHERE (name='<dataset_name>' AND user_id='<carto_user_id>');"
psql -U postgres -d carto_db_development -c "DELETE FROM visualizations WHERE (name='<dataset_name>' AND user_id='<carto_user_id>');"

You can get the carto_user_id executing thorugh SQL API SELECT current_database(); and getting the HEX string separated by - characters.

I'm not sure if is the correct way to do this or if there are more metadata that needs to be deleted in order to avoid other conflicts, but it works, datasets are deleted from Carto Builder datasets administration interface. I hope this can help someone.