Postgres 9.6.16 deadlocks during cleaning

[patrobinson]

Hi, This is probably not the repository for this issue, but putting it here incase someone else stumbles across it. Feel free to close it.

We are using DatabaseCleaner with Postgres 9.6.16 in a small Event Source application. Recently after adding a few more specs we noticed the database was consistently becoming deadlocked in our CI environment, across multiple EC2 instances each downloading a fresh Postgres container. I'm unable to replicate this locally.

Inspecting Postgres running queries shows two, one against each database (an "events" and "projections" database)

pid |       age       | usename  |                                     query
-----+-----------------+----------+-------------------------------------------------------------------------------
  67 | 00:22:19.032665 | postgres | SELECT NULL AS "nil" FROM "some_table" LIMIT 1
  66 | 00:22:19.056778 | postgres | DELETE FROM "aggregates"
(2 rows)

These hang, as far as I can tell, indefinitely. There's a bunch of locks for the DELETE FROM query but all are granted

pg_locks

``` | f postgres=# SELECT * FROM pg_locks; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | f astpath ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+---------------------+---------+-- -------- relation | 12407 | 11695 | | | | | | | | 5/19 | 639 | AccessShareLock | t | t virtualxid | | | | | 5/19 | | | | | 5/19 | 639 | ExclusiveLock | t | t relation | 12407 | 11797 | | | | | | | | 4/139 | 613 | AccessShareLock | t | t virtualxid | | | | | 4/139 | | | | | 4/139 | 613 | ExclusiveLock | t | t virtualxid | | | | | 3/1939 | | | | | 3/1939 | 67 | ExclusiveLock | t | t relation | 16384 | 123305 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 2 | 1 | 3/1939 | 67 | ExclusiveLock | t | f transactionid | | | | | | 40493 | | | | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123294 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 7 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123283 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123312 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123302 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123306 | | | | | | | | 3/1939 | 67 | ShareLock | t | f relation | 16384 | 123306 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123311 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123285 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 10 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123320 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 12 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123314 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 9 | 1 | 3/1939 | 67 | ExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 11 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123323 | | | | | | | | 3/1939 | 67 | ShareLock | t | f advisory | 16384 | | | | | | 0 | 8 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123274 | | | | | | | | 3/1939 | 67 | AccessShareLock | t | f relation | 16384 | 123274 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123290 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123318 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 1 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123301 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123296 | | | | | | | | 3/1939 | 67 | ShareLock | t | f relation | 16384 | 123296 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123315 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123319 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123291 | | | | | | | | 3/1939 | 67 | ShareLock | t | f relation | 16384 | 123291 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f object | 16384 | | | | | | 2615 | 2200 | 0 | 3/1939 | 67 | AccessShareLock | t | f advisory | 16384 | | | | | | 0 | 4 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123313 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f transactionid | | | | | | 40487 | | | | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123316 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 3 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123282 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123309 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123299 | | | | | | | | 3/1939 | 67 | ShareLock | t | f transactionid | | | | | | 40488 | | | | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123317 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123325 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123289 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123304 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f relation | 16384 | 123276 | | | | | | | | 3/1939 | 67 | AccessShareLock | t | f relation | 16384 | 123276 | | | | | | | | 3/1939 | 67 | ShareLock | t | f relation | 16384 | 123276 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 5 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123287 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f transactionid | | | | | | 40496 | | | | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123288 | | | | | | | | 3/1939 | 67 | AccessExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 6 | 1 | 3/1939 | 67 | ExclusiveLock | t | f advisory | 16384 | | | | | | 0 | 13 | 1 | 3/1939 | 67 | ExclusiveLock | t | f relation | 16384 | 123280 | | | | | | | | 3/1939 | 67 | ShareLock | t | f ```

Switching the cleaning from transactional to truncation resolves the issue.

[botandrose]

Wow, really interesting. My kneejerk hypothesis is that this is a bug somewhere upstream, but that's honestly of little value. :)

If you can reduce this down to some kind of sharable repo that reproduces the issue, I'd be happy to take a look at it!