pg.ddx.io pgsql-hackers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Sergei Kornilov <sk@zsrv.org>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Andreas Karlsson <andreas@proxel.se>
Cc: pgsql-hackers@postgresql.org
Subject: Re: reindex concurrently and two toast indexes
Date: Sat, 22 Feb 2020 05:13:19 -0600
Message-ID: <20200222111319.GS31889@telsasoft.com> (raw)
In-Reply-To: <20200218052933.GH4176@paquier.xyz>
References: <20200216190835.GA21832@telsasoft.com>
<20200218052933.GH4176@paquier.xyz>
On Tue, Feb 18, 2020 at 02:29:33PM +0900, Michael Paquier wrote:
> 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.g...
> >
> > 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.
Julien sent a patch for that, but here are my ideas (which you are free to
reject):
Could you require an AEL for that case, or something which will preclude
reindex table test_toast from working ?
Could you use atomic updates to ensure that exactly one index in an {old,new}
pair is invalid at any given time ?
Could you make the new (invalid) toast index not visible to other transactions?
--
Justin Pryzby
view thread (22+ messages) latest in thread
Message-ID: <20200222111319.GS31889@telsasoft.com> Permalink: ../20200222111319.GS31889@telsasoft.com/ Also on: postgresql.org/message-id/20200222111319.GS31889@telsasoft.com ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-hackers@postgresql.org Cc: pryzby@telsasoft.com, michael@paquier.xyz, sk@zsrv.org, peter.eisentraut@2ndquadrant.com, michael.paquier@gmail.com, andreas@proxel.se Subject: Re: reindex concurrently and two toast indexes In-Reply-To: <20200222111319.GS31889@telsasoft.com> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox