Re: index keeps on growing

Brian McCane <bmccane@mccons.net> writes:
> I assume that if I do it in a BEGIN..COMMIT block I won't lose
> anything, but I am not sure if a "DROP INDEX" can be rolled back.

DROP INDEX can be rolled back in the same releases that allow DROP
TABLE to be rolled back.  I think we allowed that beginning in
7.0, but check the release notes.

So basically you'd want

    BEGIN;
    DROP INDEX foo;
    either CREATE INDEX ...
    or ALTER TABLE ADD PRIMARY KEY ...;
    COMMIT;

Note this will imply peak disk usage equal to size of old index
plus size of new, since the old file can't physically be removed
till commit.

            regards, tom lane