3

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

4
  • I do have an theory about what happened: I had a stuck query for ~7 days, this caused the auto vacuum to run, and that cleaned up files on the pg_xact folder Mar 25, 2023 at 22:26
  • Did you figure out a solution to this? I have a similar problem. I have to restart Postgres as well to recover. Nov 20, 2023 at 19:01
  • Sadly I haven't @user2233706, also it doesn't seem that your issue is similar to mine: Yours happens right after boot randomly, mine happens after X days after the PostgreSQL instance is up Nov 20, 2023 at 23:31
  • 1
    I've better characterized my problem. My issue also happens if we run the application and database for long time, such as least a day. I think we have the same problem. Nov 29, 2023 at 2:04

1 Answer 1

1

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.

6
  • 1
    This happened in a freshly restored database, in fact, this was freshly restored (from a pg_dump .sql file) BECAUSE the previous database exhibited the same bug so I thought it was corruption. There's a thread on the PSQL group that someone reported something similar, while the user that reported this doesn't have the same issue, the explanation by Noah seems to reflect what happened to me; I had an long running transaction (forgot to create indexes, whoops) that made auto vacuum run, and that cleaned up the files from the pg_xact folder: postgrespro.com/list/thread-id/2546853 Mar 29, 2023 at 14:46
  • You are right. I updated the answer to reflect that. Mar 29, 2023 at 14:59
  • I thought about this issue again and looked at my code to see what I was doing... and now I'm not sure if that reply represents exactly my issue. I'm not creating a transaction, however I'm holding a connection open until the connection is dead, which if it is dead then I get a new transaction from the pool. Maybe JDBC is creating a transaction "behind the scenes"? github.com/LorittaBot/Loritta/blob/… Apr 25, 2023 at 19:11
  • I had other long running transactions in the database, but they shouldn't affect the NOTIFY/LISTEN system, right? They would cause issues if the LISTEN transaction was hung and wasn't reading any notifications from the queue... but they are! "The LISTEN/NOTIFY queue stores a transaction id for each notification", but if the LISTEN connection is running correctly, then there shouldn't be any notifications pending on the queue... hmmm, hard things to think about. Apr 25, 2023 at 19:15
  • You don't have to figure this out theoretically. A look into pg_stat_activity will show you which sessions are idle in transaction. Apr 26, 2023 at 5:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.