Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA22771 for ; Sat, 20 Nov 1999 14:05:10 -0500 (EST) (envelope-from oleg@sai.msu.su) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.9.1/8.9.1) with SMTP id VAA07918; Sat, 20 Nov 1999 21:57:59 +0300 (MSK) Date: Sat, 20 Nov 1999 21:57:59 +0300 (MSK) From: Oleg Bartunov X-Sender: megera@ra To: Tom Lane cc: Matthew Hagerty , pgsql-sql@postgreSQL.org Subject: Re: [SQL] Deleting indexes before vacuum? In-Reply-To: <7175.943117272@sss.pgh.pa.us> Message-ID: Organization: Sternberg Astronomical Institute (Moscow University) MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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 > To: Oleg Bartunov > Cc: Matthew Hagerty , pgsql-sql@postgreSQL.org > Subject: Re: [SQL] Deleting indexes before vacuum? > > Oleg Bartunov 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