Обсуждение: How to find and/or REINDEX only GiST indexes in the database?

Hi,

We're planning to upgrade our Production server from PostgreSQL v8.2.3 to
the latest version in 8.2 series, that is v8.2.22.

In 8.2.11 Release Notes here
http://www.postgresql.org/docs/current/interactive/release-8-2-11.html, it's
been mentioned that:
"Also, if you were running a previous 8.2.X release, it is recommended to
REINDEX all GiST indexes after the upgrade."

My question is, how do I find out or REINDEX *only* GiST indexes in the
database?  Is there a single syntax/command that does this?

Regards,
Gnanam

Στις Wednesday 12 October 2011 15:46:45 ο/η Gnanakumar έγραψε:
> Hi,
>
> We're planning to upgrade our Production server from PostgreSQL v8.2.3 to
> the latest version in 8.2 series, that is v8.2.22.
>
> In 8.2.11 Release Notes here
> http://www.postgresql.org/docs/current/interactive/release-8-2-11.html, it's
> been mentioned that:
> "Also, if you were running a previous 8.2.X release, it is recommended to
> REINDEX all GiST indexes after the upgrade."
>
> My question is, how do I find out or REINDEX *only* GiST indexes in the
> database?  Is there a single syntax/command that does this?

I guess not, one way is to take an ascii schema-only dump and parse it,
looking for GIST indexes.

Anotherway is to look in those tables:
SELECT oid,* from pg_opclass ; -- to find the GIST class
SELECT c.relname,indclass from pg_catalog.pg_index i,pg_catalog.pg_class c where indexrelid=c.oid;
^^ to find those indexes which have this gist class oid in the indclass field.

Note tho, that indclass is oidvector, and i dont know how you could iterate on those (by SQL),
without writing a C (or other) program.

>
> Regards,
> Gnanam
>
>
>



--
Achilleas Mantzios

"Gnanakumar" <gnanam@zoniac.com> writes:
> My question is, how do I find out or REINDEX *only* GiST indexes in the
> database?  Is there a single syntax/command that does this?

You could do something like

select relname from pg_class where relam = (select oid from pg_am where amname = 'gist');


            regards, tom lane

"Gnanakumar" <gnanam@zoniac.com> wrote:

> how do I find out or REINDEX *only* GiST indexes in the
> database?  Is there a single syntax/command that does this?

I would probably capture the output from:

SELECT indexdef || ';'
  FROM pg_indexes
  WHERE indexdef ~ ' USING gist ';

-Kevin

"Gnanakumar" <gnanam@zoniac.com> writes:
> My question is, how do I find out or REINDEX *only* GiST indexes in the
> database?  Is there a single syntax/command that does this?

You might find this article useful:

  http://tapoueh.org/blog/2010/08/05-querying-the-catalog-to-plan-an-upgrade.html

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support