4

When attempting to LISTEN to any channel, PostgreSQL says that Could not open file "pg_xact/0029": No such file or directory.

This is a freshly restored database, so it can't be database corruption.

Changing to another database /c anotherdb works, restarting the PostgreSQL instance also fixes the issue. The issue seems to appear randomly after a few days of uptime.

loritta=# LISTEN abc;
ERROR:  could not access status of transaction 43350785
DETAIL:  Could not open file "pg_xact/0029": No such file or directory.

PostgreSQL Version: PostgreSQL 15.2

MrPowerGamerBR
  • 141
  • 1
  • 4

1 Answers1

2

As you commented, this is very likely the problem described in this thread. In that case, the solution would be to avoid very long running transactions in the session that listens. Notifications are not delivered while you are inside a transaction, and by the time the transaction is done, the transaction ID associated with the notification might not exist any more.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90