From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sriram Dandapani <sdandapani@counterpane.com>
Cc: Matthew T. O'Connor <matthew@zeut.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: autovacuum ignore tables
Date: Fri, 29 Sep 2006 10:45:07 -0400
Message-ID: <15996.1159541107@sss.pgh.pa.us> (raw)
In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF060E5B49@ca-mail1.cis.local>
References: <6992E470F12A444BB787B5C937B9D4DF060E5B49@ca-mail1.cis.local>
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> The only issue I have with autovacuum is the fact that I have to briefly
> stop/restart postgres every couple of days, which kills autovacuum and
> it has no memory of previous work done.
As already stated, this isn't true as of 8.1.
> I work with several databases
> with partitioned tables having high daily volume. Dropping partitioned
> tables locks out jdbc inserts and the drop command itself goes into a
> WAIT state. Hence, I have to stop postgres,update pg_hba.conf to prevent
> access,restart postgres,drop tables and update pg_hba to allow
> access(Crazy, but I have no choice because Postgres deadlocks on drop
> child tables while inserts happen on the parent)
It sounds to me like you have a problem with failing to commit
transactions promptly. The DROP will wait for existing transactions to
release their locks on the doomed table, but its lock request will block
any incoming transactions that try to acquire new locks on the table.
So basically you should see a hiccup of length equal to the longest
normal transaction using that table. In a well-designed concurrent
system that should not be a problem.
It might be worth doing the DROP as
begin;
lock table parent_table;
drop table child_table;
commit;
so that SELECTs on the parent do simply block and don't risk getting
errors from trying to access a just-dropped child table.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, sdandapani@counterpane.com, matthew@zeut.net
Subject: Re: autovacuum ignore tables
In-Reply-To: <15996.1159541107@sss.pgh.pa.us>
References: <6992E470F12A444BB787B5C937B9D4DF060E5B49@ca-mail1.cis.local> <15996.1159541107@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox