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
morethan 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
privilegesin 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