pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find and/or REINDEX only GiST indexes in the database?
5+ messages / 5 participants
[nested] [flat]
* How to find and/or REINDEX only GiST indexes in the database?
@ 2011-10-12 12:46 Gnanakumar <gnanam@zoniac.com>
0 siblings, 4 replies; 5+ messages in thread
From: Gnanakumar @ 2011-10-12 12:46 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find and/or REINDEX only GiST indexes in the database?
@ 2011-10-12 14:07 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Gnanakumar <gnanam@zoniac.com>
3 siblings, 0 replies; 5+ messages in thread
From: Achilleas Mantzios @ 2011-10-12 14:07 UTC (permalink / raw)
To: gnanam@zoniac.com; pgsql-admin
Στις 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find and/or REINDEX only GiST indexes in the database?
@ 2011-10-12 14:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gnanakumar <gnanam@zoniac.com>
3 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2011-10-12 14:21 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find and/or REINDEX only GiST indexes in the database?
@ 2011-10-12 14:37 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Gnanakumar <gnanam@zoniac.com>
3 siblings, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2011-10-12 14:37 UTC (permalink / raw)
To: pgsql-admin; gnanam@zoniac.com
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to find and/or REINDEX only GiST indexes in the database?
@ 2011-10-28 17:40 Dimitri Fontaine <dimitri@2ndQuadrant.fr>
parent: Gnanakumar <gnanam@zoniac.com>
3 siblings, 0 replies; 5+ messages in thread
From: Dimitri Fontaine @ 2011-10-28 17:40 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-10-28 17:40 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-12 12:46 How to find and/or REINDEX only GiST indexes in the database? Gnanakumar <gnanam@zoniac.com>
2011-10-12 14:07 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2011-10-12 14:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-10-12 14:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-10-28 17:40 ` Dimitri Fontaine <dimitri@2ndQuadrant.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox