0

My application listens on a channel on startup. After running the database and application for a while, if I restart the application, I get this error attempting to listen on the channel:

ERROR: could not access status of transaction 145358605 (SQLSTATE 58P01)

I cannot recover from this error even if I restart the app multiple times. I have to restart the database and then the application.

I'm not doing pg_notify on this channel while the app is restarting. Other sessions are inserting into the database, but they don't trigger anything that could cause the pg_notify on this channel.

This problem only recently started happening when we upgraded to Postgres 16.0. We were previously using 10.5

I stopped all my processes that inserted into any tables, but I still get the error when the app attempts to start. I see this from @Laurenz Albe's query. It seems like the only transaction is the query itself:

# SELECT query, max(clock_timestamp() - xact_start) FROM pg_stat_activity WHERE backend_type = 'client backend' group by query;
                                                             query                                                             |       max
-------------------------------------------------------------------------------------------------------------------------------+-----------------
 SELECT query, max(clock_timestamp() - xact_start) FROM pg_stat_activity WHERE backend_type = 'client backend' group by query; | 00:00:00.009886
(1 row)

# SELECT query, max(clock_timestamp() - xact_start) FROM pg_stat_activity WHERE backend_type = 'client backend' group by query;
                                                             query                                                             |       max
-------------------------------------------------------------------------------------------------------------------------------+-----------------
 ;                                                                                                                             |
 SELECT query, max(clock_timestamp() - xact_start) FROM pg_stat_activity WHERE backend_type = 'client backend' group by query; | 00:00:00.001153
(2 rows)

# SELECT query, max(clock_timestamp() - xact_start) FROM pg_stat_activity WHERE backend_type = 'client backend' group by query;
                                                             query                                                             |      max
-------------------------------------------------------------------------------------------------------------------------------+----------------
 SELECT query, max(clock_timestamp() - xact_start) FROM pg_stat_activity WHERE backend_type = 'client backend' group by query; | 00:00:00.00106
(1 row)
3
  • This is a duplicate of this. Make sure you close your transactions. Nov 16, 2023 at 21:05
  • I had seen that issue, and it didn't seem to apply to me. I'm not doing any transactions while the app is restarting. In other processes I am inserting into tables, but not in the context of a transaction. In the transactions I do, I make sure to rollback or commit before exiting. Nov 16, 2023 at 21:12
  • I find that hard to believe. Try SELECT max(clock_timestamp() - xact_start) FROM pg_stat_activity WHERE backend_type = 'client backend'; . Nov 17, 2023 at 3:23

0

Your Answer

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

Browse other questions tagged or ask your own question.