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
