Postgres: No reindexdb in Yum packages?

csn

Here's what I have installed, via Yum:

postgresql.i386 8.0.7-1.FC4.1
postgresql-contrib.i386 8.0.7-1.FC4.1
postgresql-devel.i386 8.0.7-1.FC4.1
postgresql-libs.i386 8.0.7-1.FC4.1
postgresql-server.i386 8.0.7-1.FC4.1

I've done 'rpm -ql' on all of them and don't see
reindexdb. It's not included? How can I get it?

Sxooter

how odd. I can't find it in the rpms either. This is a packaging issue. Have you checked to see if the PGDG rpms have reindexdb in them?

Looks like you're gonna have to download the freakin' source tarball just for one script. arg.

csn

PGDG = PostGres Development Group? I haven't checked for rpms on postgresql.org yet.

Here's what Tom Lane said:
http://archives.postgresql.org/pgsql-ports/2006-04/msg00007.php

So it's apparently not in the contrib rpm anymore.

Since reindexdb is a frontend for REINDEX, could psql be used to reindex all databases?

Sxooter

Not sure. There is a reindex database form, but I don't know if that's equivalent.

Note that reindex is NOT transactable and a failed reindex can result in a lost index.. generally speaking, reindex is a repair operation, not a daily maintenance operation. I.e. you shouldn't normally need one, but some odd circumstances may need it, like certain kinds of index bloat. Those situations are much less common to find in 8.x

csn

Ah, you're right - guess I won't worry about it. I probably could've done something like 'psql -l | psql -c "reindex ..."'. BTW, check this out:

For all indexes except the shared system catalogs, REINDEX is crash-safe and transaction-safe. REINDEX is not crash-safe for shared indexes, which is why this case is disallowed during normal operation. If a failure occurs while reindexing one of these catalogs in standalone mode, it will not be possible to restart the regular server until the problem is rectified. (The typical symptom of a partially rebuilt shared index is "index is not a btree" errors.)

I think this is why I was reindexing:
http://www.postgresql.org/docs/8.1/interactive/routine-reindex.html

Wow, I think I started using PG at 7.2 or maybe 7.3. Anyhow, sounds like reindexing still isn't a bad idea if you're deleting a lot of rows.

Sxooter

Hehe. I was actually one of the users who noticed that indexes didn't shrink, and the conversation I started resulted in one of the developers writing the reindexdb script back in the day (on 7.2).

I had a table that had 1500 rows, and was about 100k in size when new, that was 80M with a 500M index for it. oh man.