On Wed, Jul 1, 2009 at 12:21 PM, johnf
<jfabiani@yolo.com> 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???
The <IDLE> in transaction session that you see could be causing blocks. That's probably coming from the application that you're using. You need to disconnect that session and make sure that your code is either using autocommit or explicitly ending your transactions.
The <IDLE> sessions are the other connections to the database (pgAdmin, psql, etc...)
Also "commit" or "rollback" gives a warning "there is no transaction in
progress". So what gives?
If you login with psql, you have your own session. You cannot commit or rollback another session from yours. psql is autocommit, if you want a commit or rollback to do anything, you start by typing:
begin;
<your sql statements>
commit; or rollback;
--
John Fabiani
