scripts/maintenance.sh --no-dry-run fails to clear deleted datasets and others from the database

[pcm32]

Describe the bug

Execution of:

galaxy@galaxy-dev-job-0-6c8f594ff5-cwzd9:/galaxy/server$ bash scripts/maintenance.sh --no-dry-run --days 1

fails the delete_datasets section with:

Traceback (most recent call last):
  File "/galaxy/server/scripts/cleanup_datasets/cleanup_datasets.py", line 702, in <module>
    main()
  File "/galaxy/server/scripts/cleanup_datasets/cleanup_datasets.py", line 212, in main
    delete_datasets(app, cutoff_time, args.remove_from_disk, info_only=args.info_only, force_retry=args.force_retry)
  File "/galaxy/server/scripts/cleanup_datasets/cleanup_datasets.py", line 383, in delete_datasets
    (app.model.Dataset.table.c.id, app.model.Dataset.table.c.state),
AttributeError: type object 'Dataset' has no attribute 'table'

Galaxy Version and/or server at which you observed the bug
Galaxy Version: 22.05
Commit: 3b068ee

To Reproduce
Steps to reproduce the behavior:

1. Spin up the Galaxy helm chart or your favourite setup
2. Go to /galaxy/server or equivalent
3. bash scripts/maintenance.sh --no-dry-run --days 1
4. Error should come up in the prompt.
5. Postgres disk space doesn't get freed up.

Expected behavior

No error of the scripts and reduction of space being used by Postgres.

Additional context

galaxyproject/galaxy-helm#401

I detected this because I saw errors related to the database and it being in recovery mode (probably because it reached a full disk).

[nuwang]

@jdavcs Is this related to the Alembic migration?

[jdavcs]

No, it's not because of Alembic, it's a byproduct of the move to declarative mapping. I'll fix this.

[jdavcs]

xref #14972 (same cause)

[nsoranzo]

Duplicate of #14972.

[pcm32]

I have tried the fix in #15222 and it works in the sense that it doesn't produce an error, however my database disk is very much still full even though I have deleted most stuff referenced there (in terms of histories and datasets in the UI and on disk):

root@galaxy-galaxy-dev-postgres-0:/home/postgres/pgdata# df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/vdc        9.8G  9.7G  122M  99% /home/postgres/pgdata

galaxy=> SELECT deleted, COUNT(*) FROM history GROUP BY deleted;
 deleted | count
---------+-------
 f       |     6
 t       |   150
(2 rows)

galaxy=> SELECT deleted, COUNT(*) FROM history_dataset_association GROUP BY deleted;
 deleted | count
---------+-------
 f       |   459
 t       |  3152
(2 rows)

either the maintenance is not deleting what needs to be deleted or there is some flushing needed to be triggered? Could you reopen @nsoranzo since my issue is not about the script failing but whether that is the correct way or not of freeing up disk space for the postgres database. At least that was the purpose of the title, I reckon that the description doesn't put the same emphasis 😂 .

[nsoranzo]

@pcm32 That is a different issue than what's in the bug description, I'd open a new one.

Any way nothing is deleted from the database so you are looking in the wrong place.