Re: Postgres hangs during VACUUM (autocommit = false)

"Mark Coffman" <mark(at)cherone(dot)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