Index Administration: pg_index vs. pg_get_indexdef()

In an old thread <http://archives.postgresql.org/pgsql-admin/2004-01/
msg00271.php>, Tom Lane suggested that it would be "unreasonable" to
use pg_index to reconstruct (expressional) indexes (in 7.4). The
suggested alternative was to use pg_get_indexdef().

I administer a postgres 8.0.x database with thousands of inherited
tables, each with what is supposed to be a consistently maintained
set of indexes. As the application programmers change (and have
changed) the DDL specification over time as it affects the creation
of new tables, however, it is the case that some indexes have gotten
out of sync or duplicated (in terms of the specified columns).

For the purposes of developing an index administration toolkit whose
intent is to discover and remove duplicate indexes and to add missing
indexes to pre-existing tables, it seems like it's easier to write an
index verification process based on the contents of pg_index (e.g.,
in order to compare the columns referenced by indexes on a table to
determine whether any of them seem to be duplicative) than to have to
parse the output of pg_get_indexdef(). Am I off base in this thinking?

P.S. Regardless of the wisdom of using pg_index for such purposes,
the OP in the old thread raised what I think is a good question: why
are techniques for accessing int2vector nowhere documented if the
type itself makes its way into very user-visible documentation and
catalogs/views?

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)