On Jul 1, 2009, at 9:21 AM, johnf wrote:
> On Monday 29 June 2009 09:26:24 am Craig Ringer wrote:
>> Try connecting to the database with psql and running
>> "select * from pg_stat_activity"
>> while the web app is running. You should see only "IDLE" or working
>> connections, never idle in transaction. If you have anything idle
>> in a
>> transaction for more than a few moments you WILL have problems,
>> because
>> if those transactions have SELECTed from the table you're trying to
>> alter they'll hold share locks that will prevent ALTER TABLE from
>> grabbing an exclusive lock on the table.
>
> I used psql to run the query "select * from pg_stat_activity;" and
> it shows
> three (3) lines , the select statement, <IDLE>, and the last one is
> <IDLE> in
> transaction. No one else is using the database because it's a on my
> machine.
> So can you explain why I have an "<IDLE> in transaction" listed and
> does it
> mean I can't alter the table from some other program like pgAdmin3???
>
> Also "commit" or "rollback" gives a warning "there is no transaction
> in
> progress". So what gives?
Using the values in the client_addr and client_port columns of the
pg_stat_activity output you can track that idle transaction back to
it's originating client process. First, since you say that all
connections should be coming from the same host, verify that the ip
listed under client_addr is the same as the host the db is on. If
not, determine what host the ip actually does refer to. Then on
whatever host the idle transaction is coming from run:
lsof -itcp:<port>
where <port> is the value from the client_port column in the earlier
pg_stat_activity output. In the lsof output there should be a pid
column take the pid value and run:
ps aux | grep <pid>
substituting the actual pid value for <pid>.
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
