Quantcast

How to find and/or REINDEX only GiST indexes in the database?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to find and/or REINDEX only GiST indexes in the database?

Gnanam
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



--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to find and/or REINDEX only GiST indexes in the database?

Achilleas Mantzios
Στις 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

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to find and/or REINDEX only GiST indexes in the database?

Tom Lane-2
In reply to this post by Gnanam
"Gnanakumar" <[hidden email]> 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

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to find and/or REINDEX only GiST indexes in the database?

Kevin Grittner
In reply to this post by Gnanam
"Gnanakumar" <[hidden email]> 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

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to find and/or REINDEX only GiST indexes in the database?

Dimitri Fontaine-7
In reply to this post by Gnanam
"Gnanakumar" <[hidden email]> 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

--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Loading...