flaky webserver 02 unit tests times-out

[sanderegg]

• https://github.com/ITISFoundation/osparc-simcore/actions/runs/12647555133/job/35240159715

[pcrespov]

• Duplicated in unit tests webserver 02 and 03 tests occasionally hang #6976

[pcrespov]

The problem is that in the tests tear-down the postgres database tables are dropped but there are some transactions still opened which makes the db to lock.

Using docker exec ... psql test -U admin we can enter in the console of testing postgres database and find the unclosed transactions

SELECT pid, query, state, wait_event_type, wait_event, application_name
FROM pg_stat_activity
WHERE state = 'idle in transaction';

and we can close them with

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

by enabling

    command:
      - "postgres"
      - "-c"
      - "log_lock_waits=on"
      - "-c"
      - "log_statement=all"

we see in the logs that two processes (i.e. here 85 and 91) are locked

2025-01-16 13:34:33.575 UTC [91] LOG:  duration: 504.342 ms
2025-01-16 13:34:34.204 UTC [96] LOG:  process 96 still waiting for AccessExclusiveLock on relation 16432 of database 16384 after 1000.075 ms
2025-01-16 13:34:34.204 UTC [96] DETAIL:  Processes holding the lock: 85, 91. Wait queue: 96.  <------------------
2025-01-16 13:34:34.204 UTC [96] STATEMENT:

Further debugging indicates that this issue may stem from a fire-and-forget task still running while the tests attempt to drop the tables. The task keeps its transaction open, while the test teardown mechanism tries to delete the affected table. This remains a hypothesis and requires further investigation.

For now, we've added in #7018 a command in the teardown process to close any remaining transactions, allowing the test to proceed. However, this could be a sign of an underlying bug in the system.

[GitHK]

In one of my PRs I also have it inside of unit-servicelibrary. Same thing, just hangs https://github.com/ITISFoundation/osparc-simcore/actions/runs/12988958713/job/36221158635?pr=7075#step:9:280