Thread: Finding the PID keeping a transaction open
Hi, Using 7.2.3 and 7.2.4 (the last .3 is being retired this weekend). I'm struggling with an application which is keeping open a transaction (or, likely from the results, more than one) against a pair of frequently-updated tables. Unfortunately, the frequently-updated tables are also a performance bottleneck. These tables are small, but their physical size is very large, because of all the updates. The problem is, of course, that vacuum isn't working because _something_ is holding open the transaction. But I can't tell what. We connect to the database via JDBC; we have a pool which recycles its connections. In the next version of the pool, the autocommit foolishness (end transaction and issue immediate BEGIN) is gone, but that won't help me in the case at hand. What I'm trying to figure out is whether there is a way to learn which pids are responsible for the long-running transaction(s) that touch(es) the candidate tables. Then I can find a way of paring those processes back, so that I can get vacuum to succeed. I think there must be a way with gdb, but I'm stumped. Any suggestions? The time a process has been living is not a guide, because the connections (and hence processes) get recycled in the pool. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes: > What I'm trying to figure out is whether there is a way to learn > which pids are responsible for the long-running transaction(s) that > touch(es) the candidate tables. In 7.3 you could look at the pg_locks system view, but I can't think of any reasonable way to do it in 7.2 :-( > I think there must be a way with gdb, but I'm stumped. The lock structures are arcane enough that manual examination with gdb would take many minutes --- which you'd have to do with the LockMgr lock held to keep them from changing underneath you. This seems quite unworkable for a production database ... It's conceivable that some version of the pg_locks code could be back-ported to 7.2 --- you'd have to settle for dumping the info to the log, probably, due to lack of table-function support, but it could be done. regards, tom lane
On Tue, Mar 25, 2003 at 09:37:41AM -0500, Tom Lane wrote: > In 7.3 you could look at the pg_locks system view, but I can't think > of any reasonable way to do it in 7.2 :-( Thanks. I was afraid you'd say that. Rats. > would take many minutes --- which you'd have to do with the LockMgr lock > held to keep them from changing underneath you. This seems quite Well, then, _that's_ a non-starter. Ugh. > It's conceivable that some version of the pg_locks code could be > back-ported to 7.2 --- you'd have to settle for dumping the info to > the log, probably, due to lack of table-function support, but it > could be done. I think it's probably better just to work on making the whole thing work correctly with 7.3, instead. I'm keen to move it, and 7.3 seems stable enough, so I'm inclined just to move that up in priority. Thanks, A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110