Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)

[newsreader]

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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)

[Mark Coffman \(Epilogue Programmer\)]

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" <[EMAIL PROTECTED]>
To: "Mark Coffman" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, August 21, 2001 6:28 PM
Subject: Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)


> "Mark Coffman" <[EMAIL PROTECTED]> 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
>


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)

[Tom Lane]

"Mark Coffman \(Epilogue Programmer\)" <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [GENERAL] Postgres hangs during VACUUM (autocommit = false)

[Tom Lane]

"Mark Coffman" <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster