From: Oleg Bartunov <oleg@sai.msu.su>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Matthew Hagerty <matthew@venux.net>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Deleting indexes before vacuum?
Date: Sat, 20 Nov 1999 21:57:59 +0300 (MSK)
Message-ID: <Pine.GSO.3.96.SK.991120212848.3910E-100000@ra> (raw)
In-Reply-To: <7175.943117272@sss.pgh.pa.us>
When I did some performance testing I got several times index
corruption. The test was simple - 30 concurrent processes
update *one* row and one process vacuuming the same table in parallel.
vacuuming was done using shell script:
while true ;do
psql -tq discovery <./vacuum_hits.sql
rc=$?
i=$((i+1))
echo Vaccuming: $i, RC=$rc
sleep 10;
done
and ./vacuum_hits.sql:
drop index hits_pkey;
vacuum analyze hits(msg_id);
create unique index hits_pkey on hits(msg_id);
Message was:
NOTICE: Index hits_pkey: NUMBER OF INDEX' TUPLES (187) IS NOT THE SAME AS HEAP' (190)
Regards,
Oleg
On Sat, 20 Nov 1999, Tom Lane wrote:
> Date: Sat, 20 Nov 1999 12:01:12 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: Matthew Hagerty <matthew@venux.net>, pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] Deleting indexes before vacuum?
>
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > The question is: what's the right way to do drop index/vacuum/create ?
> > Do I need transaction ?
>
> VACUUM should not be run inside a transaction block. (I am planning to
> make the code enforce this for 7.0, but it doesn't yet.) So you can't
> protect the whole sequence with a transaction. AFAICS the only real
> problem is that if you are depending on UNIQUE indexes to catch
> attempts to insert duplicate data, an insertion that got in between
> the drop and recreate wouldn't get checked.
>
> I have been toying with the notion of ripping out the existing VACUUM
> index handling code and putting in fresh code that would simply do an
> index drop and rebuild ;-). This could happen inside VACUUM's exclusive
> lock on the table, so it'd be perfectly safe whereas doing it the manual
> way is not. But I do not want to do this unless Vadim approves it as a
> good idea --- perhaps the existing index-vacuuming code can be fixed to
> be an even better solution than this. I haven't looked at the code to
> understand why it's so slow or whether there's a way to make it better.
>
> regards, tom lane
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
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-sql@postgresql.org
Cc: oleg@sai.msu.su, tgl@sss.pgh.pa.us, matthew@venux.net
Subject: Re: [SQL] Deleting indexes before vacuum?
In-Reply-To: <Pine.GSO.3.96.SK.991120212848.3910E-100000@ra>
References: <7175.943117272@sss.pgh.pa.us> <Pine.GSO.3.96.SK.991120212848.3910E-100000@ra>
* 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