Thread: finding the procpid (or pid) for a prepared transaction
Hi All; I have several prepared tx which are running for a long long time. I want to kill 'em but I dont know the process id. I can get the xid via pg_prepared_xacts.transaction however the tx's have no locks thus no corresponding pg_locks row. Anyone know how I can get a procpid based on a tx id? Thanks in advance...
CS DBA <cs_dba@consistentstate.com> writes: > I have several prepared tx which are running for a long long time. I > want to kill 'em but I dont know the process id. If they're prepared, they're *not* running, and they don't have a process id. They're just going to sit there until you do COMMIT PREPARED or ROLLBACK PREPARED. regards, tom lane
On 02/17/2011 07:19 PM, Tom Lane wrote: > CS DBA<cs_dba@consistentstate.com> writes: >> I have several prepared tx which are running for a long long time. I >> want to kill 'em but I dont know the process id. > If they're prepared, they're *not* running, and they don't have a > process id. They're just going to sit there until you do COMMIT > PREPARED or ROLLBACK PREPARED. > > regards, tom lane aah, makes sense Thanks. So, for future reference.. if I prepare a tx, and then execute the prepared stmt, is there a way to associate the xid from a prepared txn (pg_prepared_xacts) with the row in pg_stat_activity showing the executing prepared stmt (within the current prepared txn)? or is there a way to see the text of a currently prepared txn so I can look for the statement being executed in pg_stat_activity?
On 02/17/2011 07:48 PM, CS DBA wrote: > On 02/17/2011 07:19 PM, Tom Lane wrote: >> CS DBA<cs_dba@consistentstate.com> writes: >>> I have several prepared tx which are running for a long long time. I >>> want to kill 'em but I dont know the process id. >> If they're prepared, they're *not* running, and they don't have a >> process id. They're just going to sit there until you do COMMIT >> PREPARED or ROLLBACK PREPARED. >> >> regards, tom lane > > aah, makes sense Thanks. > > So, for future reference.. if I prepare a tx, and then execute the > prepared stmt, is there a way to associate the xid from a prepared txn > (pg_prepared_xacts) with the row in pg_stat_activity showing the > executing prepared stmt (within the current prepared txn)? or is > there a way to see the text of a currently prepared txn so I can look > for the statement being executed in pg_stat_activity? > > > nevermind.... sorry I'm confusing prepared txn's with prepared stmts. -- --------------------------------------------- Kevin Kempter - Constent State A PostgreSQL Professional Services Company www.consistentstate.com ---------------------------------------------