Обсуждение: idle in transaction
Hi All,
Can somebody help me to understand the below given idle in transaction?
SELECT * FROM pg_stat_activity where pid = 2161513;
-[ RECORD 1 ]----+-----------------------------------------------------------------------------------
datid | 37490141
datname | prod
pid | 2161513
leader_pid |
usesysid | 37490157
usename | admin
application_name |
client_addr | ::1
client_hostname |
client_port | 16788
backend_start | 2023-11-27 20:24:20.20062+05:30
xact_start | 2023-11-27 20:24:33.694904+05:30
query_start | 2023-11-28 09:25:57.666297+05:30
state_change | 2023-11-28 09:25:57.666336+05:30
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid |
backend_xmin |
query | SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7fdd58004550";RELEASE _per_query_svp_
backend_type | client backend
-[ RECORD 1 ]----+-----------------------------------------------------------------------------------
datid | 37490141
datname | prod
pid | 2161513
leader_pid |
usesysid | 37490157
usename | admin
application_name |
client_addr | ::1
client_hostname |
client_port | 16788
backend_start | 2023-11-27 20:24:20.20062+05:30
xact_start | 2023-11-27 20:24:33.694904+05:30
query_start | 2023-11-28 09:25:57.666297+05:30
state_change | 2023-11-28 09:25:57.666336+05:30
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction
backend_xid |
backend_xmin |
query | SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7fdd58004550";RELEASE _per_query_svp_
backend_type | client backend
I would like to understand the following query found in the pg_stat_activity.
I see this particular process always active but not doing anything in the BDR network. How do I find out what query is it running?
SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7fdd58004550";RELEASE _per_query_svp_
Regards,
Nikhil Ingale
Nikhil Ingale
On Monday, November 27, 2023, Nikhil Ingale <niks.bgm@gmail.com> wrote:
How do I find out what query is it running?SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7fdd58004550";RELEASE _per_query_svp_
That is the last command (multi-statement though it is) that was executed before the transaction/session went idle.
David J.
On Mon, 2023-11-27 at 21:07 -0700, David G. Johnston wrote: > On Monday, November 27, 2023, Nikhil Ingale <niks.bgm@gmail.com> wrote: > > How do I find out what query is it running? > > > > SAVEPOINT _per_query_svp_;DEALLOCATE "_PLAN0x7fdd58004550";RELEASE _per_query_svp_ > > > > That is the last command (multi-statement though it is) that was executed before the transaction/session went idle. ... and it points towards a problem that is quite unrelated to the actual question. There seems to be an automatism in your application that wraps every statement in a subtransaction to emulate statement-level rollback. That will cause severe performance problems: https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/ So your application is broken not only because it does not close its transactions. Yours, Laurenz Albe