Thread: Postgres hangs during VACUUM (autocommit = false)

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?

Autocommit is not an option.

Thanks!

Mark
epilogue.net

"Mark Coffman" <mark@epilogue.net> writes:
> is there any way for me to schedule a VACUUM
> that won't hang?

Fix your client-side code to not sit idle forever with uncommitted
transactions.  That's a bad practice independently of whether it
gives VACUUM problems.  The clients presumably think that whatever
they've done so far is committed ... but guess what, it's not.

> I cannot autocommit because I need transactions.

Fine, but that means that you have the responsibility to commit.
Evidently you're not doing so.

FWIW, 7.2's default VACUUM will be more forgiving, but that doesn't
make your clients' behavior a good idea.

            regards, tom lane

Thanks for the reply.  I use Perl and DBI to make my database conneciton.
In the

BEGIN {}
part of the module, I check for the persistant connection, and make one if
none is present, autocommit off
in the
END {}
block, I do a $dbh->commit();

They all still have idle in transaction
:(

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Mark Coffman" <mark@epilogue.net>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, August 21, 2001 6:28 PM
Subject: Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)


> "Mark Coffman" <mark@epilogue.net> writes:
> > is there any way for me to schedule a VACUUM
> > that won't hang?
>
> Fix your client-side code to not sit idle forever with uncommitted
> transactions.  That's a bad practice independently of whether it
> gives VACUUM problems.  The clients presumably think that whatever
> they've done so far is committed ... but guess what, it's not.
>
> > I cannot autocommit because I need transactions.
>
> Fine, but that means that you have the responsibility to commit.
> Evidently you're not doing so.
>
> FWIW, 7.2's default VACUUM will be more forgiving, but that doesn't
> make your clients' behavior a good idea.
>
> regards, tom lane
>

"Mark Coffman" <mark@cherone.com> writes:
> Yeah, I want autocommit off because there is a perl script that runs between
> the BEGIN and END blocks, and that script uses transactions.  I just add the
> $dbh->commit(); to the END block to try to FORCE it to not be idle in
> transaction.  I am having no luck.

What I suspect is that "autocommit off" causes the DBD driver to send a
fresh BEGIN immediately after the COMMIT.  You might be better off with
"autocommit on" which I think suppresses any automatic issuance of
BEGIN/COMMIT.  Then you'd need to issue "BEGIN" and "COMMIT" explicitly
to turn your module into a transaction block.

However, this theory doesn't completely explain your problem, because
if there were a COMMIT and BEGIN coming out of the client, it'd show as
"idle in transaction" afterwards, but it'd not be holding any locks and
so couldn't block VACUUM.  So there's something else going on.  This is
why I wanted to see a query log.  (Easiest way to get one is to start
postmaster with -d2 switch.)

            regards, tom lane

"Mark Coffman \(Epilogue Programmer\)" <mark@epilogue.net> writes:
> Thanks for the reply.  I use Perl and DBI to make my database conneciton.
> In the

> BEGIN {}
> part of the module, I check for the persistant connection, and make one if
> none is present, autocommit off
> in the
> END {}
> block, I do a $dbh->commit();

Hmm.  I wonder whether "autocommit off" does what you think it does;
it may be that you want the exact opposite.

You might want to turn on query logging and see just what the clients
are sending.

            regards, tom lane

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

On Tue, Aug 21, 2001 at 07:19:42PM -0400, Tom Lane wrote:
>
> What I suspect is that "autocommit off" causes the DBD driver to send a
> fresh BEGIN immediately after the COMMIT.  You might be better off with
> "autocommit on" which I think suppresses any automatic issuance of
> BEGIN/COMMIT.  Then you'd need to issue "BEGIN" and "COMMIT" explicitly
> to turn your module into a transaction block.
>


$ perldoc DBD::Pg

------------- snip
.
.
       According to the DBI specification the default for AutoCommit is TRUE.  In
       this mode, any change to the database becomes valid immediately. Any
       'begin', 'commit' or 'rollback' statement will be rejected.

       If AutoCommit is switched-off, immediately a transaction will be started by
       issuing a 'begin' statement. Any 'commit' or 'rollback' will start a new
       transaction. A disconnect will issue a 'rollback' statement.

-------------

Suggestion to the original poster: don't use persistent
connections then or else temporarily stop the front
ends.  Vacuuming locks the tables anyhow and
they won't be able to access them during vacuuming