-1

Is there a way to commit a transaction that is in an idle state in Postgres?

If we use idle_in_transaction_session_timeout it kills and rollbacks the transaction, but is there any way to commit and complete the session, I can't see any blocked by PID, I am using postgre10. Or is there any way I can find what is blocking these sessions? all these seem to be the result of the orphan processes from the application.

APPNEW=# SELECT datname
APPNEW-# ,pid
APPNEW-# , usename
APPNEW-# , wait_event_type
APPNEW-# , wait_event
APPNEW-# ,backend_start
APPNEW-# ,state
APPNEW-# , pg_blocking_pids(pid) AS blocked_by
APPNEW-# ,query
APPNEW-# FROM pg_stat_activity
APPNEW-# WHERE wait_event IS NOT NULL
APPNEW-# order by backend_start;
 datname |  pid  |   usename   | wait_event_type |     wait_event      |         backend_start         |        state        | blocked_by | query
---------+-------+-------------+-----------------+---------------------+-------------------------------+---------------------+------------+--------------------------------------------------
 APPNEW   |  4227 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:26.956884+12 | idle                | {}         | BEGIN;commit
 APPNEW   |  4305 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:35.955987+12 | idle                | {}         | BEGIN;commit
 APPNEW   |  4314 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.002783+12 | idle                | {}         | commit
 APPNEW   |  4323 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.046023+12 | idle                | {}         | BEGIN;commit
 APPNEW   |  4332 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.088676+12 | idle                | {}         | commit
 APPNEW   |  4341 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.137323+12 | idle                | {}         | BEGIN;commit
 APPNEW   |  4350 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.185843+12 | idle                | {}         | commit
 APPNEW   |  4359 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.227245+12 | idle                | {}         | BEGIN;commit
 APPNEW   |  4368 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.288329+12 | idle                | {}         | commit
 APPNEW   |  4377 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.337534+12 | idle                | {}         | commit
 APPNEW   |  4386 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.402352+12 | idle                | {}         | commit
 APPNEW   |  4395 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.439634+12 | idle                | {}         | commit
 APPNEW   |  4404 | appnew     | Client          | ClientRead          | 2020-07-26 14:28:36.481263+12 | idle                | {}         | commit
 .
 .
 (240 rows)
Sujeet Chaurasia
  • 97
  • 8

2 Answers2

1

Those are sessions that are "idle", not transactions.

An idle session has no pending transaction that can (or needs to) be committed.

There is nothing you need to do

0

'ClientRead' means they are blocked waiting for the client to send another command. Which it is not doing. If they were inside a transaction, the state would be called 'idle in transaction', not just 'idle'. You can't make them commit, as there is nothing to commit.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Those are the result of the orphan process from application, is there any way to deal with such transaction, I don't want to kill them as it might cause the data loss? – Sujeet Chaurasia Aug 23 '20 at 02:24
  • They have already committed. The connections have no data to lose. Maybe the app has data it never sent to the database, but you can't do anything about that from the database. The way to fix the problem is fix the app. – jjanes Aug 23 '20 at 14:12