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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
