[GENERAL] Creating indeces on existing tables...

2001-01-31 Thread Steve Wolfe 


  OK, one of my tables was getting duplicate entries, and I thought I'd
create a unique index on a particular field.  So, I created the index:


domains=# create unique index idx_domain_name on domain (domain_name);
CREATE

Then, tried a vaccum:

domains=# vacuum analyze;
NOTICE:  Index idx_domain_name: NUMBER OF INDEX' TUPLES (305) IS NOT THE
SAME AS HEAP' (311).
Recreate the index.
VACUUM

  Huh?  So, I dropped the index, did a vacuum analyze, recreated the index,
tried a vacuum, same thing.

  So, I did a pg_dump, edited the dump file to create the index with the
table, and dropped/restored the database.  It worked, but I'm puzzled why
the origianal attempt didn't work.  It's Postgres 7.0.2.

steve

Re: [GENERAL] Creating indeces on existing tables...

2001-01-31 Thread Hiroshi Inoue 
Steve Wolfe wrote:
> 
>   OK, one of my tables was getting duplicate entries, and I thought I'd
> create a unique index on a particular field.  So, I created the index:
> 
> domains=# create unique index idx_domain_name on domain (domain_name);
> CREATE
> 
> Then, tried a vaccum:
> 
> domains=# vacuum analyze;
> NOTICE:  Index idx_domain_name: NUMBER OF INDEX' TUPLES (305) IS NOT THE
> SAME AS HEAP' (311).
> Recreate the index.
> VACUUM
>

This is a known bug.
Probably there's a long transaction somewhere.
It is fixed in 7.1.

Regards,
Hiroshi Inoue