Thread: 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@sitening.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@sitening.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)