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 ;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(at)sss(dot)pgh(dot)pa(dot)us>
> To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> Cc: Matthew Hagerty <matthew(at)venux(dot)net>, pgsql-sql(at)postgreSQL(dot)org
> Subject: Re: [SQL] Deleting indexes before vacuum?
>
> Oleg Bartunov <oleg(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83