Gerhard Hintermayer <gerhard(dot)hintermayer(at)gmail(dot)com> wrote:
> I have e.g. a table with:
> Indexes:
> "idx_auftrag_l1" hash (a_nr)
Any *hash* index will need to be rebuilt. Like that one.
> Seeing this and reading the docs about the caveats I see no other
> solution as to REINDEX all of my DB's :-(
As the docs say, you need to REINDEX your *hash* indexes. Most
people don't use those because they aren't WAL-logged, which can
lead to various problems (including this one) and because there seem
to be very few circumstances were they perform better than btree
indexes, which don't have such a problem.
If you switched to btree indexes where you currently have hash
indexes, the REINDEX would be totally unneeded.
> I have lot's of tables with primary key a_nr as above (though I
> know it is not reflected in the table definition, but should
> result in the same result, i.e. a_nr integer PRIMARY KEY would
> create an index to lookup the table).
I think a PRIMARY KEY constraint normally builds a btree index,
which wouldn't have this problem.
-Kevin