Re: Postgres hangs during VACUUM (autocommit = false)

At 8/21/01 3:51 PM, Mark Coffman wrote:

>I am having trouble with setting up a scheduled VACUUM.  Since VACUUM needs
>an exclusive lock, I can never get it to run.  I am using persistant
>connections from a web server and use a single module to make the
>connection.  I cannot autocommit because I need transactions.
>
>So a ps gives several similar processes:
>
>postgres 24298  0.6  1.1  6548 2964 ?        S    14:00   0:15 postgres:
>httpd epilogue [local] idle in transaction
>postgres 24345  0.7  1.1  6496 2984 ?        S    14:01   0:15 postgres:
>httpd epilogue [local] idle in transaction
>postgres 24391  0.9  1.1  6488 2900 ?        S    14:02   0:19 postgres:
>httpd epilogue [local] idle in transaction
>
>I've tried doing a $dbh->commit() ; but that does no good.  I read all the
>posts I could find on this, but is there any way for me to schedule a VACUUM
>that won't hang?

Where are you trying your $dbh->commit()? You need to put it at the end
of the transactions from your Web server, even if the Web server only did
a select and didn't modify anything.

I had the same problem you're having, and found I wasn't using the commit
after I did a select. Adding $dbh->commit() as the last thing the
persistent Web client does, in all cases, fixed it.

--
Robert L Mathews, Tiger Technologies