Re: 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)

"Thomas F. O'Connell" <tfo(at)sitening(dot)com> writes:
> 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().

IIRC, the point I was trying to make was that making client code try to
interpret the contents of pg_index.indexprs or pg_index.indpred is a
losing proposition. If you feel that you'd rather read the other fields
of pg_index for yourself, I won't argue with you.

regards, tom lane

On Nov 22, 2005, at 10:56 PM, Tom Lane wrote:

> "Thomas F. O'Connell" <tfo(at)sitening(dot)com> writes:
>> 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().
>
> IIRC, the point I was trying to make was that making client code
> try to
> interpret the contents of pg_index.indexprs or pg_index.indpred is a
> losing proposition. If you feel that you'd rather read the other
> fields
> of pg_index for yourself, I won't argue with you.

Yeah, I took a look at pg_index.indexprs and have already Perled up a
parser for the pg_get_indexdef() output... :)

Out of curiosity (without much knowledge of how pg_get_indexdef()
generates its output), would it be difficult to allow the view to
have a more useful format? What is the intention of providing an
expression tree? How could that be used?

--
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)