From: | "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "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? |
Date: | 1999-11-22 01:53:28 |
Message-ID: | 000001bf348c$5f35f260$2801007e@cadzone.tpf.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> -----Original Message-----
> From: owner-pgsql-sql(at)postgreSQL(dot)org [mailto:owner-pgsql-sql(at)postgreSQL(dot)
> org]On Behalf Of Tom Lane
> Sent: Sunday, November 21, 1999 2:01 AM
> To: Oleg Bartunov
> Cc: Matthew Hagerty; 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.
>
Shouldn't vacuum preserve consistency even in case of abort ?
Currently PostgreSQL doesn't do little in case of abort(even commit also ?).
So I think it's a strong limitation.
It's well known that rebuilding indexes is faster than insert/deleting
many index tuples.
I have thought rebuilding indexes in vacuum for half a year.
But I don't have a reasonable solution yet.
Rebuiding indexes in vacuum has a big flaw that index may vanish in
case of abort and we may have to recreate index manually.
Regards.
Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-11-22 03:18:24 | Re: [SQL] Deleting indexes before vacuum? |
Previous Message | marten | 1999-11-21 11:12:00 | How to optimize this simple query :-( |