pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedRe[4]:
2+ messages / 2 participants
[nested] [flat]
* Re[4]:
@ 2020-06-01 14:39 Andrey Klychkov <aaklychkov@mail.ru>
0 siblings, 1 reply; 2+ messages in thread
From: Andrey Klychkov @ 2020-06-01 14:39 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Continuing working with the feature.
If we run the following, we won't see any broken indexes:
zabbix=# \d sysmap_shape
Table "public.sysmap_shape"
Column | Type | Collation | Nullable | Default
------------------+----------------------+-----------+----------+-----------------------------
….
….
Indexes:
"sysmap_shape_pkey" PRIMARY KEY, btree (sysmap_shapeid)
"sysmap_shape_1" btree (sysmapid)
But if we run the following query we'll see a broken index related to the same table but including toast.
SELECT pg_class.relname
FROM pg_class, pg_index
WHERE pg_index.indisvalid = false
AND pg_index.indexrelid = pg_class.oid;
we get:
pg_toast_68086_index_ccnew
It's logical but not obvious.
So, would be also really helpful to have in the documentation:
1. note about the query above, i.e. how to find invalid indexes including TOAST to https://www.postgresql.org/docs/current/sql-reindex.html and to https://www.postgresql.org/docs/current/sql-dropindex.html
2. Referrence how to drop toast indexes to https://www.postgresql.org/docs/current/sql-dropindex.html
I’ve seen on the internet people ask each other about this (when i was searching the same not seeing that in the documentation).
Thanks for the previous patch!
>Воскресенье, 31 мая 2020, 4:51 +03:00 от Michael Paquier <michael@paquier.xyz>:
>
>On Fri, May 29, 2020 at 09:17:58AM -0400, Tom Lane wrote:
>> Sure, works for me.
>Thanks, applied then.
>--
>Michael
>
--
Regards,
Andrew K.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re:
@ 2020-06-02 01:32 Michael Paquier <michael@paquier.xyz>
parent: Andrey Klychkov <aaklychkov@mail.ru>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Paquier @ 2020-06-02 01:32 UTC (permalink / raw)
To: Andrey Klychkov <aaklychkov@mail.ru>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
On Mon, Jun 01, 2020 at 05:39:05PM +0300, Andrey Klychkov wrote:
> I’ve seen on the internet people ask each other about this (when i
> was searching the same not seeing that in the documentation).
Note that indexes and tables can be schema-qualified in a REINDEX
query, so you would need a bit more than the query above, still you
could just wrap that in a plpgsql function that scans pg_class and
pg_index, and issues a set of DROP INDEX commands on each entry. I
have been wondering as well in the past about adding a set of SQL
queries to be able to do the work in a database as if you run into
this issue repeatedly it is annoying. So we could add that on the
reindex page close to the paragraph about the drop of invalid
indexes.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-06-02 01:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-01 14:39 Re[4]: Andrey Klychkov <aaklychkov@mail.ru>
2020-06-02 01:32 ` Re: Michael Paquier <michael@paquier.xyz>
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