trying to delete profile: 17 other sessions using the database

[ltalirz]

This is just something I encountered on the tutorial VM.

I was trying to delete an AiiDA profile and got this message:

$ verdi daemon stop
Profile: generic
Waiting for the daemon to shut down... OK

$ verdi profile delete generic
Info: Deleting profile 'generic'
Warning: Found host 'localhost' but dropping '-h localhost' option for psql since this may cause psql to switch to password-based authentication.
Delete associated database 'aiidadb'?
WARNING: All data will be lost. [y/N]: y
Info: Deleting database 'aiidadb'.
ERROR:  database "aiidadb" is being accessed by other users
DETAIL:  There are 17 other sessions using the database.
Traceback (most recent call last):
  File "/home/max/.virtualenvs/aiida/bin/verdi", line 10, in <module>
    sys.exit(verdi())
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/aiida/cmdline/commands/cmd_profile.py", line 108, in profile_delete
    include_config=include_config)
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/aiida/manage/configuration/setup.py", line 129, in delete_profile
    delete_db(profile, non_interactive)
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/aiida/manage/configuration/setup.py", line 78, in delete_db
    postgres.drop_db(database_name)
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/aiida/manage/external/postgres.py", line 132, in drop_db
    self.execute(_DROP_DB_COMMAND.format(dbname))
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/aiida/manage/external/pgsu.py", line 104, in execute
    return _execute_psql(command, **kw_copy)
  File "/home/max/.virtualenvs/aiida/lib/python3.6/site-packages/aiida/manage/external/pgsu.py", line 325, in _execute_psql
    result = subprocess.check_output(sudo_su_psql, **kwargs)
  File "/usr/lib/python3.6/subprocess.py", line 356, in check_output
    **kwargs).stdout
  File "/usr/lib/python3.6/subprocess.py", line 438, in run
    output=stdout, stderr=stderr)
subprocess.CalledProcessError: Command '['sudo', '-n', 'su', 'postgres', '-c', 'psql -d template1 -p 5432 -tc \'DROP DATABASE "aiidadb"\'']' returned non-zero exit status 1.

Is this expected?
Here are these sessions:

$ ps -ef | grep postgres
postgres   892  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54142) idle
postgres   893  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54144) idle in transaction
postgres   965  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54248) idle in transaction
postgres  1004  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54292) idle in transaction
postgres  1012  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54308) idle in transaction
postgres  1028  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54336) idle in transaction
postgres  1072  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54406) idle in transaction
postgres  1109  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54454) idle
postgres  1178  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54486) idle
postgres  1191  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54512) idle in transaction
postgres  1284  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54600) idle
postgres  1289  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54610) idle
postgres  1290  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54612) idle
postgres  1291  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54614) idle
postgres  1302  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54622) idle
postgres  1506     1  0 Aug27 ?        00:00:05 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf
postgres  1531  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54690) idle
postgres  1535  1506  0 Aug27 ?        00:00:00 postgres: 10/main: checkpointer process
postgres  1536  1506  0 Aug27 ?        00:00:01 postgres: 10/main: writer process
postgres  1537  1506  0 Aug27 ?        00:00:01 postgres: 10/main: wal writer process
postgres  1539  1506  0 Aug27 ?        00:00:02 postgres: 10/main: autovacuum launcher process
postgres  1540  1506  0 Aug27 ?        00:00:03 postgres: 10/main: stats collector process
postgres  1541  1506  0 Aug27 ?        00:00:00 postgres: 10/main: bgworker: logical replication launcher
postgres  1542  1506  0 Aug28 ?        00:00:00 postgres: 10/main: aiida aiidadb 127.0.0.1(54698) idle
max       5966  2670  0 13:47 pts/0    00:00:00 grep --color=auto postgres

After sudo service postgresql restart I was able to delete the profile.
Would there have been another way?

@giovannipizzi @sphuber

[sphuber]

Depends. If you have no other instances or profiles connecting to the same database and active then in principle this shouldn't happen. Assuming there are no other applications outside AiiDA accessing this database, such as open psql sessions etc. Did you do things in the shell or run processes that causes exceptions before shutting down the daemon? I.e. might it have been possible that during some operations the database connections became orphaned and did not shutdown correctly?

[ltalirz]

If you have no other instances or profiles connecting to the same database and active then in principle this shouldn't happen. Assuming there are no other applications outside AiiDA accessing this database, such as open psql sessions etc.

Hm... on second thought it is not entirely impossible that the REST API was still running...
I doubt though that this would be responsible for all 17 connections.

Did you do things in the shell or run processes that causes exceptions before shutting down the daemon? I.e. might it have been possible that during some operations the database connections became orphaned and did not shutdown correctly?

Well... that is possible of course - are you saying that whenever there are exceptions people might end up with this situation? How about cases of a work station that may run for months without being restarted?
Is there no way to clean up this situation so that people don't end up with loads of orphaned connections?

[sphuber]

Well... that is possible of course - are you saying that whenever there are exceptions people might end up with this situation? How about cases of a work station that may run for months without being restarted?
Is there no way to clean up this situation so that people don't end up with loads of orphaned connections?

No, I was just spitballing here. I have never encountered this problem, nor have we heard of it from users. In the case where you really did not have any other applications running, I was already hypothesizing if this potentially might be an explication, but I would be very surprised.

[sphuber]

Hm... on second thought it is not entirely impossible that the REST API was still running... I doubt though that this would be responsible for all 17 connections.

Coming back to this issue. In think your current investigation on the REST API actually showed that it can open many connections and keep them open correct? Think this can be closed therefore. Feel free to reopen if you disagree.

[ltalirz]

Yes, just linking the corresponding issue for reference: #4374