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 ;dopsql -tq discovery <./vacuum_hits.sqlrc=$?i=$((i+1))echo Vaccuming: $i, RC=$rcsleep 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
