Search Postgresql Archives

Re: Python client + select = locked resources???

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux