Hello Hackers, I've encountered a number of users (mostly people who do not use a database on a day-to-day basis, such as consultants and support engineers, and therefore unfamiliar with the state of the database they're working with) who have experienced challenges when attempting to use CREATE INDEX IF NOT EXISTS. Specifically, if the index creation was attempted previously but failed, the CREATE INDEX IF NOT EXISTS syntax happily reports that the index exists, but falls short of identifying it as invalid. In more than one instance, such users lose a fair amount of time wondering why a query is not choosing the index, only to discover minutes (or hours) later that it is an invalid index.
As such, I'm proposing a small patch to make the notice from CREATE INDEX IF NOT EXISTS more informative when the existing index is invalid. The patch preserves the existing notice and adds detail when the conflicting relation has pg_index.indisvalid = false: NOTICE: relation "idx_test" already exists, skipping DETAIL: The existing index is invalid. Would this additional detail be useful? Comments on the wording and approach are welcome. Thank you for your consideration, Richard
v1-0001-Report-invalid-index-in-CREATE-INDEX-IF-NOT-EXISTS.patch
Description: Binary data
