asyncpg.exceptions.OutOfMemoryError: out of shared memory after upgrade to latest version

[2-5]

• asyncpg version: 0.22.0
• PostgreSQL version: 13.2
• Python version: 3.8.0
• Platform: Ubuntu 18.04
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• Can the issue be reproduced under both asyncio and
  uvloop?: I use uvloop, didn't test without it

After upgrading to asyncpg 0.22, suddenly I get a lot of this error which I never got before:

asyncpg.exceptions.OutOfMemoryError: out of shared memory
HINT:  You might need to increase max_pred_locks_per_transaction.

I tried changing the Postgres setting max_pred_locks_per_transaction from the default 64 to 256. The problem seemd to be fixed, but now it's happening again.

It might be related to the execute_many change you did, I use that to update around 50 rows in 3 different tables about 5 times a second. Not sure if that is a lot of not. However, the exception stated above happens when trying to update a different table, which is less frequently updated and with regular execute (not the many version).

[2-5]

Might be related to #700 since I also get that error at process start.

[elprans]

This is likely because executemany() became transactional, but there's nothing we can do really, since this is specific to your workflow and the way Postgres is managing its resources. Look into increasing max_pred_locks_per_transaction further and maybe also try fiddling with max_pred_locks_per_relation, and/or max_pred_locks_per_page.

(#700 is a separate bug)

[2-5]

Working around #700 by disabling SSL actually fixed this problem, so I could revert back to the default max_pred_locks_per_transaction

I think #700 exceptions cause some sort of Postgres resource leak.