Can't remove collections via GUI Admin! Remove collections directly via DB?

Help
1

Hey,
I’m attempting to remove a couple of collections from my Directus, but I noticed that it was taking super long through the admin (hasn’t actually concluded nor failed even after multiple hours long attempts).

So I tried it via the Database directly with psql, but certain records still seem to either take forever or just freeze.

To be exact…
Collections causing trouble:

  1. product_params
  2. product_params_translations

DB tables/records causing trouble:

  1. directus_presets - can’t remove records for “product_params”
  2. directus_collections - can’t remove none of the records of the two collections
  3. directus_fields - can’t remove record for “product_params” (field “id”)
  4. product_params - can’t drop the table

Imporant to note:

  • records from all other system tables related to collections like directus_relations have been deleted
  • the answer I’m seeking is any way to delete these collections/tables along with their leftovers - no need to limit the solution to just the admin interface or through the db.
  • the product_params collection seems to be severely cooked - I just tried to add a field to it just to see what would happen, and even that doesn’t work. It seems to be in some intermediate state where some of the metadata regarding doesn’t line up.

Any idea what could be causing this behaviour?

Thanks in advance to everyone reading this :]

2

Maybe foreign key constraints prevent dropping the tables.

DROP TABLE foo CASCADE;

would drop all constraints together with the table.

3

Progress - 50% - I managed to remove the product_params_translations table. In the graphical hierarchy arangement, it was placed under product_params, so I placed it at the top level instead and that allowed me to delete product_params_translations.

I’ll leave a message if I figure out how to get rid of product_params as well.

4

Okay I’ve got it!

What seems to have caused the issue:

*Click Delete Collection in Admin* =>
Directus uses the GraphQL api to delete the collection =>
the GraphQL api’s handler generates SQL commands =>
That SQL somehow resulted in a deadlock. That seems to have prevended me from mutating any resources related to that collection.

How I solved the deadlock:

List hanging SQL commands which are blocking any further ones:

SELECT pid, state, usename, query, xact_start, query_start, backend_start FROM pg_stat_activity WHERE state != 'idle' ORDER BY xact_start NULLS LAST;

Delete the hanging command:

SELECT pg_terminate_backend(<-- insert process pid here -->);
5

Great you figured that out.
Do you have a list of the dead locked queries by chance? Could be interesting to find the root cause of the problem.