Thread: Postgres and mod_perl: table locked during vacuum?

We are experiencing a fairly predictable lock-up in the overnight VACUUM
ANALYZE maintenance script.  The VACUUM gets to the pg_class table and
then stops, obviously waiting for something else to give up control of
the table.  The VACUUM script resumes precisely when the web server is
bounced.  The web server is using mod_perl, and mod_perl uses DBI code
(in a locally developed module that does establish a connection at
*compile* time in order to verify existence of tables used in the
code).  What puzzles me is that the start-up code does not do any
UPDATEs or DELETEs, so although some of the connections do not have
AutoCommit enabled, I don't understand why pg_class would seem to be
locked in a transaction.

--
Jeff Boes                                      vox 616.226.9550 ext 24
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise

Jeff Boes <jboes@nexcerpt.com> writes:
> We are experiencing a fairly predictable lock-up in the overnight VACUUM
> ANALYZE maintenance script.  The VACUUM gets to the pg_class table and
> then stops, obviously waiting for something else to give up control of
> the table.  The VACUUM script resumes precisely when the web server is
> bounced.  The web server is using mod_perl, and mod_perl uses DBI code
> (in a locally developed module that does establish a connection at
> *compile* time in order to verify existence of tables used in the
> code).

I suppose you're not running 7.2 ?

Turn on debug_print_query and look at the log to see what queries
are being issued.  I'll bet lunch that the webserver code is doing
something like

    begin;
    select * from pg_class where ... ;
    <<go to sleep indefinitely>>

            regards, tom lane