On Sun, Feb 16, 2020 at 01:08:35PM -0600, Justin Pryzby wrote: > Forking old, long thread: > https://www.postgresql.org/message-id/36712441546604286%40sas1-890ba5c2334a.qloud-c.yandex.net > On Fri, Jan 04, 2019 at 03:18:06PM +0300, Sergei Kornilov wrote: >> About reindex invalid indexes - i found one good question in archives [1]: >> how about toast indexes? >> I check it now, i am able drop invalid toast index, but i can not drop >> reduntant valid index. >> Reproduce: >> session 1: begin; select from test_toast ... for update; >> session 2: reindex table CONCURRENTLY test_toast ; >> session 2: interrupt by ctrl+C >> session 1: commit >> session 2: reindex table test_toast ; >> and now we have two toast indexes. DROP INDEX is able to remove >> only invalid ones. Valid index gives "ERROR: permission denied: >> "pg_toast_16426_index_ccnew" is a system catalog" >> [1]: >> https://www.postgresql.org/message-id/CAB7nPqT%2B6igqbUb59y04NEgHoBeUGYteuUr89AKnLTFNdB8Hyw%40mail.gmail.com > > It looks like this was never addressed.
On HEAD, this exact scenario leads to the presence of an old toast index pg_toast.pg_toast_*_index_ccold, causing the index to be skipped on a follow-up concurrent reindex: =# reindex table CONCURRENTLY test_toast ; WARNING: XX002: cannot reindex invalid index "pg_toast.pg_toast_16385_index_ccold" concurrently, skipping LOCATION: ReindexRelationConcurrently, indexcmds.c:2863 REINDEX And this toast index can be dropped while it remains invalid: =# drop index pg_toast.pg_toast_16385_index_ccold; DROP INDEX I recall testing that stuff for all the interrupts which could be triggered and in this case, this waits at step 5 within WaitForLockersMultiple(). Now, in your case you take an extra step with a plain REINDEX, which forces a rebuild of the invalid toast index, making it per se valid, and not droppable. Hmm. There could be an argument here for skipping invalid toast indexes within reindex_index(), because we are sure about having at least one valid toast index at anytime, and these are not concerned with CIC. Any thoughts? -- Michael
signature.asc
Description: PGP signature
