Обсуждение: Unable to drop invalid TOAST indexes
Hello!
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?
Thanks!
gabrielle
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?
Thanks!
gabrielle
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