Re: GLOBAL vs LOCAL temp tables

On Wed, Apr 16, 2003 at 06:42:36PM -0700, Kevin Brown wrote:

> Hmm...could VACUUM FULL then take an additional option, REINDEX (not
> sure if calling it that will be possible if it'll cause ambiguities in
> the parser), so that you don't have to do REINDEX separately?

Actually, the need to REINDEX should be pretty minimal. The command was
originally invented to recover from corrupted indexes, but was used as
a regular maintenance because of the ever-growing indexes problem. The
problem is mostly gone now, and REINDEX is again only meant to the
corrupted indexes scenario.

VACUUM generally won't free disk space, but it will mark pages free so
subsequent index growth will use them. Periodic VACUUM usage should be
enough to keep indexes in control.

> Ignore this if REINDEX can reindex a database without being in
> standalone mode in 7.4...

You should not need to use REINDEX anymore.

> On a slightly different note, from what I've seen of the source,
> REINDEX TABLE acquires an access exclusive lock on the table, thus
> preventing concurrent SELECTs against the table while REINDEX TABLE is
> running.

Yeah, it's difficult to recreate an index keeping concurrency. I had
originally meant to implement this (concurrent index rebuild), but got
stuck in the freelist thing, and now concurrent index rebuild is not
needed as much. Because of this I selected a different project that is
probably more useful.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)