Re: Unable to drop invalid TOAST indexes

On Fri, 2021-09-10 at 17:50 -0700, gabrielle wrote:
> I ran into an interesting situation last week.
>
> Discovered on RDS Postgres version 13.
> Replicated on community Postgres version 13.
>
> A client wanted us to drop some invalid indexes, some of which turned out to be invalid indexes on TOAST tables.
>
> We don't know for sure how their database got this way; we suspect a failed 'REINDEX DATABASE CONCURRENTLY', possibly more than one.
>
> Attempts to drop the invalid index fail like so:
>     testy=> drop index CONCURRENTLY pg_toast.pg_toast_14199_index_ccnew;
>     ERROR:  permission denied for schema pg_toast
>
> This fails even when I'm logged in as the owner of the toast table's parent table;  I have to assume superuser privileges in order to drop them.
>
> On a hosted environment, this means a user could create an invalid index that they can't drop without provider intervention.
>
> Is this expected behavior?

In order to rebuild the TOAST index on "sometable", run

REINDEX TABLE CONCURRENTLY sometable;

As the documentation says: "If the table has a secondary “TOAST” table, that is reindexed as well."

You cannot drop the TOASE index, as that is required for PostgreSQL to function.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com