pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew Sullivan <andrew@libertyrms.info>
To: pgsql-performance@postgresql.org
Subject: Finding the PID keeping a transaction open
Date: Tue, 25 Mar 2003 08:12:34 -0500
Message-ID: <20030325081233.B18817@mail.libertyrms.com> (raw)

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




view thread (3+ messages)  latest in thread

Message-ID: <20030325081233.B18817@mail.libertyrms.com>
Permalink:  ../20030325081233.B18817@mail.libertyrms.com/
Also on:    postgresql.org/message-id/20030325081233.B18817@mail.libertyrms.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: andrew@libertyrms.info
  Subject: Re: Finding the PID keeping a transaction open
  In-Reply-To: <20030325081233.B18817@mail.libertyrms.com>
  References: <20030325081233.B18817@mail.libertyrms.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox