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