Re: [SQL] Deleting indexes before vacuum?

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