Unable to drop invalid TOAST indexes

From: gabrielle <gorthx(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Unable to drop invalid TOAST indexes
Date: 2021-09-11 00:50:32
Message-ID: CAHRs-_c93COBSL+ESer5vadGf4rb6XZ4t6hM_yF+xwz3Ae0rGA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2021-09-11 01:41:29 Re: PostgreSQL starting with a corrupted database or other fatal issues
Previous Message Keith Christian 2021-09-11 00:08:04 PostgreSQL starting with a corrupted database or other fatal issues